ado.net data access error help

[Michael]

Hi Everyone,
Please help me on this one. I have the following function:
Public Sub SearchFor(ByRef mType As Module1.SearchForType, ByRef mValue As
String)
On Error GoTo Err_Handler
Dim Proc As String
Dim conStr as string = "Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Inventory;Data Source=SQLSERVER;"
Dim con as SqlConnection = new SqlConnection(conStr)
Dim cmd as SqlCommand
Select Case mType
Case Module1.SearchForType.ByBarCode
cmd = new SqlCommand("nf_GetInventoryItem", con)
cmd.Parameters.Add("@BarCodeId", sqldbtype.Int).Value = Val(CStr(mValue))
Case Module1.SearchForType.ByProductName
cmd = new SqlCommand("nf_GetInventoryByProductName", con)
cmd.Parameters.Add("@ProductName", sqldbtype.NVarChar, 25 ).Value = mValue
Case Module1.SearchForType.ByModelNumber
cmd = new SqlCommand("nf_GetInventoryByModelNumber", con)
cmd.Parameters.Add("@ModelNumber", sqldbtype.NVarChar, 30 ).Value = mValue
End Select
Dim ds as DataSet = new DataSet("SearchForQuery")
dim da as SqlDataAdapter
da = new SqlDataAdapter()
da.SelectCommand = cmd
da.Fill(ds)
PopulateGrid(ds)
Me.Show()
Exit Sub
Err_Handler:
MsgBox(Err.Number & ": " & Err.Description)
End Sub

The problem is that when the code gets to the da.Fill(ds) command, I get a
"Line 1: Incorrect syntax near 'nf_GetInventoryByProductName'." Error. I can
run the query in Studio .Net or Sql Server Enterprise manager and it works
just fine. I'm also feeding the parameter a value that is in the database. I
can run any of the three stored procedures within this function and will get
the same error message(except the sp name is changed to reflect the sp that
was selected). Does this code right? I'm new to the ADO.Net and little things
like this is driving me nuts :( Thanks for any suggestions that you can offer.
Michael

[Ken Tucker [MVP]]

Hi,

Two things. First I would get rid of the on error goto and
switch to using a try catch block. Second You never set the command type to
stored procedure

cmd = new SqlCommand("nf_GetInventoryItem", con)
cmd.commandtype=commandtype.storedprocedure

http://msdn.microsoft.com/library/de...dtypetopic.asp

Try catch info
http://msdn.microsoft.com/library/de...tchfinally.asp

http://msdn.microsoft.com/library/de...tchfinally.asp

Ken
------------------
"Michael" <Mi*****@discussions.microsoft.com> wrote in message
news:10**********************************@microsof t.com...
Hi Everyone,
Please help me on this one. I have the following function:
Public Sub SearchFor(ByRef mType As Module1.SearchForType, ByRef mValue As
String)
On Error GoTo Err_Handler
Dim Proc As String
Dim conStr as string = "Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Inventory;Data Source=SQLSERVER;"
Dim con as SqlConnection = new SqlConnection(conStr)
Dim cmd as SqlCommand
Select Case mType
Case Module1.SearchForType.ByBarCode
cmd = new SqlCommand("nf_GetInventoryItem", con)
cmd.Parameters.Add("@BarCodeId", sqldbtype.Int).Value = Val(CStr(mValue))
Case Module1.SearchForType.ByProductName
cmd = new SqlCommand("nf_GetInventoryByProductName", con)
cmd.Parameters.Add("@ProductName", sqldbtype.NVarChar, 25 ).Value = mValue
Case Module1.SearchForType.ByModelNumber
cmd = new SqlCommand("nf_GetInventoryByModelNumber", con)
cmd.Parameters.Add("@ModelNumber", sqldbtype.NVarChar, 30 ).Value = mValue
End Select
Dim ds as DataSet = new DataSet("SearchForQuery")
dim da as SqlDataAdapter
da = new SqlDataAdapter()
da.SelectCommand = cmd
da.Fill(ds)
PopulateGrid(ds)
Me.Show()
Exit Sub
Err_Handler:
MsgBox(Err.Number & ": " & Err.Description)
End Sub

The problem is that when the code gets to the da.Fill(ds) command, I get a
"Line 1: Incorrect syntax near 'nf_GetInventoryByProductName'." Error. I
can
run the query in Studio .Net or Sql Server Enterprise manager and it works
just fine. I'm also feeding the parameter a value that is in the database. I
can run any of the three stored procedures within this function and will get
the same error message(except the sp name is changed to reflect the sp that
was selected). Does this code right? I'm new to the ADO.Net and little
things
like this is driving me nuts :( Thanks for any suggestions that you can
offer.
Michael

[Michael]

Hi Ken,
Thanks for the reply. Last night I found the solution after a little more
reading(I've done alot of reading the last few months, lol). One more
question, is there something like the Refresh method on the Parameter
collection like there was in ADO. It was alittle easier to write the
parameters before. Thanks again for the reply.
Michael Lee
"Ken Tucker [MVP]" wrote:

Hi,

Two things. First I would get rid of the on error goto and
switch to using a try catch block. Second You never set the command type to
stored procedure

cmd = new SqlCommand("nf_GetInventoryItem", con)
cmd.commandtype=commandtype.storedprocedure

http://msdn.microsoft.com/library/de...dtypetopic.asp

Try catch info
http://msdn.microsoft.com/library/de...tchfinally.asp

http://msdn.microsoft.com/library/de...tchfinally.asp

Ken
------------------
"Michael" <Mi*****@discussions.microsoft.com> wrote in message
news:10**********************************@microsof t.com...
Hi Everyone,
Please help me on this one. I have the following function:
Public Sub SearchFor(ByRef mType As Module1.SearchForType, ByRef mValue As
String)
On Error GoTo Err_Handler
Dim Proc As String
Dim conStr as string = "Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Inventory;Data Source=SQLSERVER;"
Dim con as SqlConnection = new SqlConnection(conStr)
Dim cmd as SqlCommand
Select Case mType
Case Module1.SearchForType.ByBarCode
cmd = new SqlCommand("nf_GetInventoryItem", con)
cmd.Parameters.Add("@BarCodeId", sqldbtype.Int).Value = Val(CStr(mValue))
Case Module1.SearchForType.ByProductName
cmd = new SqlCommand("nf_GetInventoryByProductName", con)
cmd.Parameters.Add("@ProductName", sqldbtype.NVarChar, 25 ).Value = mValue
Case Module1.SearchForType.ByModelNumber
cmd = new SqlCommand("nf_GetInventoryByModelNumber", con)
cmd.Parameters.Add("@ModelNumber", sqldbtype.NVarChar, 30 ).Value = mValue
End Select
Dim ds as DataSet = new DataSet("SearchForQuery")
dim da as SqlDataAdapter
da = new SqlDataAdapter()
da.SelectCommand = cmd
da.Fill(ds)
PopulateGrid(ds)
Me.Show()
Exit Sub
Err_Handler:
MsgBox(Err.Number & ": " & Err.Description)
End Sub

The problem is that when the code gets to the da.Fill(ds) command, I get a
"Line 1: Incorrect syntax near 'nf_GetInventoryByProductName'." Error. I
can
run the query in Studio .Net or Sql Server Enterprise manager and it works
just fine. I'm also feeding the parameter a value that is in the database. I
can run any of the three stored procedures within this function and will get
the same error message(except the sp name is changed to reflect the sp that
was selected). Does this code right? I'm new to the ADO.Net and little
things
like this is driving me nuts :( Thanks for any suggestions that you can
offer.
Michael