Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

using parameters with data adapter

Posted on 2011-02-17
16
Medium Priority
?
293 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 59

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 59

Expert Comment

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

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 59

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 59

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 59

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 59

Accepted Solution

by:
HainKurt earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

688 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