vsshah
asked on
VB .NET, SQL Server 2000, & Data Concurrency
Hi,
Having some issues with a VB .NET front end with a SQL 2000 back end. I used some code found on the net and it traps the concurrency error with no problems. The problem I have with the code is a small section that deals with the key value on the table...it seems like it wants only a single column value for the key, but I'm using mulitple columns for the primary key and the command doesn't except multiple arguments.
Here's the code being used:
'The following is only the portion to catch the concurrency on the Update
Try
Me.daMaintCarMaintSQL.Upda te(Me.dsMa intCarMain tSQL1)
Catch dbcEx As Data.DBConcurrencyExceptio n
Dim dResult As DialogResult
dResult = MessageBox.Show("The underlying data for the car information has been changed while you were working with the data. Would you like to reload the form to see the new data, or simply apply your changes anyway. Click Yes to see the new data, or No to apply your changes anyway." & vbCrLf & dbcEx.Message, _
"Data Concurrency Exception Occurred", _
MessageBoxButtons.YesNoCan cel, MessageBoxIcon.Error, _
MessageBoxDefaultButton.Bu tton1, _
MessageBoxOptions.DefaultD esktopOnly )
If dResult = DialogResult.Yes Then
UpdateRow("UTC_QC_CARMAINT ", dbcEx.Row.Item(0)) '********This is where the error is happening. The dbcEx.Row.Item() only wants to accept 1 value there. I've tried using what I call the PK on SQL Server, but it doesn't like that either.
Exit Sub
ElseIf dResult = DialogResult.No Then
'Save a copy of the new row
Dim drCopy As DataRow, drCurrent As DataRow
drCopy = dsMaintCarMaintSQL1.Tables ("UTC_QC_C ARMAINT"). NewRow()
Dim dc As DataColumn
drCurrent = dsMaintCarMaintSQL1.Tables ("UTC_QC_C ARMAINT"). Rows.Find( dbcEx.Row. Item(0))
For Each dc In drCurrent.Table.Columns
If dc.ReadOnly = False Then drCopy.Item(dc.ColumnName) = drCurrent.Item(dc.ColumnNa me)
Next
'Get the current values from the database
UpdateRow("UTC_QC_CARMAINT ", dbcEx.Row.Item(0))
'Now restore the values entered by the user and save again
For Each dc In drCurrent.Table.Columns
If dc.ReadOnly = False Then _
drCurrent.Item(dc.ColumnNa me) = drCopy.Item(dc.ColumnName)
Next
daMaintCarMaintSQL.Update( dsMaintCar MaintSQL1. Tables("UT C_QC_CARMA INT"))
End If
End Try
'The Sub Being called by Above Try...Catch
Private Sub UpdateRow(ByVal TableName As String, ByVal ID As String)
'Get a reference to the specified row
Dim dr As DataRow = dsMaintCarMaintSQL1.Tables (TableName ).Rows.Fin d(ID)
'Create a Command update to pull the new underlying data
Dim cmd As New SqlClient.SqlCommand("SELE CT * FROM " & TableName & _
" WHERE ID=" & ID.ToString(), frmNav.myConnectionSQL)
'Open the connection and create the DataReader
frmNav.myConnectionSQL.Ope n()
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
rdr.Read()
'Copy the new data from the database to the DataRow
Dim dc As DataColumn
For Each dc In dr.Table.Columns
If dc.ReadOnly = False Then _
dr.Item(dc.ColumnName) = rdr.Item(dc.ColumnName)
Next
'Accept changes in the DataRow
dr.AcceptChanges()
frmNav.myConnectionSQL.Clo se()
End Sub
So, I guess my question is how I can use the above code, but with a composite primary key? As stated earlier, when I try to use the name I gave the Primary key when I defined it in SQL Server, it doesn't recognize it as being a legitimate column.
Having some issues with a VB .NET front end with a SQL 2000 back end. I used some code found on the net and it traps the concurrency error with no problems. The problem I have with the code is a small section that deals with the key value on the table...it seems like it wants only a single column value for the key, but I'm using mulitple columns for the primary key and the command doesn't except multiple arguments.
Here's the code being used:
'The following is only the portion to catch the concurrency on the Update
Try
Me.daMaintCarMaintSQL.Upda
Catch dbcEx As Data.DBConcurrencyExceptio
Dim dResult As DialogResult
dResult = MessageBox.Show("The underlying data for the car information has been changed while you were working with the data. Would you like to reload the form to see the new data, or simply apply your changes anyway. Click Yes to see the new data, or No to apply your changes anyway." & vbCrLf & dbcEx.Message, _
"Data Concurrency Exception Occurred", _
MessageBoxButtons.YesNoCan
MessageBoxDefaultButton.Bu
MessageBoxOptions.DefaultD
If dResult = DialogResult.Yes Then
UpdateRow("UTC_QC_CARMAINT
Exit Sub
ElseIf dResult = DialogResult.No Then
'Save a copy of the new row
Dim drCopy As DataRow, drCurrent As DataRow
drCopy = dsMaintCarMaintSQL1.Tables
Dim dc As DataColumn
drCurrent = dsMaintCarMaintSQL1.Tables
For Each dc In drCurrent.Table.Columns
If dc.ReadOnly = False Then drCopy.Item(dc.ColumnName)
Next
'Get the current values from the database
UpdateRow("UTC_QC_CARMAINT
'Now restore the values entered by the user and save again
For Each dc In drCurrent.Table.Columns
If dc.ReadOnly = False Then _
drCurrent.Item(dc.ColumnNa
Next
daMaintCarMaintSQL.Update(
End If
End Try
'The Sub Being called by Above Try...Catch
Private Sub UpdateRow(ByVal TableName As String, ByVal ID As String)
'Get a reference to the specified row
Dim dr As DataRow = dsMaintCarMaintSQL1.Tables
'Create a Command update to pull the new underlying data
Dim cmd As New SqlClient.SqlCommand("SELE
" WHERE ID=" & ID.ToString(), frmNav.myConnectionSQL)
'Open the connection and create the DataReader
frmNav.myConnectionSQL.Ope
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
rdr.Read()
'Copy the new data from the database to the DataRow
Dim dc As DataColumn
For Each dc In dr.Table.Columns
If dc.ReadOnly = False Then _
dr.Item(dc.ColumnName) = rdr.Item(dc.ColumnName)
Next
'Accept changes in the DataRow
dr.AcceptChanges()
frmNav.myConnectionSQL.Clo
End Sub
So, I guess my question is how I can use the above code, but with a composite primary key? As stated earlier, when I try to use the name I gave the Primary key when I defined it in SQL Server, it doesn't recognize it as being a legitimate column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
UpdateRow("UTC_QC_CARMAINT
The only other way I can think to do it is to make an array out of the composite key, but I'm going to try your way first.
Thanks.