parameterized query from code

[Ken]

Can anybody see where this is failing?

Private Sub Button2_Click(B yVal sender As Object, ByVal e As
System.EventArg s) Handles Button2.Click

da.SelectComman d.Parameters.Ad d("@CustomerID" ,
SqlDbType.VarCh ar, 40)
Dim ds As New DataSet
da.SelectComman d.Parameters("@ CustomerID").Va lue = 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]

Re: parameterized query from code

"Ken" <fkml99@yahoo.c omwrote in message
news:1177387275 .496603.227340@ y80g2000hsf.goo glegroups.com.. .

Can anybody see where this is failing?
>
Private Sub Button2_Click(B yVal sender As Object, ByVal e As
System.EventArg s) Handles Button2.Click
>
da.SelectComman d.Parameters.Ad d("@CustomerID" ,
SqlDbType.VarCh ar, 40)
Dim ds As New DataSet
da.SelectComman d.Parameters("@ CustomerID").Va lue = 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]

Re: parameterized query from code

>

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]

Re: parameterized query from code

>
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]

Re: parameterized query from code

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]

Re: parameterized query from code

"Ken" <fkml99@yahoo.c omwrote in message
news:1177393445 .236537.309040@ u32g2000prd.goo glegroups.com.. .

>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

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]

Re: parameterized query from code

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.SqlDa taAdapter". 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]

Re: parameterized query from code

"Ken" <fkml99@yahoo.c omwrote in message
news:1177397636 .374660.56870@c 18g2000prb.goog legroups.com...

>

>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.SqlDa taAdapter". 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]

Re: parameterized query from code


"Michael C" <nospam@nospam. comwrote in message
news:OK%23Ii8jh HHA.4516@TK2MSF TNGP03.phx.gbl. ..

"Ken" <fkml99@yahoo.c omwrote in message
news:1177397636 .374660.56870@c 18g2000prb.goog legroups.com...

>>

>>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.SqlDa taAdapter". 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
>

That would be

Imports System.Data.Sql Client

Robin S.

[Michael C]

Re: parameterized query from code

"RobinS" <RobinS@NoSpam. yah.nonewrote in message
news:vKOdnV_OKe HwM7DbnZ2dnUVZ_ jGdnZ2d@comcast .com...

That would be
>
Imports System.Data.Sql Client

Thanks. My statement was missing the System.Data part :-)

Michael

[Ravichandran J.V.]

Re: parameterized query from code

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.Ravichandra n
- http://www.geocities.com/
jvravichandran
- Or, just search on "J.V.Ravichandr an"
at http://www.Google.com

*** Sent via Developersdex http://www.developersdex.com ***

[RobinS]

Re: parameterized query from code

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.
-----------------------------
"Michael C" <nospam@nospam. comwrote in message
news:u5Bx2EkhHH A.4516@TK2MSFTN GP03.phx.gbl...

"RobinS" <RobinS@NoSpam. yah.nonewrote in message
news:vKOdnV_OKe HwM7DbnZ2dnUVZ_ jGdnZ2d@comcast .com...

>That would be
>>
> Imports System.Data.Sql Client

>
Thanks. My statement was missing the System.Data part :-)
>
Michael
>