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.SqlClient.SqlCommand("SELECT name FROM
sysobjects WHERE (xtype = 'U') and name <> 'dtproperties'",
SqlConnection)
SqlDataAdapterVOL.SelectCommand = cmd
DataSetVOL.Clear()
SqlDataAdapterVOL.Fill(DataSetVOL)
DataGridVol.Expand(-1)
DataGridVol.DataMember = DataSetVOL.Tables(0).TableName
DataGridVol.Select(0)

'for the table contents
cmd = "SELECT * FROM " &
DataGridVol.Item(DataGridVol.CurrentCell.RowNumber , 0), SqlConnection)
SqlDataAdapterVOL.SelectCommand = cmd
DataSetWI.Tables.Clear()
DataSetWI.Tables.Add(DataGridVol.Item(DataGridVol. CurrentCell.RowNumber,
0))
SqlDataAdapterVOL.Fill(DataSetWI, DataGridVol.Item
DataGridVol.CurrentCell.RowNumber, 0))
DataGridWI.DataSource = DataSetWI
DataGridWI.Expand(-1)
DataGridWI.DataMember = DataSetWI.Tables(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 programmatically 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.Tables(0).Rows(row).Item(i) = Avalue
DataGridWI.Item(row, i) = Avalue
Dim cb As New System.Data.SqlClient.SqlCommandBuilder(SqlDataAda pterWI)
SqlDataAdapterWI.Update(DataSetWI, DataGridVol.Item
DataGridVol.CurrentCell.RowNumber, 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
SqlCommandBuilder. Then, any additional commands that you do not set are
generated by the
SqlCommandBuilder. 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.google.c om...

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.SqlClient.SqlCommand("SELECT name FROM
sysobjects WHERE (xtype = 'U') and name <> 'dtproperties'",
SqlConnection)
SqlDataAdapterVOL.SelectCommand = cmd
DataSetVOL.Clear()
SqlDataAdapterVOL.Fill(DataSetVOL)
DataGridVol.Expand(-1)
DataGridVol.DataMember = DataSetVOL.Tables(0).TableName
DataGridVol.Select(0)

'for the table contents
cmd = "SELECT * FROM " &
DataGridVol.Item(DataGridVol.CurrentCell.RowNumber , 0), SqlConnection)
SqlDataAdapterVOL.SelectCommand = cmd
DataSetWI.Tables.Clear()
DataSetWI.Tables.Add(DataGridVol.Item(DataGridVol. CurrentCell.RowNumber,
0))
SqlDataAdapterVOL.Fill(DataSetWI, DataGridVol.Item
DataGridVol.CurrentCell.RowNumber, 0))
DataGridWI.DataSource = DataSetWI
DataGridWI.Expand(-1)
DataGridWI.DataMember = DataSetWI.Tables(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 programmatically 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.Tables(0).Rows(row).Item(i) = Avalue
DataGridWI.Item(row, i) = Avalue
Dim cb As New System.Data.SqlClient.SqlCommandBuilder(SqlDataAda pterWI)
SqlDataAdapterWI.Update(DataSetWI, DataGridVol.Item
DataGridVol.CurrentCell.RowNumber, 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.com> wrote in message news:<Oj**************@TK2MSFTNGP09.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
SqlCommandBuilder. Then, any additional commands that you do not set are
generated by the
SqlCommandBuilder. 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.SqlClient.SqlCommand
cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM " &
DataGridVol.Item(DataGridVol.CurrentCell.RowNumber , 0), SqlConnection)
SqlDataAdapterWI.SelectCommand = cmd
Dim custCB As System.Data.SqlClient.SqlCommandBuilder = New
System.Data.SqlClient.SqlCommandBuilder(SqlDataAda pterWI)
custCB.QuotePrefix = "["
custCB.QuoteSuffix = "]"
custCB.RefreshSchema()
SqlDataAdapterWI.Fill(DataSetWI, DataGridVol.Item
DataGridVol.CurrentCell.RowNumber, 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