Solved

using parameters with data adapter

Posted on 2011-02-17
16
289 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
[X]
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
  • 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 56

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 56

Expert Comment

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

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 56

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 56

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 56

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 56

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 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