469,271 Members | 1,399 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

DATAGRID - How can I change UpdateCommand dynamically ?

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
Nov 15 '05 #1
3 2490
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


Nov 15 '05 #2
"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))
Nov 15 '05 #3
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
Nov 15 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Sebi | last post: by
3 posts views Thread by Jim Heavey | last post: by
1 post views Thread by MrMike | last post: by
2 posts views Thread by Deepesh | last post: by
reply views Thread by herman404 | last post: by
9 posts views Thread by rn5a | last post: by
reply views Thread by arlie_maija | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.