Question Update SQL Database No Records Is Updated

[w.kalo]

My FE Is Visual Studio 2010 With SQL Database
I Have Been trying To Update Database Called Transact Where It Has 2 Fields 'TNTRNS' Invoice No. And 'SEQN' Sequence In Invoice
The Following Is My Code
I Get 0 Record Affected
No Indexes Are Set On Database
Any Help ??????

 Dim con As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\StkAcc\Databases\MyData14.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        Dim da As New SqlClient.SqlDataAdapter("Select TNTRNS, SEQN From Transact Where TNTRNS = '14200932' ORDER BY TNTRNS, SEQN", con)
        Dim updateCommand As New SqlClient.SqlCommand("UPDATE TRANSACT SET SEQN = @SEQN WHERE (TNTRNS = @Original_TNTRNS) AND (SEQN = @Original_SEQN)")
        'Dim updateCommand As New SqlClient.SqlCommand("exec prc_TrialUpdateTransact @TNTRNS, @Original_TNTRNS, @SEQN, @Original_SEQN")
        Dim ds As New DataSet
        Dim MyCount As Integer = 1
        Dim MyInvoice As String


        da.UpdateCommand = updateCommand


        da.UpdateCommand.Connection = con
        da.UpdateCommand.Parameters.Add("@SEQN", SqlDbType.NVarChar, 2, "SEQN")
        da.UpdateCommand.Parameters.Add("@Original_SEQN", SqlDbType.NVarChar, 2, "SEQN")
        da.UpdateCommand.Parameters.Add("@Original_TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS")
        da.UpdateCommand.Parameters.Add("@TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS")


        Try
            da.Fill(ds, "TRANSACT")
            Dim MyTable As DataTable = ds.Tables(0)
            Dim i As Integer


            If MyTable.Rows.Count > 0 Then
                da.UpdateCommand.Parameters("@SEQN").Value = MyTable.Columns("SEQN").ToString
                da.UpdateCommand.Parameters("@Original_SEQN").Value = MyTable.Columns("SEQN").ToString
                da.UpdateCommand.Parameters("@Original_TNTRNS").Value = MyTable.Columns("TNTRNS").ToString
                da.UpdateCommand.Parameters("@TNTRNS").Value = MyTable.Columns("TNTRNS").ToString
                MyInvoice = MyTable.Rows(0).Item("TNTRNS")
                For i = 0 To MyTable.Rows().Count - 1
                    If MyInvoice = MyTable.Rows(i).Item("TNTRNS").ToString Then
                        With MyTable.Rows(i)
                            .Item("SEQN") = Format(MyCount, "00")
                            MyCount += 1
                        End With
                    Else
                        MyCount = 1
                        MyInvoice = MyTable.Rows(i).Item("TNTRNS")
                        With MyTable.Rows(i)
                            .Item("SEQN") = Format(MyCount, "00")
                            MyCount += 1
                        End With
                    End If
                    Console.WriteLine(MyInvoice & " " & MyTable.Rows(i).Item("SEQN"))
                Next
            End If
            con.Open()


            da.UpdateCommand.ExecuteNonQuery()
            da.ContinueUpdateOnError = True
            da.Update(MyTable)
            If MyTable.HasErrors Then
                Dim strMessage As String
                strMessage = "The Following row(s) were Not Updated Successfully: "
                Dim row As DataRow
                For Each row In MyTable.Rows
                    If row.HasErrors Then
                        strMessage &= vbCrLf & row("SEQN") & " - " & row.RowError
                    End If
                Next
                MessageBox.Show(strMessage)
            Else
                MessageBox.Show("All Updates Succeeded")
            End If


        Catch ex As Exception
            'MessageBox.Show(ex.Message)


        Finally
            con.Close() 'not needed if the connection was closed to begin with
        End Try

 

[jmcilhinney]

Firstly, get rid of that call to ExecuteNonQuery. What exactly is that even for when it's the call to Update that is supposed to save the changes? When you call Update, it returns a number. What is that number?

 

[w.kalo]

Concurrency Violation

Firstly, get rid of that call to ExecuteNonQuery. What exactly is that even for when it's the call to Update that is supposed to save the changes? When you call Update, it returns a number. What is that number?

Many Thanks For Your reply
My Sql Table Does Not Have Primary Key Set
On Excuting Command Through Attached Code I get Concurrency Violation, No Records Were Updated

While If I Execute the Same Update From An SQL Stored Procedure The Update Is Successful

My Purpose Of The Code That I Have A Table Of Invoice Transaction And SEQN Is The Order Of The Item In The Invoice Details Some Invoices Have Duplicate SEQN I Want To Run A Routine That Calls Each Invoice, Count The No Of Records And Set Each Record With A Number To Enable Me To Assign The Column TNTRNS And SEQN As Primary key

 

[jmcilhinney]

You have a number of issues relating to your parameters. You seem to be aware that, without the PK, you have to use the original values from some of your columns to identify the record when you update it but your not actually doing that. This part:

       da.UpdateCommand.Connection = con
       da.UpdateCommand.Parameters.Add("@SEQN", SqlDbType.NVarChar, 2, "SEQN")
       da.UpdateCommand.Parameters.Add("@Original_SEQN", SqlDbType.NVarChar, 2, "SEQN")
       da.UpdateCommand.Parameters.Add("@Original_TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS")
       da.UpdateCommand.Parameters.Add("@TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS")

should be like this:

       da.UpdateCommand.Connection = con
       da.UpdateCommand.Parameters.Add("@SEQN", SqlDbType.NVarChar, 2, "SEQN")
       da.UpdateCommand.Parameters.Add("@Original_SEQN", SqlDbType.NVarChar, 2, "SEQN").SourceVersion = DataRowVersion.Original
       da.UpdateCommand.Parameters.Add("@Original_TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS").SourceVersion = DataRowVersion.Original
       da.UpdateCommand.Parameters.Add("@TNTRNS", SqlDbType.NVarChar, 8, "TNTRNS")

If you don't do that then you're using the new values to try to identify the record and they may either not be found or refer to the wrong record.

That's not all though. You need to get rid of that bit that sets the Value of each parameter. You don't set the Value. It gets pulled automatically from the DataTable when you call Update.

 

[w.kalo]

I Have Made The Changes You Have Suggested And It Worked Fine

Many Thanks