fill dataset from Sql Server stored procedure?

**Rich** · Nov 21 '05, 05:34 PM

RE: fill dataset from Sql Server stored procedure?

OK. Now I remember my dilema. ADO.Net doesn't use recordset objects. So
how can I fill my data table/dataset from a stored procedure?

"Rich" wrote:
[color=blue]
> I have a stored procedure on Sql Server2k. I can fill a data table which I
> can append to a dataset using an ADODB recordset object which gets populated
> from a command object that runs the sp. I was hoping to use a DataAdapter.
> But I think the data adapter only uses select statements. I could write the
> sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
> will guess that I will need to stick with the ADODB recordset object for
> this. But I am open to any better suggestions on how to fill the
> datatable/dataset with the data from the stored procedure.
>
> Thanks,
> Rich[/color]

**Rich** · Nov 21 '05, 05:34 PM

RE: fill dataset from Sql Server stored procedure?

Of course, the SqlDataReader. It's all coming back to me. Just haven't done
this for a while. Actually, I want to populate a datagrid with this. So I
am thinkin I will populate the datatable from the Reader object and set the
datagrid datasource to the datatable. Am I on track here? can a reader be
used as a datasource?

"Rich" wrote:
[color=blue]
> OK. Now I remember my dilema. ADO.Net doesn't use recordset objects. So
> how can I fill my data table/dataset from a stored procedure?
>
> "Rich" wrote:
>[color=green]
> > I have a stored procedure on Sql Server2k. I can fill a data table which I
> > can append to a dataset using an ADODB recordset object which gets populated
> > from a command object that runs the sp. I was hoping to use a DataAdapter.
> > But I think the data adapter only uses select statements. I could write the
> > sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
> > will guess that I will need to stick with the ADODB recordset object for
> > this. But I am open to any better suggestions on how to fill the
> > datatable/dataset with the data from the stored procedure.
> >
> > Thanks,
> > Rich[/color][/color]

**Roger** · Nov 21 '05, 05:34 PM

Re: fill dataset from Sql Server stored procedure?

> how can I fill my data table/dataset from a stored procedure?[color=blue]
>[/color]

Hi,
I am not an expert, but set the CommandType
to StoredProcedure .
CommandText to your StoredProc name.
[color=blue]
>I was hoping to use a DataAdapter.
> But I think the data adapter only uses select statements[/color]
DataAdapter has Select, Insert and Delete statements.

Then, DataAdapter.Fil l(DataTable)
Of course, Connection must be open.
Hope that helps,
Roger

**Steve** · Nov 21 '05, 05:34 PM

Re: fill dataset from Sql Server stored procedure?

The following code will connect to an SQL Server 2000 database, retrieve
records from a table, fill a dataset, and bind the dataset to a datagrid
object.

dim conn as SqlConnection = new SqlConnection(" Data
Source=(local); Integrated Security=SSPI; Initial Catalog=northwi nd")

dim da as SqlDataAdapter = new SqlDataAdapter( "SELECT CustomerID,
ContactName FROM Customers", thisConnection)

dim ds as dataset = new DataSet

da.Fill(ds, "Customers" )
datagrid1.datas ource = ds
datagrid1.datab ind

It isn't necessary to call the databind method if you're creating a Windows
Forms application, but is required for ASP.NET.

"Rich" <Rich@discussio ns.microsoft.co m> wrote in message
news:207AA44B-F0C2-402B-8F7E-27A401A45A6D@mi crosoft.com...[color=blue]
>I have a stored procedure on Sql Server2k. I can fill a data table which I
> can append to a dataset using an ADODB recordset object which gets
> populated
> from a command object that runs the sp. I was hoping to use a
> DataAdapter.
> But I think the data adapter only uses select statements. I could write
> the
> sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever.
> I
> will guess that I will need to stick with the ADODB recordset object for
> this. But I am open to any better suggestions on how to fill the
> datatable/dataset with the data from the stored procedure.
>
> Thanks,
> Rich[/color]

**Steve** · Nov 21 '05, 05:34 PM

Re: fill dataset from Sql Server stored procedure?

Oops.. when converting this code from C# to VB.NET, I missed changing
"thisconnection " to "conn". Change that and the code should work.

"Steve" <splanck9271@ya hoo.com> wrote in message
news:O7HdgyyYFH A.1344@TK2MSFTN GP15.phx.gbl...[color=blue]
> The following code will connect to an SQL Server 2000 database, retrieve
> records from a table, fill a dataset, and bind the dataset to a datagrid
> object.
>
> dim conn as SqlConnection = new SqlConnection(" Data
> Source=(local); Integrated Security=SSPI; Initial Catalog=northwi nd")
>
> dim da as SqlDataAdapter = new SqlDataAdapter( "SELECT CustomerID,
> ContactName FROM Customers", thisConnection)
>
> dim ds as dataset = new DataSet
>
> da.Fill(ds, "Customers" )
> datagrid1.datas ource = ds
> datagrid1.datab ind
>
> It isn't necessary to call the databind method if you're creating a
> Windows Forms application, but is required for ASP.NET.
>
> "Rich" <Rich@discussio ns.microsoft.co m> wrote in message
> news:207AA44B-F0C2-402B-8F7E-27A401A45A6D@mi crosoft.com...[color=green]
>>I have a stored procedure on Sql Server2k. I can fill a data table which
>>I
>> can append to a dataset using an ADODB recordset object which gets
>> populated
>> from a command object that runs the sp. I was hoping to use a
>> DataAdapter.
>> But I think the data adapter only uses select statements. I could write
>> the
>> sp in my vb.net app, but the sp references UDF's I wrote in the Sql
>> Sever. I
>> will guess that I will need to stick with the ADODB recordset object for
>> this. But I am open to any better suggestions on how to fill the
>> datatable/dataset with the data from the stored procedure.
>>
>> Thanks,
>> Rich[/color]
>
>[/color]

**Cor Ligthert** · Nov 21 '05, 05:35 PM

Re: fill dataset from Sql Server stored procedure?

Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

**Rich** · Nov 21 '05, 05:35 PM

Re: fill dataset from Sql Server stored procedure?

Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

"Cor Ligthert" wrote:
[color=blue]
> Rich,
>
> When you combine the answers from Roger and Steve, than you have your
> answer,
>
> I hope this helps,
>
> Cor
>
>
>[/color]

**Kerry Moorman** · Nov 21 '05, 05:35 PM

Re: fill dataset from Sql Server stored procedure?

Rich,

Here is an example. Note that this particular stored procedure requires a
parameter. Also note that this code uses OleDb objects, but you can easily
change it to use SQLClient:

Public Function GetRentedTapesB yCustomerID(ByV al CustomerID As Integer)
As DataTable

Dim cn As OleDb.OleDbConn ection = Settings.GetCon nection()
Dim cmd As New OleDb.OleDbComm and
Dim da As New OleDb.OleDbData Adapter
Dim dt As New DataTable
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = "RentedTapesByC ustomerID"
cmd.Parameters. Add("@CustomerI D", CustomerID)
cn.Open()
cmd.Connection = cn
da.SelectComman d = cmd
da.Fill(dt)
cn.Close()

Return dt

End Function

Kerry Moorman

"Rich" wrote:
[color=blue]
> Actually, I ended up using a datareader to load the data from the stored
> procedure and then loaded a datatable in a dataset from the datareader and
> binded that to the datagrid. My question is if it is possible to use a
> dataAdapter with a stored procedure? I don't think it is, is it?
>
> "Cor Ligthert" wrote:
>[color=green]
> > Rich,
> >
> > When you combine the answers from Roger and Steve, than you have your
> > answer,
> >
> > I hope this helps,
> >
> > Cor
> >
> >
> >[/color][/color]

**Brian Henry** · Nov 21 '05, 05:35 PM

Re: fill dataset from Sql Server stored procedure?

why recode for what is already coded? use a data adapter it will do all the
reader work and such for you... using a reader to do it alone is dangerous
because you can only have one open at once and have to make sure you close
it when done, etc...

"Rich" <Rich@discussio ns.microsoft.co m> wrote in message
news:72AA46EB-F0C4-4E5F-8F86-213481547687@mi crosoft.com...[color=blue]
> Actually, I ended up using a datareader to load the data from the stored
> procedure and then loaded a datatable in a dataset from the datareader and
> binded that to the datagrid. My question is if it is possible to use a
> dataAdapter with a stored procedure? I don't think it is, is it?
>
> "Cor Ligthert" wrote:
>[color=green]
>> Rich,
>>
>> When you combine the answers from Roger and Steve, than you have your
>> answer,
>>
>> I hope this helps,
>>
>> Cor
>>
>>
>>[/color][/color]

**Rich** · Nov 21 '05, 05:35 PM

Re: fill dataset from Sql Server stored procedure?

Thanks all for your replies. I didn't think the datareader was the way to
go. Also, I broke down and used the SqlDataAdapter wizard. It had stored
procedures as an optioin (I am such a lamo). Anyway, now the explanations
all make sense. Everything working OK, finally.

Thanks all for your help.

"Rich" wrote:
[color=blue]
> Actually, I ended up using a datareader to load the data from the stored
> procedure and then loaded a datatable in a dataset from the datareader and
> binded that to the datagrid. My question is if it is possible to use a
> dataAdapter with a stored procedure? I don't think it is, is it?
>
> "Cor Ligthert" wrote:
>[color=green]
> > Rich,
> >
> > When you combine the answers from Roger and Steve, than you have your
> > answer,
> >
> > I hope this helps,
> >
> > Cor
> >
> >
> >[/color][/color]

fill dataset from Sql Server stored procedure?

fill dataset from Sql Server stored procedure?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment