none
Concurrancy and Identity Columns RRS feed

  • Question

  • ADO.Net VS2010 VB.net target .net 2.0

    I have a table holding INI values. Its primary key is an idenity column. The first time a given set of values passes thoruhg the code a record is added. The second time the value and date values are changed. On the first update during the session I get concurrancy errors (most of the time but not always) on the update if I don't call Refesh() during the add step. Refresh() wipes out the dataset and starts over.

    How can I add a record with an identity column and then perform an update w/o reloading the entire table?

     

    Sub PutValue(ByVal SectionForPut As StringByVal KeyForPut As StringByVal ValueForPut As String)
            If SectionForPut = "MostRecentlyUsed" Then
                ValueForPut = MRUPush(KeyForPut, ValueForPut)
            End If
            Dim row() As DataRow = dtINIStaff.Select("Staff='" & Staff & "' AND Section='" & SectionForPut & "' AND Key='" & KeyForPut & "'")
            If row.Length = 1 Then
                If ValueForPut.Length > 0 Then
                    row(0)("Value") = ValueForPut
                    row(0)("Last_Update_Date") = Now()
                Else
                    row(0).Delete()
                End If
                Try
                    daINIStaff.Update(dsINIStaff)
                Catch ex As Exception
                    Debug.Print(ex.Message)
                    ' occasional unexplained concurrency violations
                End Try
            Else
                Dim newRow As DataRow = dtINIStaff.NewRow
                ' Identity column as PK
                newRow("Version") = Version
                newRow("Staff") = Staff
                newRow("Section") = SectionForPut
                newRow("Key") = KeyForPut
                newRow("Value") = ValueForPut
                newRow("Last_Update_Date") = Now()
                Try
                    dtINIStaff.Rows.Add(newRow)
                    daINIStaff.Update(dsINIStaff)
                    Refresh() ' reloads everything
                Catch ex As Exception
                    MsgBox("error in PutValue... " & ex.Message)
                End Try
            End If
        End Sub

    The load routine snippet:

     SQL = "SELECT * FROM INI_Table WHERE Version='" & Version & "' AND Staff='" & Staff & "' ORDER BY Section,[Key]"
                daINIStaff.SelectCommand = New SqlCommand(SQL, con)
                cbINI = New SqlCommandBuilder(daINIStaff) ' used Row update and delete
                daINIStaff.Fill(dsINIStaff)
                daINIStaff.FillSchema(dsINIStaff, System.Data.SchemaType.Mapped)
                dtINIStaff = dsINIStaff.Tables(0)

     

     

    Monday, August 16, 2010 8:02 PM

Answers

  • After researching around the net I've conculded that the answer is that ADO.Net Datasets do not support SQL Server Identity columns. You have to code around the lack of support if you want to use a Dataset for updates on tables with identity columns.

    My work around abandons the Dataset and does an update command, then reloads the Dataset. Won't scale all that well but it works. The code assume there will be only one matching row.

            Dim row() As DataRow = dtINIStaff.Select("Staff='" & Staff & "' AND Section='" & SectionForPut & "' AND Key='" & KeyForPut & "'")
            If row.Length = 0 Then
                Try ' add row
                    'Insert SQL sans ID/indentity column
                    Dim SQL As String = "INSERT INTO INI_Table (Last_Update_Date,Version,Staff,Section,[Key],Value)" _
                                        & " VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')"
                    SQL = String.Format(SQL, Now(), Version, Staff, SectionForPut, KeyForPut, ValueForPut)
                    Using con As New SqlConnection(g.OISConnectString)
                        Try
                            Dim cmd As New Data.SqlClient.SqlCommand
                            con.Open()
                            cmd.CommandText = SQL
                            cmd.Connection = con
                            cmd.ExecuteNonQuery()
                            'cmd.CommandText = "SELECT Scope_Identity()" ' retrive just created ID this connection
                            '_ID = cmd.ExecuteScalar.ToString
                            con.Close()
                            Refresh() ' reloads DS
                        Catch ex As Exception
                            MsgBox("error in PutValue... Section='" & SectionForPut & "' AND Key='" & KeyForPut & "'" & vbCrLf & ex.Message)
                        End Try
                    End Using
                Catch ex As Exception
                    MsgBox("error in PutValue, New Row... Section='" & SectionForPut & "' AND Key='" & KeyForPut & "'" & vbCrLf & ex.Message)
                End Try
            Else ' update/delete row
                If ValueForPut.Length > 0 Then
                    row(0)("Value") = ValueForPut
                    row(0)("Last_Update_Date") = Now()
                Else
                    row(0).Delete()
                End If
                Try
                    daINIStaff.Update(dsINIStaff)
                Catch ex As Exception
                    MsgBox("error in PutValue,Update/Delete... Section='" & SectionForPut & "' AND Key='" & KeyForPut & "'" & vbCrLf & ex.Message)
                End Try
            End If

     

     

     

    • Marked as answer by Bob Heitzman Tuesday, August 17, 2010 5:19 PM
    Tuesday, August 17, 2010 5:19 PM