Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


VB .NET, SQL Server 2000, & Data Concurrency

Posted on 2006-05-19
Medium Priority
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 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
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

Accepted Solution

HouseMusic earned 2000 total points
ID: 16718188
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

ID: 16719086
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 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