Batch Query with Parameters

  • Thread starter Thread starter JimM
  • Start date Start date
J

JimM

I'm using a stored procedure to retrieve hierarchical data from a database
for read only use. The stored procedure is:

CREATE PROC GetStringTable(@Token int) AS
SELECT * FROM StringTable WHERE Token = @Token
SELECT * FROM StringTableDetail WHERE Token = @Token"

When I use the "design surface" of the data access component to generate the
SqlDataAdapter (after setting the TableMappings manually) I can set the
Parameter value @Token with no problem.

However I'd like to code this without the design surface (with everything
local so the function can be shared/static). When I set the code:

Dim da As New SqlDataAdapter("GetStringTable", New
SqlConnection(ConnectString))
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.TableMappings.Add("Table", "StringTable")
da.TableMappings.Add("Table1", "StringTableDetail")
da.SelectCommand.Parameters("@Token").Value = token

at runtime it fails, complaining that there is no parameter @Token in the
collection. Sure enough, the da.SelectCommand.Parameters collection is
initialized (not Nothing/null), but it contains zero items.

Is there any way to configure this DataAdapter to handle the multiple tables
with Parameters?

--- Jim ---
 
Scratch that...figured it out...never fails, work on a problem for a day,
then find the solution just after posting to the newsgroup...

All I had to do was add the parameter to the SelectCommand:
da.SelectCommand.Parameters.Add(New SqlParameter("@Token",
SqlDbType.NVarChar, 8))

or even simpler:
da.SelectCommand.Parameters.Add("@Token", token)

Never mind!
 
I'm using a stored procedure to retrieve hierarchical data from a database
for read only use. The stored procedure is:

CREATE PROC GetStringTable(@Token int) AS
SELECT * FROM StringTable WHERE Token = @Token
SELECT * FROM StringTableDetail WHERE Token = @Token"

When I use the "design surface" of the data access component to generate the
SqlDataAdapter (after setting the TableMappings manually) I can set the
Parameter value @Token with no problem.

However I'd like to code this without the design surface (with everything
local so the function can be shared/static). When I set the code:

Dim da As New SqlDataAdapter("GetStringTable", New
SqlConnection(ConnectString))
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.TableMappings.Add("Table", "StringTable")
da.TableMappings.Add("Table1", "StringTableDetail")
da.SelectCommand.Parameters("@Token").Value = token

at runtime it fails, complaining that there is no parameter @Token in the
collection. Sure enough, the da.SelectCommand.Parameters collection is
initialized (not Nothing/null), but it contains zero items.

Is there any way to configure this DataAdapter to handle the multiple tables
with Parameters?

--- Jim ---
Click to expand...
You have to call parameterts.add. You also need to set their direction
(Input / Output) and the datatype. Possibly also the size.
 
I think the way you're creating your Parameter is not going to work. You
need to Add a new Parameter to the Parameters collection. The following
example illustrates what I mean.

ALTER PROCEDURE MultipleResultsetTitlesPriceByState

(

@StateCodeWanted Char(2)

)

AS

select pubid, pubname, city, state

from publishers

where state = @StateCodeWanted

select title, price, pubid

from titles

where pubid in

(select pubid from publishers where state = @StateCodeWanted)


RETURN



_________________ the code to call this procedure_________

Try

cn = New SqlConnection("Data Source=demoserver;integrated
security=sspi;initial catalog=biblio")

cmd = New SqlCommand("MultipleResultsetTitlesPriceByState", cn)

With cmd

.CommandType = CommandType.StoredProcedure

.Parameters.Add("@StateCodeWanted", "CA")

End With

da = New SqlDataAdapter(cmd)

da.Fill(ds)

Catch ex As Exception

MsgBox(ex.ToString)

End Try


--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top