parameterized query from code

[Ken]

Can anybody see where this is failing?

Private Sub Button2_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button2.Click

da.SelectCommand.Parameters.Add("@CustomerID",
SqlDbType.VarChar, 40)
Dim ds As New DataSet
da.SelectCommand.Parameters("@CustomerID").Value = myCustID
da.Fill(ds, "Customers")
End Sub

The error that I am getting on the fill command is: "The variable name
'@CustomerID' has already been declared. Variable names must be unique
within a query batch or stored procedure."

The dataadapter being referenced (da) is on the form with a
selectcommand set up in the query builder as such:

SELECT OrderID, CustomerID, OrderDate, EmployeeID
FROM Orders
WHERE CustomerID = @CustomerID

I can't seem to pass this value into the query correctly. I've tried
taking the @ signs out of the code, but exact same error.

thanks, Ken

 

[Michael C]

Can anybody see where this is failing?

Private Sub Button2_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button2.Click

da.SelectCommand.Parameters.Add("@CustomerID",
SqlDbType.VarChar, 40)
Dim ds As New DataSet
da.SelectCommand.Parameters("@CustomerID").Value = myCustID
da.Fill(ds, "Customers")
End Sub

The error that I am getting on the fill command is: "The variable name
'@CustomerID' has already been declared. Variable names must be unique
within a query batch or stored procedure."

You're adding a new parameter every time you click Button2! Possibly it
already has the parameter depending on how it is setup. Generally I just
create these things in code instead of putting them on the form but presume
it's possible to add params at design time.

Michael

 

[Ken]

You're adding a new parameter every time you click Button2! Possibly it
already has the parameter depending on how it is setup. Generally I just
create these things in code instead of putting them on the form but presume
it's possible to add params at design time.

Michael

That's what I was afraid of. I'd love to do the whole thing in code,
but I'm using VB Express, and it doesn't recognize the New
SqlDataAdapter as a valid command. That's why I put the adapter on
the form instead. Any idea how I can pass the parameter value from
the code into the query?

Thanks alot for the response,
Ken

 

[Ken]

You're adding a new parameter every time you click Button2!

That's what I was afraid of.

Possibly it already has the parameter depending on how it is setup. Generally I just
create these things in code instead of putting them on the form but presume
it's possible to add params at design time.

I'd love to do this in code, but I'm using VB Express, and it doesn't
recognize New SqlDataAdapter as a valid command. That's why I put the
adapter on the form instead. Any idea as to how I can pass the
parameter value into the query as structured above? Or is there a
better way to do this? The value is originally coming from a textbox
on the form.

Thanks for your help, Michael
Ken

 

[Ken]

You're adding a new parameter every time you click Button2!

That's what I was afraid of.

Possibly it already has the parameter depending on how it is setup. Generally I just
create these things in code instead of putting them on the form but presume
it's possible to add params at design time.

I'd love to do it in code, but I'm using VB Express, which doesn't
recognize the New SqlDataAdapter command. Any ideas how I can pass
the param value into the sql on the form da? Or any other better
ideas?

Thanks for your help, Michael.
Ken

 

[Michael C]

That's what I was afraid of.

I'd love to do it in code, but I'm using VB Express, which doesn't
recognize the New SqlDataAdapter command. Any ideas how I can pass
the param value into the sql on the form da? Or any other better
ideas?

Thanks for your help, Michael.
Ken

Surely it allows you to create a new data adaptor? The one designed on the
form only generates code to do just that anyway.

If you still want to leave it on the form then you could just remove the
line that adds the parameter. Otherwise you could move the line that creates
the parameter to form_load so it gets executed only once.

 

[Ken]

Surely it allows you to create a new data adaptor? The one designed on the
form only generates code to do just that anyway.

I think that I might have found it. I was trying "dim da as New
SqlDataAdapter", but that wasn't valid.

I found "dim da as New SqlClient.SqlDataAdapter". I'm going to play
around with that to see if it will allow me to do this all within the
procedure.

I'll repost if I get stuck. Thanks for your help.

Sorry for the multiple posts. The board didn't display my replies, so
I thought they weren't posting.
Ken

 

[Michael C]

I think that I might have found it. I was trying "dim da as New
SqlDataAdapter", but that wasn't valid.

I found "dim da as New SqlClient.SqlDataAdapter". I'm going to play
around with that to see if it will allow me to do this all within the
procedure.

You need to add the vb equivelant of C#'s "Using SqlClient" to the top of
your form.

Michael

 

[RobinS]

You need to add the vb equivelant of C#'s "Using SqlClient" to the top of
your form.

Michael

That would be

Imports System.Data.SqlClient

Robin S.

 

[Michael C]

That would be

Imports System.Data.SqlClient

Thanks. My statement was missing the System.Data part

Michael

 

[Ravichandran J.V.]

Even if VB Express does not do something you can always use the compiler
from the command prompt and compile the code with vbc.exe.

If you are using SqlClient, you can as well directly assign the value to
the da object as your command object already knows the @customerid
parameter.

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[RobinS]

You're welcome. I find the search box on the Object browser to be
invaluable when looking for what I need to include.

Robin S.
-----------------------------