Stored Procedures with SQLCommandBuilder

  • Thread starter Thread starter microsoft.private.windows.netserver.setup
  • Start date Start date
M

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

John Amick said:
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
Click to expand...
 
Could you post your code? That might help someone come up with an answer
for you.

-John

microsoft.private.windows.netserver.setup said:
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.

John Amick said:
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.
Click to expand...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
Click to expand...
 
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
 
Back
Top