asked on

Updating a SQL Server table

Hi all,

I am using the following code to transfer items from one datagridview to another and then update the sql server table with the new data.

Private Sub BTN_stf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_stf.Click

Dim STR_part As String = ""
Dim STR_gdr As String = ""
Dim STR_type As String = ""

'go through the selected rows
For Each dgvr As DataGridViewRow In DGV_gdrstfs.SelectedRows

STR_part = dgvr.Cells("PRT_MARK").Value
STR_gdr = dgvr.Cells("PRT_GDR_NO").Value
STR_type = dgvr.Cells("PRT_PART_TYPE1").Value

Dim newrow As DataRow = DS_weld1.WELD.NewRow
newrow("WLD_SORT") = dgvr.Cells("PRT_ITEM_KEY").Value
newrow("WLD_PART_ID") = STR_part
newrow("WLD_GDR_NO") = STR_gdr
newrow("WLD_CONT") = PUB_Cont
newrow("WLD_STR") = PUB_Str
newrow("WLD_THICKNESS") = dgvr.Cells("PRT_THICKNESS").Value
newrow("WLD_PART_TYPE") = dgvr.Cells("PRT_PART_TYPE1").Value
newrow("WLD_FACE") = ""
newrow("WLD_END") = ""

DS_weld1.WELD.Rows.Add(newrow)

Next

If DS_weld1.HasChanges Then

SDA_weld.Update(DS_weld1.GetChanges())

End If

End Sub

My problem is that. When I move the 1st item over (say item no P10), I see one new record in my SQL server table. However, when I now move the 2nd item (say P15), I now see two P10's. But why is this? I can still only see one P10 in my datagridview.

Any help would be great.

Cheers.

vbturbo

Try use the commandbuilder object

If DS_weld1.HasChanges Then

Dim cb As New OleDb.OleDbCommandBuilder(SDA_weld)
SDA_weld.Update(DS_weld1, "MyTableName")
End If

vbturbo

vbturbo

try have a look here

https://filedb.experts-exchange.com/incoming/ee-stuff/5731-Relations.zip

type some data in the one of the grids or create/delete a row(s)

then do the update made to the tables

vbturbo

FMabey

ASKER

Hi,

thanks for replying, but SDA_weld is a SQL data adapter. Not OleDb.

ASKER CERTIFIED SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbturbo

For the adapter to perform update and delete commands your table has to be setup with a primary key

FMabey

ASKER

Yep, it has a primary key... It's an identity field.

Ok, this works:

If DS_weld.haschanges() then

SDA_weld.Update(DS_weld1, "WELD")

endif

But why doesn't my previous code work properly? I use it in numerous other places at it works fine. Which is the best method to use?

vbturbo

The SqlDataAdapter does not automatically generate the Transact-SQL statements required to reconcile changes made to a DataSet with the associated instance of SQL Server. However, you can create a SqlCommandBuilder object to automatically generate Transact-SQL statements for single-table updates if you set the SelectCommand property of the SqlDataAdapter. Then, any additional Transact-SQL statements that you do not set are generated by the SqlCommandBuilder.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

vbturbo