Fill a dataset with a stored procedure (VB.NET)

Hi, I can't find the syntax for filling a dataset. I can create a SqlDataCommand object and bind a datagrid to it, but I can't seem to get it into a dataset.
Who is Participating?
Ryan ChongConnect With a Mentor Commented:
Example using CommandType.StoredProcedure with Parameter:

Dim conn As SqlConnection = New SqlConnection(appSettings.GetSetting("constring"))
        Dim cmdSP As SqlCommand = New SqlCommand("MySP", conn)

        cmdSP.CommandType = CommandType.StoredProcedure

        Dim RetValue As SqlParameter = cmdSP.Parameters.Add("@EnterMonth", SqlDbType.NChar, 2)
        RetValue.Value = "07"


        Dim da As New SqlDataAdapter(cmdSP)
        Dim ds As DataSet = New DataSet()
        da.Fill(ds, selectedSP)

        ' Attach dataset's DefaultView to the DGrid control
        DGrid.DataSource = ds.Tables(0)
        DGrid.CaptionText = ds.Tables(0).TableName & ": " & ds.Tables(0).Rows.Count & " records found."


Try customize according to your requirement. cheers
Ryan ChongCommented:
oops, try change the line of :

Dim cmdSP As SqlCommand = New SqlCommand("MySP", conn)


Dim cmdSP As SqlCommand = New SqlCommand(selectedSP, conn)

then define:

Dim selectedSP As String = "MyStoredProcedure"

Hope this helps

sumo_the_catAuthor Commented:
Do you need to use a DataAdapter? I thought that one of the points of using sprocs was that it cuts out the middle man.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

well, its not absolutly necessary to use dataadapter, you could use an datareader instead:
sumo_the_catAuthor Commented:

I'm sure that's the first thing I tried! I'll let you know when I get back in a couple of hours!
sumo_the_catAuthor Commented:
Whoops, misread that in my haste to get to the door. EBatista, I want to put the data into a dataset, not a datagrid.
Bob LearnedConnect With a Mentor Commented:
You can use all the code, or you can use the Data Access Application Block from Microsoft:

Data Access Application Block for .NET

(1) Download the DLL
(2) Add a reference
(3) Syntax would be:

Dim ds As DataSet = SqlHelper.ExecuteDataset( _
      "SERVER=(local);DATABASE=Northwind;INTEGRATED SECURITY=True;", _    
      CommandType.StoredProcedure, "SalesByCategory")

(4) There are a lot of other options to make life a lot easier.


EBatistaConnect With a Mentor Commented:
then, if your data comes from a database you have to use the DataAdapter to fill a DataSet.
I think you are a bit confused about the DataAdapters and stored procedures. DataAdapters have a string property called SelectCommand, that property is used by the DataAdapter when you call its Fill method to fill a dataset, that property can be either a sql string or a command object (SqlCommand or OleDbCommand), even it could be the stored procedure name you built in the database. All that means that DataAdapters and sprocs work together to fill a dataset:
Dim yourDataAdapter as new SqlDataAdapter()
yourDataAdapter.SelectCommand="storeProcedureName" 'or Command name or sql query string
' it is the same to say:
' Dim yourDataAdapter as New DataAdapter("sprocName")

but if you want to fill the dataset with your own data, ie: data coming from controls or any other source you can fill the dataset manually:
Dim theDataSet as New DataSet()
Dim theDataTable as new DataTable
Dim theRow as DataRow

'Adding columns to the DataTable

'Adding Rows

'Adding the DataTable to the DataSet


hope all this clarify a bit
sumo_the_catAuthor Commented:
Ahh. Thanks for that.

TheLearnedOne, that looks brilliant. But I'm being a bit stupid - I can't find the Microsoft.ApplicationBlocks.Data.dll after downloading this. The help just says "Set a reference to it" - but I can't find it (even after searching my harddrive!)
sumo_the_catAuthor Commented:
Had to build the project which compiled into a .dll. Then referenced it. Thanks for that golden nugget, LearnedOne.
sumo_the_catAuthor Commented:
ryancys answered the question I actually asked, though didn't quite mean, so s/he gets the accepted answer.
Cheers averyone.
look for the dll file name you just downloaded (fileName.dll) and paste it in the bin directory, then right click the Reference node in the Solution Explorer and hit the Add Reference command,a dialog box should appears, select the Project tab and hit the Browse button to navigate to the bin directory and then select the fileName.dll file.
sumo_the_catAuthor Commented:
like i said, had to compile the project to make the .dll - no dll provided by M$. BTW, you wouldn't need to paste to the bin dir anyway, since adding a reference to a project copies all the necessary files to your bin dir automatically.
sumo, i have posted my comment before notice that you have closed this thread

...yes it is no necesary to copy the project manually, it is just that i use to copy the assamble directly to the bin folder when it comes from a download, but when i have to program it my self i do the usual as you do.
sumo_the_catAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.