Re: Commandtext using an @parametername but not with a stored procedure

Good ole sp_execute. That explains it. Thanks.

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:O6U1x9MeIHA.4396@xxxxxxxxxxxxxxxxxxxxxxx
If you look at what is happening using profiler you will most likely see
that the normally adhoc call was turned in to a sp_executesql call
instead. sp_executesql can make use of parameters and helps to reuse the
query plans. By specifying parameters for adhoc statements you can give it
the best chance (short of a sp) to reuse query plans.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"John Dohn" <noemail@xxxxxxxxxxx> wrote in message
news:u31aqTMeIHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
I have seen example vb.net 1.1 code that uses a commandtext string that
includes a parameter. The commandtype is commandtype.text, not a SP. I did
not know it was possible to do this. I would like to understand what is
going on here and exactly how the use of parameters works when using a
commandtype of text rather than a SP. For example, Who is receiving the
parameter values if there is no SP to catch them?

Thanks in advance for your help. Here is the relevant code:

Dim cmdUpdate as SqlCommand = cnn.createcommand
cmdUpdate.commandtype = commandtype.text
cmdUpdate.commandtext= "UPDATE Customers SET ContactName = @ContactName
WHERE CustomerID = @CustomerID"

The code then goes on to add various parameters to cmdUpdate.
e.g.
cmdUpdate.Parameters.Add("@ContactName", sqldbtype.Nvarchar, 30,
"ContactName")

And sets the command for the dataadapter...

da.UpdateCommand = cmdUpdate

And specifies which version of the dataset data to use...

cmdUpdate.parameters("@CustomerID").SourceVersion =
DataRowVersion.Original

Then goes on to fill the dataset, gets a particular datarow to edit,
makes sure there is some data there and then as exï¶ì(ed saves the
changes with

da.Update(ds, "Customers")








.

Relevant Pages

  • Re: Commandtext using an @parametername but not with a stored procedure
    ... If you look at what is happening using profiler you will most likely see that the normally adhoc call was turned in to a sp_executesql call instead. ... I would like to understand what is going on here and exactly how the use of parameters works when using a commandtype of text rather than a SP. ... The code then goes on to add various parameters to cmdUpdate. ...
    (microsoft.public.sqlserver.connect)
  • Commandtext using an @parametername but not with a stored procedure
    ... The commandtype is commandtype.text, not a SP. ... WHERE CustomerID = @CustomerID" ... The code then goes on to add various parameters to cmdUpdate. ... And specifies which version of the dataset data to use... ...
    (microsoft.public.sqlserver.connect)
Loading