Stored Procedures with SQLCommandBuilder

[microsoft.private.windows.netserver.setup]

I have a very strange thing occurring in the program. I have a dataset
retrieved from a stored procedure that just select * from a table. I then
try to use the SQlCommandBuilder on the dataset, and fails. I try the same
select statement directly and not using a stored procedure and use
SQLCommandBuilder, the program works. This is a ASP.net page, and I am
stumped. I would like to use the stored procedure rather than controlling
it in the ASP.net page. Can anyone help? Thanks.

 

[Colin Basterfield]

For a start, though not the answer you're currently looking for I would
separate out all your database activity into a separate assembly.

Unfortunately I've not used SQLCommandBuilder yet, so this is all I can
currently contiribute.

FWIW
Colin

 

[John Amick]

To add to Colin's comment, the Data Access Application Block released by
MSFT is a nice set of pre-written
functions for DB access, and a good place to start.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

-John

 

[microsoft.private.windows.netserver.setup]

It has to be doable. I am sure it is just the TableMappings of the stored
procedure. The result set from the stored procedure is sp_WellProperties,
but the SqlCommandBuilder is asking for WellProperties which is the actual
name of the table. Even when I use sqlAdaptor.TableMappings.Add("Table",
"WellProperties") it still doesn't work. I am quite sure I have
WellProperties incorrect.in the TableMappings Add method. Is there a way to
get the actual name of the dataset that was returned by the stored
procedure? Thanks.

To add to Colin's comment, the Data Access Application Block released by
MSFT is a nice set of pre-written
functions for DB access, and a good place to start.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

-John

 

[John Amick]

Could you post your code? That might help someone come up with an answer
for you.

-John

It has to be doable. I am sure it is just the TableMappings of the stored
procedure. The result set from the stored procedure is sp_WellProperties,
but the SqlCommandBuilder is asking for WellProperties which is the actual
name of the table. Even when I use sqlAdaptor.TableMappings.Add("Table",
"WellProperties") it still doesn't work. I am quite sure I have
WellProperties incorrect.in the TableMappings Add method. Is there a way to
get the actual name of the dataset that was returned by the stored
procedure? Thanks.

To add to Colin's comment, the Data Access Application Block released by
MSFT is a nice set of pre-written
functions for DB access, and a good place to start.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

 

[microsoft.private.windows.netserver.setup]

I have pasted the code from the original file. I am sure that it is because
it is trying to write back to the sp_WellPropertiesDatasets stored
procedure. Of course this can't be done, becuase the stored proc just
retrieves the table. I then tried a table mapping, but I think I have the
wrong original table name, thus in SqlCommandBuilder it still thinks it is
coming from a table named sp_WellPropertiesDatasets. Any help greatly
appreciated, thanks.

Code;
'loads each section of the WellProperties table
Sub Load_UserForm()
sqlSQLCommand = New SqlCommand( strSQL2, sqlConnect )
sqlDataAdaptor = New SqlDataAdapter( sqlSQLCommand )

'add the table mappings (parameters is name of new table name, and
original table name)
sqlDataAdaptor.TableMappings.Add( "Table", "WellProperties" )

sqlDataAdaptor.Fill( sqlDataSet1, "Table" )
'Response.Write(sqlDataSet1.DataSetName)

'bind to the datagrid, the key is ID field
WellProperties.Datasource() = sqlDataset1
WellProperties.DataBind()
End Sub

'save the changes back to the database
Sub SaveDatasets(s As Object, e as EventArgs)
'change a piece of data
'sqlDataset1.Tables("WellProperties").Rows(0)(2) = "Test4"
'original value is Heavy

'create the sqlcommandbuilder
Dim cbWellProperties As New SqlCommandBuilder( sqlDataAdaptor )

'display the update statement of the records that were changed
strDebug.Text = cbWellProperties.GetUpdateCommand().CommandText

'push the updates to the database
sqlDataAdaptor.Update( sqlDataSet1 )

'bind to the datagrid, the key is ID field
WellProperties.Datasource() = sqlDataset1
WellProperties.DataBind()
End Sub