Link to home
Start Free TrialLog in
Avatar of vsshah
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.Update(Me.dsMaintCarMaintSQL1)
Catch dbcEx As Data.DBConcurrencyException
                    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.YesNoCancel, MessageBoxIcon.Error, _
                      MessageBoxDefaultButton.Button1, _
                      MessageBoxOptions.DefaultDesktopOnly)
                    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_CARMAINT").NewRow()
                        Dim dc As DataColumn
                        drCurrent = dsMaintCarMaintSQL1.Tables("UTC_QC_CARMAINT").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.ColumnName)
                        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.ColumnName) = drCopy.Item(dc.ColumnName)
                        Next

                        daMaintCarMaintSQL.Update(dsMaintCarMaintSQL1.Tables("UTC_QC_CARMAINT"))
                    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.Find(ID)

        'Create a Command update to pull the new underlying data
        Dim cmd As New SqlClient.SqlCommand("SELECT * FROM " & TableName & _
         " WHERE ID=" & ID.ToString(), frmNav.myConnectionSQL)

        'Open the connection and create the DataReader
        frmNav.myConnectionSQL.Open()
        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.Close()
    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
Avatar of HouseMusic
HouseMusic

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vsshah
vsshah

ASKER

Great idea! Let me give it a try and see how it goes...what I was doing was trying to overload the Row.Item with:

UpdateRow("UTC_QC_CARMAINT", dbcEx.Row.Item(0,1,2,3,4))

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.