Update Concurrency Error

  • Thread starter Thread starter Plateriot
  • Start date Start date
P

Plateriot

I have a seemingly straightforward Update Query that works fine in query
analyzer.
However, when I put it into code either by using an TableAdapter Object data
source, or Manually, I get a concurrency error and I can't figure out why...

here's the basic sql for the update (followed by my manual attempt to
program it)
UPDATE PPU.tbl_Users_Main
SET ProvNUID = @ProvNUID,
ProvID = @ProvID,
ProvHC_ID =@ProvHC_ID,
ProvName = @ProvName,
[Alias] = @Alias, Role = @Role,
Type = @Type
WHERE (UserID = @Original_UserID)

Yes, I realize the shortcoming of using the words 'Alias' and 'Type', but
the designer unfortunately, named it this and most of the program has code
that already works with it, so although I hate to keep using the names, I may
have to.

Here is the attempt to program it into an aspx page using a lnk button:

Protected Sub lnkEditUpdateIns_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles lnkEditUpdateIns.Click
Select Case Me.lnkEditUpdateIns.Text
Case "Edit"
Me.lnkEditUpdateIns.Text = "Update"
Me.lnkNewCancel.Text = "Cancel"
EnableUserBoxes(True)
SaveRecordForCancel()
Case "Update"
Dim NV As New PCPUser
Dim SR As New PCPUser
NV = GetNewValues()
SR = Session("ssCur")

'update the user and return any error message
Select Case UpdateUser(NV, SR)
Case DBResult.Success
Me.lnkEditUpdateIns.Text = "Edit"
EnableUserBoxes(False)
Case DBResult.ConcurrencyError
Me.lblInform.Text = "Concurrency error"
Case DBResult.DatabaseError
Me.lblInform.Text = "An Error occured in the database"
End Select



Case "Insert"
Me.lnkEditUpdateIns.Text = "Update"


End Select
End Sub

Private Function GetNewValues() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssNew") = SR
Return SR
End Function

Public Shared Function UpdateUser(ByVal NewUser As PCPUser, ByVal
OldUser As PCPUser) As DBResult
Dim cn As SqlConnection = GetVitalsConnection()

Dim sSqlCommand As String = "UPDATE PPU.tbl_Users_Main " _
& "SET ProvNUID = @ProvNUID, ProvID = @ProvID, ProvHC_ID =
@ProvHC_ID, ProvName = @ProvName, [Alias] = @Alias, Role = @Role, Type =
@Type " _
& "WHERE (UserID = @Original_UserID)"


cn.Open()
Dim cmdUsers As New SqlCommand(sSqlCommand, cn)



With cmdUsers.Parameters
.AddWithValue("@ProvNUID", NewUser.ProvNUID)
.AddWithValue("@ProvID", NewUser.ProvID)
.AddWithValue("@ProvHC_ID", NewUser.ProvHC_ID)
.AddWithValue("@ProvName", NewUser.ProvName)
.AddWithValue("@Alias", NewUser.ProvAlias)
.AddWithValue("@Role", NewUser.Role)
.AddWithValue("@Type", NewUser.ProvType)
.AddWithValue("@Original_UserID", OldUser.UserID)
End With


Try
If cmdUsers.ExecuteNonQuery() > 0 Then
UpdateUser = DBResult.Success
Else
UpdateUser = DBResult.ConcurrencyError
End If
Catch ex As Exception
UpdateUser = DBResult.DatabaseError
End Try


cn.Close()
cn.Dispose()
cn = Nothing
End Function

Private Function SaveRecordForCancel() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssCUR") = SR
Return SR
End Function

Any suggestions?
 
First, I would also [bracket] [type].
Next, I would turn on the profiler to see what's getting executed by
ADO.NET. Take that generated TSQL and execute it in SSMS and see if it fails
in the same way. There really isn't any magic going on...
hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Plateriot said:
I have a seemingly straightforward Update Query that works fine in query
analyzer.
However, when I put it into code either by using an TableAdapter Object
data
source, or Manually, I get a concurrency error and I can't figure out
why...

here's the basic sql for the update (followed by my manual attempt to
program it)
UPDATE PPU.tbl_Users_Main
SET ProvNUID = @ProvNUID,
ProvID = @ProvID,
ProvHC_ID =@ProvHC_ID,
ProvName = @ProvName,
[Alias] = @Alias, Role = @Role,
Type = @Type
WHERE (UserID = @Original_UserID)

Yes, I realize the shortcoming of using the words 'Alias' and 'Type', but
the designer unfortunately, named it this and most of the program has code
that already works with it, so although I hate to keep using the names, I
may
have to.

Here is the attempt to program it into an aspx page using a lnk button:

Protected Sub lnkEditUpdateIns_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles lnkEditUpdateIns.Click
Select Case Me.lnkEditUpdateIns.Text
Case "Edit"
Me.lnkEditUpdateIns.Text = "Update"
Me.lnkNewCancel.Text = "Cancel"
EnableUserBoxes(True)
SaveRecordForCancel()
Case "Update"
Dim NV As New PCPUser
Dim SR As New PCPUser
NV = GetNewValues()
SR = Session("ssCur")

'update the user and return any error message
Select Case UpdateUser(NV, SR)
Case DBResult.Success
Me.lnkEditUpdateIns.Text = "Edit"
EnableUserBoxes(False)
Case DBResult.ConcurrencyError
Me.lblInform.Text = "Concurrency error"
Case DBResult.DatabaseError
Me.lblInform.Text = "An Error occured in the
database"
End Select



Case "Insert"
Me.lnkEditUpdateIns.Text = "Update"


End Select
End Sub

Private Function GetNewValues() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssNew") = SR
Return SR
End Function

Public Shared Function UpdateUser(ByVal NewUser As PCPUser, ByVal
OldUser As PCPUser) As DBResult
Dim cn As SqlConnection = GetVitalsConnection()

Dim sSqlCommand As String = "UPDATE PPU.tbl_Users_Main " _
& "SET ProvNUID = @ProvNUID, ProvID = @ProvID, ProvHC_ID =
@ProvHC_ID, ProvName = @ProvName, [Alias] = @Alias, Role = @Role, Type =
@Type " _
& "WHERE (UserID = @Original_UserID)"


cn.Open()
Dim cmdUsers As New SqlCommand(sSqlCommand, cn)



With cmdUsers.Parameters
.AddWithValue("@ProvNUID", NewUser.ProvNUID)
.AddWithValue("@ProvID", NewUser.ProvID)
.AddWithValue("@ProvHC_ID", NewUser.ProvHC_ID)
.AddWithValue("@ProvName", NewUser.ProvName)
.AddWithValue("@Alias", NewUser.ProvAlias)
.AddWithValue("@Role", NewUser.Role)
.AddWithValue("@Type", NewUser.ProvType)
.AddWithValue("@Original_UserID", OldUser.UserID)
End With


Try
If cmdUsers.ExecuteNonQuery() > 0 Then
UpdateUser = DBResult.Success
Else
UpdateUser = DBResult.ConcurrencyError
End If
Catch ex As Exception
UpdateUser = DBResult.DatabaseError
End Try


cn.Close()
cn.Dispose()
cn = Nothing
End Function

Private Function SaveRecordForCancel() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssCUR") = SR
Return SR
End Function

Any suggestions?
Click to expand...
 
I broke down and renamed the fields to ProvAlias and ProvType
but I still get the error for my manual code --

However, now that I changed the names, the Object Data Source from the table
adapter works.
But I'm kinda apprehensive about using dropdowns list in a DetailsView

Is SSMS in SQL 2000?

William Vaughn said:
First, I would also [bracket] [type].
Next, I would turn on the profiler to see what's getting executed by
ADO.NET. Take that generated TSQL and execute it in SSMS and see if it fails
in the same way. There really isn't any magic going on...
hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Plateriot said:
I have a seemingly straightforward Update Query that works fine in query
analyzer.
However, when I put it into code either by using an TableAdapter Object
data
source, or Manually, I get a concurrency error and I can't figure out
why...

here's the basic sql for the update (followed by my manual attempt to
program it)
UPDATE PPU.tbl_Users_Main
SET ProvNUID = @ProvNUID,
ProvID = @ProvID,
ProvHC_ID =@ProvHC_ID,
ProvName = @ProvName,
[Alias] = @Alias, Role = @Role,
Type = @Type
WHERE (UserID = @Original_UserID)

Yes, I realize the shortcoming of using the words 'Alias' and 'Type', but
the designer unfortunately, named it this and most of the program has code
that already works with it, so although I hate to keep using the names, I
may
have to.

Here is the attempt to program it into an aspx page using a lnk button:

Protected Sub lnkEditUpdateIns_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles lnkEditUpdateIns.Click
Select Case Me.lnkEditUpdateIns.Text
Case "Edit"
Me.lnkEditUpdateIns.Text = "Update"
Me.lnkNewCancel.Text = "Cancel"
EnableUserBoxes(True)
SaveRecordForCancel()
Case "Update"
Dim NV As New PCPUser
Dim SR As New PCPUser
NV = GetNewValues()
SR = Session("ssCur")

'update the user and return any error message
Select Case UpdateUser(NV, SR)
Case DBResult.Success
Me.lnkEditUpdateIns.Text = "Edit"
EnableUserBoxes(False)
Case DBResult.ConcurrencyError
Me.lblInform.Text = "Concurrency error"
Case DBResult.DatabaseError
Me.lblInform.Text = "An Error occured in the
database"
End Select



Case "Insert"
Me.lnkEditUpdateIns.Text = "Update"


End Select
End Sub

Private Function GetNewValues() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssNew") = SR
Return SR
End Function

Public Shared Function UpdateUser(ByVal NewUser As PCPUser, ByVal
OldUser As PCPUser) As DBResult
Dim cn As SqlConnection = GetVitalsConnection()

Dim sSqlCommand As String = "UPDATE PPU.tbl_Users_Main " _
& "SET ProvNUID = @ProvNUID, ProvID = @ProvID, ProvHC_ID =
@ProvHC_ID, ProvName = @ProvName, [Alias] = @Alias, Role = @Role, Type =
@Type " _
& "WHERE (UserID = @Original_UserID)"


cn.Open()
Dim cmdUsers As New SqlCommand(sSqlCommand, cn)



With cmdUsers.Parameters
.AddWithValue("@ProvNUID", NewUser.ProvNUID)
.AddWithValue("@ProvID", NewUser.ProvID)
.AddWithValue("@ProvHC_ID", NewUser.ProvHC_ID)
.AddWithValue("@ProvName", NewUser.ProvName)
.AddWithValue("@Alias", NewUser.ProvAlias)
.AddWithValue("@Role", NewUser.Role)
.AddWithValue("@Type", NewUser.ProvType)
.AddWithValue("@Original_UserID", OldUser.UserID)
End With


Try
If cmdUsers.ExecuteNonQuery() > 0 Then
UpdateUser = DBResult.Success
Else
UpdateUser = DBResult.ConcurrencyError
End If
Catch ex As Exception
UpdateUser = DBResult.DatabaseError
End Try


cn.Close()
cn.Dispose()
cn = Nothing
End Function

Private Function SaveRecordForCancel() As PCPUser
Dim SR As New PCPUser
SR.ProvNUID = Me.txtNUID.Text
SR.ProvID = Me.txtProvID.Text
SR.ProvHC_ID = Me.txtHC_ID.Text
SR.ProvName = Me.txtProvName.Text
SR.ProvAlias = Me.txtAlias.Text
SR.Role = Me.cmbRole.SelectedValue
SR.ProvType = Me.cmbType.SelectedValue
Session("ssCUR") = SR
Return SR
End Function

Any suggestions?
Click to expand...
Click to expand...
 
Back
Top