Updating DataSet/DataTable

[Steve Schroeder]

I've brought in data to an ASPX page to a datagrid via ADO.Net before, but
what I need to do now is to bring in data from a stored procedure (actually
two procedures, and two result sets) and update the DataSet or DataTable.

Can I do this and bind the updated results to a datagrid? I don't need to
write the data back to the datasource, I just need to do something dynamic
where otherwise cursors would be needed in the stored procedures, and
obviously I want to avoid that.

From what I've read I should be able to update a Dataset/DataTable much like
it were a regular table....

Thanks,

Steve

 

[Steve Schroeder]

Well, this is as far as I could get without generating an error. When I go
to fill the SQLDataAdapter, I get a run time error. But because debugging is
such a pain in .Net/ASP.Net I don't have enough information to go on. Note
that I do have the connection object defined as a module level object.

adoConn.Open()
Dim adoInvoiceProc As New SqlCommand()
adoInvoiceProc.Connection = adoConn
adoInvoiceProc.CommandType = CommandType.StoredProcedure
adoInvoiceProc.CommandText = "MG_InvoiceProc"
adoInvoiceProc.Parameters.Add(New SqlParameter("@iInvoice", 7211391))
Dim adoSqlDA As New SqlDataAdapter(adoInvoiceProc)
adoSqlDA.Fill(adoDs, "Invoice") '<---Error occurs here
adoConn.Close

I just want to be able to work with the results of the procedure as a
classic recordset. At this point I'm not trying to bind anything to a
datagrid.
Any pointers would be greatly appreciated. Thanks.

 

[Guest]

Steve,

Have you instantiated your adoDS first? (You don't say what the error is).

~~Bonnie

 

[Steve Schroeder]

Actually I noted where the error occurs. See: '<---Error occurs here. I did
define the dataset object earlier in the code, my bad.

Trying a different approach using a datareader instead. But still no luck.
Here is the code and error message:

Dim adoConn As New SqlConnection("Data Source=******;Initial
Catalog=******;User ID=******;Password=******;")
Dim adoInvoiceProc As New SqlCommand()
Dim InvoiceDataReader As New SqlDataReader()

adoConn.Open()

adoInvoiceProc.Connection = adoConn
adoInvoiceProc.CommandType = CommandType.StoredProcedure
adoInvoiceProc.CommandText = "MG_InvoiceProc"
adoInvoiceProc.Parameters.Add(New SqlParameter("@iInvoice", 7211391))
InvoiceDataReader =
adoInvoiceProc.ExecuteReader(CommandBehavior.CloseConnection)

adoConn.Close()

Error Message:
'System.Data.SqlClient.SqlDataReader.Private Overloads Sub New(command As
System.Data.SqlClient.SqlCommand)' is not accessible in this context because
it is 'Private'.

Note also that this doesn't build and that Dim InvoiceDataReader...is
underlined.

Merely trying to open a forwardonly recordset from a procedure so I can
iterate through it. Shouldn't be this difficult....

Thanks

 

[W.G. Ryan eMVP]

You have a scope issue, Double check your spellings and then make sure that
things are declared at the module level or as properties if you are
referrring to them and htey aren't declared locally.

 

[W.G. Ryan eMVP]

If you send me the VB file, WilliamRyan At Gmail Dot Com - I'll take a look
at it for you and try to fix it.

 

[Steve Schroeder]

Thanks. I got my initial problem resolved. I has switched back and for from
saying: As New...to As...without removing the parens (). Oddly enough, that
was the issue. Of course, I didn't put the parens there in the first place.
The IDE did.

Try it...define an object As New...the take out the New...