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 ??????
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