Need a good example of using SQLDataAdapter and SPROC's with params

Posted on 2005-03-16
Medium Priority
Last Modified: 2008-02-20
I need to load up a DataSet with multiple tables. I found a good example of how to do this here:

The example on this page loads up the SQLDataAdapters by using a SQL string and calling the SQL directly. My project requirements forbid me from doing that, I need to make any data calls via stored procedures.

What I'm having trouble figuring out is how to load up the SQLDataAdapters from a Sproc, especially considering that the Sproc's take several parameters typically.

Can anyone either write for me, or point me to, a good example of how to do this?
Question by:toddhd
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
LVL 10

Assisted Solution

anv earned 400 total points
ID: 13562050
LVL 19

Accepted Solution

arif_eqbal earned 1200 total points
ID: 13562085
Here's a small Sample

I am using a Stored Proc by the name MyTest, it takes two parameters, and based on the parameter it selects some record and returns the fields (I am returning only two fields). Then I use this returned rows to fill a DataTable that can easily be added to DataSet

         Dim Cn As SqlConnection = New SqlConnection("YourConnection")
         Dim Cmd As SqlCommand = New SqlCommand("MyTest", Cn)
         Cmd.CommandType = CommandType.StoredProcedure
         Cmd.Parameters.Add(New SqlParameter("RETVALUE", SqlDbType.Int))
         Cmd.Parameters("RETVALUE").Direction = ParameterDirection.ReturnValue
         Cmd.Parameters.Add(New SqlParameter("@P1", SqlDbType.NVarChar, 80, "UserID"))
        Cmd.Parameters.Add(New SqlParameter("@P2", SqlDbType.NVarChar, 40, "Pwd"))
        Cmd.Parameters.Add(New SqlParameter("@O1", SqlDbType.NVarChar, 20))
        Cmd.Parameters.Add(New SqlParameter("@O2", SqlDbType.NVarChar, 80))
        Cmd.Parameters("@O1").Direction = ParameterDirection.Output
        Cmd.Parameters("@O2").Direction = ParameterDirection.Output
        Cmd.Parameters("@P1").Value = "Test"
        Cmd.Parameters("@P2").Value = "Test"
        Dim Dr As SqlDataReader
        Dim Da As New SqlDataAdapter(Cmd)
        Dim DT As New DataTable
        Catch ex As Exception
        End Try
LVL 14

Assisted Solution

amyhxu earned 400 total points
ID: 13562189

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

762 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