Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

using parameters with data adapter

Posted on 2011-02-17
16
281 Views
Last Modified: 2012-05-11
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
Comment
Question by:vbnetcoder
  • 8
  • 7
16 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 34916927
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 34916955
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
 

Author Comment

by:vbnetcoder
ID: 34917253
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 51

Expert Comment

by:HainKurt
ID: 34917359
'spReturnSpecific CategoryDetails' & spReturnSpecificCategoryDetails  are different, note the space
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34917368
also "spReturnSpecificCategoryDetails " has an extra space at the end
0
 

Author Comment

by:vbnetcoder
ID: 34917374
Yeah .... my actual code does not have a space and I have the same problem
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34917385
post the declaration part of code for spReturnSpecificCategoryDetails
0
 

Author Comment

by:vbnetcoder
ID: 34917394
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
 

Author Comment

by:vbnetcoder
ID: 34917406
ALTER PROCEDURE [dbo].[spReturnSpecificCategoryDetails]
      -- Add the parameters for the stored procedure here
      @Category_ID            int

AS
BEGIN
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34917440
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
 

Author Comment

by:vbnetcoder
ID: 34917560
That gave me the same result
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34917665
what about this

command.Parameters.AddWithValue("@Category_ID", CategoryID)
-->
command.Parameters.Add("@Category_ID", SqlDbType.Int)
command.Parameters("@Category_ID").Value = CategoryID
0
 

Author Comment

by:vbnetcoder
ID: 34921871
I forgot this very important line of code:

command.CommandType = CommandType.StoredProcedure
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 34922932
:) after that, is it working now?
0
 

Author Comment

by:vbnetcoder
ID: 34922937
Yeah!  It was one of those issues that was to easy!
0
 

Author Closing Comment

by:vbnetcoder
ID: 34922942
ty
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question