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
"John Amick" <an*******@discussions.microsoft.com> wrote in message
news:u9**************@TK2MSFTNGP11.phx.gbl...
Could you post your code? That might help someone come up with an answer
for you.
-John
"microsoft.private.windows.netserver.setup" <ra***********@shaw.ca> wrote
in message news:uz**************@TK2MSFTNGP10.phx.gbl... 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" <an*******@discussions.microsoft.com> wrote in message
news:em**************@TK2MSFTNGP09.phx.gbl... 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/de...ml/daab-rm.asp
-John
"Colin Basterfield" <co**************@hotmail.com> wrote in message
news:uV**************@TK2MSFTNGP09.phx.gbl...
> 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
>
> "microsoft.private.windows.netserver.setup" <ra***********@shaw.ca>
wrote in
> message news:em**************@TK2MSFTNGP10.phx.gbl...
> > 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.
> >
> >
>
>