DATAGRID - How can I change UpdateCommand dynamically ?

[Andrew Dodgshun]

I have 2 grids - one shows a list of table names in a database and
when you click on a table name the other grid dynamically populates
the grid with the table contents. My problem is that I cannot get any
changes I do in the grid of a given table saved back to the database
unless I nominate a particular table in the SQLDataAdapter but this is
not dynamic as the table changes when the user picks another table
I have the first part working nicely this way..

' for the list of table names
dim cmd = New System.Data.Sql Client.SqlComma nd("SELECT name FROM
sysobjects WHERE (xtype = 'U') and name <> 'dtproperties'" ,
SqlConnection)
SqlDataAdapterV OL.SelectComman d = cmd
DataSetVOL.Clea r()
SqlDataAdapterV OL.Fill(DataSet VOL)
DataGridVol.Exp and(-1)
DataGridVol.Dat aMember = DataSetVOL.Tabl es(0).TableName
DataGridVol.Sel ect(0)

'for the table contents
cmd = "SELECT * FROM " &
DataGridVol.Ite m(DataGridVol.C urrentCell.RowN umber, 0), SqlConnection)
SqlDataAdapterV OL.SelectComman d = cmd
DataSetWI.Table s.Clear()
DataSetWI.Table s.Add(DataGridV ol.Item(DataGri dVol.CurrentCel l.RowNumber,
0))
SqlDataAdapterV OL.Fill(DataSet WI, DataGridVol.Ite m
DataGridVol.Cur rentCell.RowNum ber, 0))
DataGridWI.Data Source = DataSetWI
DataGridWI.Expa nd(-1)
DataGridWI.Data Member = DataSetWI.Table s(0).TableName

but when I want to update a cell in a table it
does not update in the database unless the table is specified in the
adapter with all the sql command for update, delete etc.
Do I have to programmaticall y update the SQL "update" command for the
new table ? Or is there a way for it to work out the update command
dynamically given I know the newly selected table name ?

'the update bit I use is
DataSetWI.Table s(0).Rows(row). Item(i) = Avalue
DataGridWI.Item (row, i) = Avalue
Dim cb As New System.Data.Sql Client.SqlComma ndBuilder(SqlDa taAdapterWI)
SqlDataAdapterW I.Update(DataSe tWI, DataGridVol.Ite m
DataGridVol.Cur rentCell.RowNum ber, 0))

Maybe its not possible to use a SQLDataAdapter on more than one table
?

thanks
Andrew

[Dmitriy Lapshin [C# / .NET MVP]]

Hello Andrew,

According to the MSDN topic titled "SqlDataAdapter .UpdateCommand Property":

----------------------------------------------------------------------------
---------------------------
During Update, if this property is not set and primary key information is
present in the DataSet, the
UpdateCommand can be generated automatically if you set the SelectCommand
property and use the
SqlCommandBuild er. Then, any additional commands that you do not set are
generated by the
SqlCommandBuild er. This generation logic requires key column information to
be present in the
DataSet. For more information see Automatically Generated Commands.
----------------------------------------------------------------------------
---------------------------

Does this answer your question?

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://x-unity.miik.com.ua/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

"Andrew Dodgshun" <an************ *@eistream.com. au> wrote in message
news:8a******** *************** ***@posting.goo gle.com...

I have 2 grids - one shows a list of table names in a database and
when you click on a table name the other grid dynamically populates
the grid with the table contents. My problem is that I cannot get any
changes I do in the grid of a given table saved back to the database
unless I nominate a particular table in the SQLDataAdapter but this is
not dynamic as the table changes when the user picks another table
I have the first part working nicely this way..

' for the list of table names
dim cmd = New System.Data.Sql Client.SqlComma nd("SELECT name FROM
sysobjects WHERE (xtype = 'U') and name <> 'dtproperties'" ,
SqlConnection)
SqlDataAdapterV OL.SelectComman d = cmd
DataSetVOL.Clea r()
SqlDataAdapterV OL.Fill(DataSet VOL)
DataGridVol.Exp and(-1)
DataGridVol.Dat aMember = DataSetVOL.Tabl es(0).TableName
DataGridVol.Sel ect(0)

'for the table contents
cmd = "SELECT * FROM " &
DataGridVol.Ite m(DataGridVol.C urrentCell.RowN umber, 0), SqlConnection)
SqlDataAdapterV OL.SelectComman d = cmd
DataSetWI.Table s.Clear()
DataSetWI.Table s.Add(DataGridV ol.Item(DataGri dVol.CurrentCel l.RowNumber,
0))
SqlDataAdapterV OL.Fill(DataSet WI, DataGridVol.Ite m
DataGridVol.Cur rentCell.RowNum ber, 0))
DataGridWI.Data Source = DataSetWI
DataGridWI.Expa nd(-1)
DataGridWI.Data Member = DataSetWI.Table s(0).TableName

but when I want to update a cell in a table it
does not update in the database unless the table is specified in the
adapter with all the sql command for update, delete etc.
Do I have to programmaticall y update the SQL "update" command for the
new table ? Or is there a way for it to work out the update command
dynamically given I know the newly selected table name ?

'the update bit I use is
DataSetWI.Table s(0).Rows(row). Item(i) = Avalue
DataGridWI.Item (row, i) = Avalue
Dim cb As New System.Data.Sql Client.SqlComma ndBuilder(SqlDa taAdapterWI)
SqlDataAdapterW I.Update(DataSe tWI, DataGridVol.Ite m
DataGridVol.Cur rentCell.RowNum ber, 0))

Maybe its not possible to use a SQLDataAdapter on more than one table
?

thanks
Andrew

[Andrew Dodgshun]

"Dmitriy Lapshin [C# / .NET MVP]" <x-****@no-spam-please.hotpop.c om> wrote in message news:<Oj******* *******@TK2MSFT NGP09.phx.gbl>. ..

Hello Andrew,

According to the MSDN topic titled "SqlDataAdapter .UpdateCommand Property":

----------------------------------------------------------------------------
---------------------------
During Update, if this property is not set and primary key information is
present in the DataSet, the
UpdateCommand can be generated automatically if you set the SelectCommand
property and use the
SqlCommandBuild er. Then, any additional commands that you do not set are
generated by the
SqlCommandBuild er. This generation logic requires key column information to
be present in the
DataSet. For more information see Automatically Generated Commands.
----------------------------------------------------------------------------
---------------------------

Does this answer your question?

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://x-unity.miik.com.ua/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Thanks for the suggestion but ....- I tried this exactly as described
in the help and still nothing gets populated in the Update Command (or
the delete or insert). The Select command gets updated correctly and
there is a primary key in the table ?
Here is my code ...

Dim cmd As System.Data.Sql Client.SqlComma nd
cmd = New System.Data.Sql Client.SqlComma nd("SELECT * FROM " &
DataGridVol.Ite m(DataGridVol.C urrentCell.RowN umber, 0), SqlConnection)
SqlDataAdapterW I.SelectCommand = cmd
Dim custCB As System.Data.Sql Client.SqlComma ndBuilder = New
System.Data.Sql Client.SqlComma ndBuilder(SqlDa taAdapterWI)
custCB.QuotePre fix = "["
custCB.QuoteSuf fix = "]"
custCB.RefreshS chema()
SqlDataAdapterW I.Fill(DataSetW I, DataGridVol.Ite m
DataGridVol.Cur rentCell.RowNum ber, 0))

[Andrew Dodgshun]

Hold everything !!
It did work it just doesn't show the sql statement in the watch window
whilst debugging - not sure why but the update works which is what
matters

thanks
Andrew