Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-04-22
15
Medium Priority
?
2,316 Views
Last Modified: 2008-09-23
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.
Thanks!
0
Comment
Question by:sumo_the_cat
[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
  • 4
  • 2
  • +1
15 Comments
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 400 total points
ID: 10892719
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"

        conn.Open()

        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."

        conn.Close()

Try customize according to your requirement. cheers
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 10892737
oops, try change the line of :

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

to

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

then define:

Dim selectedSP As String = "MyStoredProcedure"

Hope this helps

0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10892802
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 10

Expert Comment

by:EBatista
ID: 10893091
well, its not absolutly necessary to use dataadapter, you could use an datareader instead:
DataGrid1.DataSource=yourCommand.ExecuteReader()
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10893435
DataGrid1.DataSource=yourCommand.ExecuteReader()

I'm sure that's the first thing I tried! I'll let you know when I get back in a couple of hours!
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10894489
Whoops, misread that in my haste to get to the door. EBatista, I want to put the data into a dataset, not a datagrid.
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 400 total points
ID: 10894949
You can use all the code, or you can use the Data Access Application Block from Microsoft:

Data Access Application Block for .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

(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.

Bob

0
 
LVL 10

Assisted Solution

by:EBatista
EBatista earned 400 total points
ID: 10896111
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")
yourDataAdapter.Fill(yourDataSet,"yourDataTableName")


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
theDataTable.Columns.Add("UserId")
theDataTable.Columns.Add("UserName")

'Adding Rows
theRow=theDataTable.NewRow()
theRow("UserId")=TextBox1.Text
theRow("UserName")=TextBox2.Text

'Adding the DataTable to the DataSet
theDataSet.Tables.Add(theDataTable)

yourDataSet.Tables.Add(yourDataTable)

hope all this clarify a bit
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10897373
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!)
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10902789
Had to build the project which compiled into a .dll. Then referenced it. Thanks for that golden nugget, LearnedOne.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10902826
ryancys answered the question I actually asked, though didn't quite mean, so s/he gets the accepted answer.
Cheers averyone.
0
 
LVL 10

Expert Comment

by:EBatista
ID: 10903132
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.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10903183
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.
0
 
LVL 10

Expert Comment

by:EBatista
ID: 10903452
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.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10905034
cool!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

604 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