Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

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.
0
FMabey
Asked:
FMabey
  • 5
  • 2
1 Solution
 
vbturboCommented:
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
0
 
vbturboCommented:
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
0
 
FMabeyAuthor Commented:
Hi,

thanks for replying, but SDA_weld is a SQL data adapter. Not OleDb.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vbturboCommented:
Sorry

Dim cb As New SqlClient.SqlCommandBuilder(SDA_weld)
0
 
vbturboCommented:
For the adapter to perform update and delete commands your table has to be setup with a primary key
0
 
FMabeyAuthor Commented:
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?
0
 
vbturboCommented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now