VB .NET, SQL Server 2000, & Data Concurrency

Posted on 2006-05-19
Last Modified: 2010-07-27

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 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
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, _
                    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)

                        '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)

                    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
        Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()

        '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)

        'Accept changes in the DataRow
    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.

Question by:vsshah
    LVL 3

    Accepted Solution

    It looks like you are passing in one field in the row.

    Try this ...

    UpdateRow("UTC_QC_CARMAINT", dbcEx.Row.Item(0), dbcEx.Row.Item(1) )
    or even better
    UpdateRow("UTC_QC_CARMAINT", dbcEx.Row.Item("ID"), dbcEx.Row.Item("ID2") )

    Private Sub UpdateRow(ByVal TableName As String, ByVal ID As String, byval ID2 as string)

      Dim cmd As New SqlClient.SqlCommand("SELECT * FROM " & TableName & _
             " WHERE ID=" & ID.ToString() & " ID2=" & ID2, frmNav.myConnectionSQL)


    Author Comment

    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.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now