I insert the following snippet and receive th error message "Declaration expected" but I thought the "Dim cmd As New SqlCommand" was the declaration! What am I missing!
Thanks in advance.
Public Class Class1
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers WHERE CompanyName LIKE @companyName"
cmd.Connection = conn
' Create a SqlParameter for each parameter in the stored procedure.
Dim companyNameParam As New SqlParameter("@companyName", "a%")
cmd.Parameters.Add(companyNameParam)
Thanks for this but it doesn't seem to help - although I may be missing something obvious. The code shown above was a straight insert of the snippet into the class so I'm a bit bemused tha it produces an error which makes me suspect I've not done something
very basic!
Can you give more info what this .vb filename is is part to this smb. or you are creating the select statment below, the only thing i dont see is the connection string.
Santos Martinez, MCSE, MCDBA, MCTS, MCT
Windows Server System - SQL Server MVP
Many thanks for your response. I've not been online for a while so apologies for not responding sooner.
The following is a code sample copied from Microsoft documentation (reference ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddata/html/8a929e96-2cf5-43a5-b5b7-c0a5a397bbc5.htm)
Imports Microsoft.VisualBasic
Public Class Class3
Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("ReservationsDBConnectionString")
Dim cmd As New System.Data.SqlClient.SqlCommand
Dim rowsAffected As Integer
cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
rowsAffected = cmd.ExecuteNonQuery()
sqlConnection1.Close()
End Class
I get the error "Declaration expected" for each of the 6 lines before the End Class line. I am clearly missing something terribly simple!
It's because you are not declaring a method i.e. you are trying to run code outside an event handler; try this...
Imports Microsoft.VisualBasic
Public Class Class3
Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("ReservationsDBConnectionString")
Dim cmd As New System.Data.SqlClient.SqlCommand
Dim rowsAffected As Integer
' For security create a public method so it can be referenced from a class instance (you do not expose the class code this way)
Public Function OpenConn()
' Call private method returning the integer value
Return p_OpenConn()
End Function
Private Function p_OpenConn()
cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
rowsAffected = cmd.ExecuteNonQuery()
' Now return your value and close the connection
Return rowsAffected
sqlConnection1.Close()
End Function
End Class
Hope that helps [:)]
Cheers, Matt
Marked as answer by craigton on Feb 25, 2007 02:00 PM
Hi, you marked your answer as correct instead of my answer (if I was correct!), I don't know if it makes a difference but I thought i'd mention it. [;)]
craigton
Member
33 Points
61 Posts
Parameterized slect command snippet
Jan 18, 2007 12:44 PM|LINK
Hi,
I insert the following snippet and receive th error message "Declaration expected" but I thought the "Dim cmd As New SqlCommand" was the declaration! What am I missing!
Thanks in advance.
Public Class Class1
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers WHERE CompanyName LIKE @companyName"
cmd.Connection = conn
' Create a SqlParameter for each parameter in the stored procedure.
Dim companyNameParam As New SqlParameter("@companyName", "a%")
cmd.Parameters.Add(companyNameParam)
End Class
SQL
MIB426
Contributor
3511 Points
852 Posts
Re: Parameterized slect command snippet
Jan 18, 2007 01:05 PM|LINK
not sure if you have same issue, but have a look
http://news.umailcampaign.com/message/30539.aspx
.NET is only way to go
MCP, MCSE, MCDBA, MCSD, MCAD
craigton
Member
33 Points
61 Posts
Re: Parameterized select command snippet
Jan 18, 2007 06:53 PM|LINK
smartinezpr
Member
83 Points
29 Posts
Re: Parameterized select command snippet
Jan 23, 2007 07:16 PM|LINK
Windows Server System - SQL Server MVP
craigton
Member
33 Points
61 Posts
Re: Parameterized select command snippet
Feb 08, 2007 01:41 PM|LINK
Many thanks for your response. I've not been online for a while so apologies for not responding sooner.
The following is a code sample copied from Microsoft documentation (reference ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddata/html/8a929e96-2cf5-43a5-b5b7-c0a5a397bbc5.htm)
Imports Microsoft.VisualBasic
Public Class Class3
Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("ReservationsDBConnectionString")
Dim cmd As New System.Data.SqlClient.SqlCommand
Dim rowsAffected As Integer
cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
rowsAffected = cmd.ExecuteNonQuery()
sqlConnection1.Close()
End Class
I get the error "Declaration expected" for each of the 6 lines before the End Class line. I am clearly missing something terribly simple!
Matt Morriso...
Member
66 Points
31 Posts
Re: Parameterized select command snippet
Feb 09, 2007 09:34 PM|LINK
Hi there,
It's because you are not declaring a method i.e. you are trying to run code outside an event handler; try this...
Imports Microsoft.VisualBasic Public Class Class3 Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("ReservationsDBConnectionString") Dim cmd As New System.Data.SqlClient.SqlCommand Dim rowsAffected As Integer ' For security create a public method so it can be referenced from a class instance (you do not expose the class code this way) Public Function OpenConn() ' Call private method returning the integer value Return p_OpenConn() End Function Private Function p_OpenConn() cmd.CommandText = "StoredProcedureName" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open() rowsAffected = cmd.ExecuteNonQuery() ' Now return your value and close the connection Return rowsAffected sqlConnection1.Close() End Function End ClassHope that helps [:)]craigton
Member
33 Points
61 Posts
Re: Parameterized select command snippet
Feb 13, 2007 10:19 AM|LINK
Matt,
Many thanks for this fulsome explanation - it's very helpful and greatly appreciated.
Matt Morriso...
Member
66 Points
31 Posts
Re: Parameterized select command snippet
Feb 13, 2007 10:48 AM|LINK
Thanks for the kind comments, just glad to help...
Did it resolve your issue? I think that you can mark the post as answered if it did; i'm happy to offer more help if required [:)]
Matt Morriso...
Member
66 Points
31 Posts
Re: Parameterized select command snippet
Feb 20, 2007 08:33 AM|LINK