Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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")


0
vbnetcoder
Asked:
vbnetcoder
  • 8
  • 7
1 Solution
 
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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
HainKurtSr. System AnalystCommented:
:) after that, is it working now?
0
 
vbnetcoderAuthor Commented:
Yeah!  It was one of those issues that was to easy!
0
 
vbnetcoderAuthor Commented:
ty
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now