using parameters with data adapter

I want to use a parameter with a data adapter.  I have this code .. but the stored procedure spReturnValues has a parameter that I want to pass something to.  Could somebody let me know how to best do that?


        Dim DA As New SqlClient.SqlDataAdapter("spReturnValues ", Connection)

        Dim DS As New DataSet()
        DA.Fill(DS, "values")


vbnetcoderAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
:) after that, is it working now?
0
 
DhaestCommented:
Dim DA As New SqlClient.SqlDataAdapter("spReturnValues ", Connection)
DA .SelectCommand.Parameters("YourParameterName").Value = parameterValue

Dim DS As New DataSet()
DA.Fill(DS, "values")

Parameters in Data-Adapter Commands
http://msdn.microsoft.com/en-us/library/ebxy9a8b(v=VS.90).aspx
0
 
HainKurtSr. System AnalystCommented:
something like this:

Dim command As SqlCommand = New SqlCommand("spReturnValues", connection)
' Add the parameters for the SelectCommand.
command.Parameters.Add("@Country", SqlDbType.NVarChar, 15)
command.Parameters.Add("@City", SqlDbType.NVarChar, 15)

Dim DA As New SqlClient.SqlDataAdapter(command)
Dim DS As New DataSet()
DA.Fill(DS, "values")


0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
vbnetcoderAuthor Commented:
I ran this code:


  Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("spReturnSpecificCategoryDetails ", Connection)
        ' Add the parameters for the SelectCommand.
        command.Parameters.AddWithValue("@Category_ID", CategoryID)

        Dim DA As New SqlClient.SqlDataAdapter(command)
        Dim DS As New DataSet()
        DA.Fill(DS, "Category")


And got a error:

Procedure or function 'spReturnSpecific CategoryDetails' expects parameter '@Category_ID', which was not supplied.
0
 
HainKurtSr. System AnalystCommented:
'spReturnSpecific CategoryDetails' & spReturnSpecificCategoryDetails  are different, note the space
0
 
HainKurtSr. System AnalystCommented:
also "spReturnSpecificCategoryDetails " has an extra space at the end
0
 
vbnetcoderAuthor Commented:
Yeah .... my actual code does not have a space and I have the same problem
0
 
HainKurtSr. System AnalystCommented:
post the declaration part of code for spReturnSpecificCategoryDetails
0
 
vbnetcoderAuthor Commented:
OK this code give me the same result:


    Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("spReturnSpecificCategoryDetails", Connection)
        ' Add the parameters for the SelectCommand.
        command.Parameters.AddWithValue("@Category_ID", CategoryID)

        Dim DA As New SqlClient.SqlDataAdapter(command)
        Dim DS As New DataSet()
        DA.Fill(DS, "Category")
0
 
vbnetcoderAuthor Commented:
ALTER PROCEDURE [dbo].[spReturnSpecificCategoryDetails]
      -- Add the parameters for the stored procedure here
      @Category_ID            int

AS
BEGIN
0
 
HainKurtSr. System AnalystCommented:
lets try this:

command.Parameters.AddWithValue("@Category_ID", CategoryID)
-->
command.Parameters.AddWithValue("@Category_ID", 1)

maybe it works, if it works, check the value of CategoryID before this code, assign a value first...
0
 
vbnetcoderAuthor Commented:
That gave me the same result
0
 
HainKurtSr. System AnalystCommented:
what about this

command.Parameters.AddWithValue("@Category_ID", CategoryID)
-->
command.Parameters.Add("@Category_ID", SqlDbType.Int)
command.Parameters("@Category_ID").Value = CategoryID
0
 
vbnetcoderAuthor Commented:
I forgot this very important line of code:

command.CommandType = CommandType.StoredProcedure
0
 
vbnetcoderAuthor Commented:
Yeah!  It was one of those issues that was to easy!
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.