HELP with dataset update

B

Brad Shook

I am trying to update a dataset on a web page. I keep getting concurrency
validation error. Here is the code. I have shortened it for posting
purposes.

Private Sub DGDrVisits_UpdateCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DGDrVisits.UpdateCommand

'I have to requery the DB for the data inorder to detect any changes

'If you don't save the current values they will be reset to the original
values

Try

Dim row As DataRow 'for changing the datatable datarow

Dim pAppropedDate As DateTime =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("ApprovedDate"),
TextBox).Text

Dim pClerk As String =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("Clerk"),
TextBox).Text

Dim pDoctorName As String =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("DoctorName"),
TextBox).Text

Dim pVisitDate As DateTime =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("VisitDate"),
TextBox).Text

Dim pPrimaryDr As Boolean =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("Checkbox2"),
CheckBox).Checked

Dim pSpecialistDr As Boolean =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("Checkbox4"),
CheckBox).Checked

Dim pApprovedAmount As Decimal =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("ApprovedAmount"),
TextBox).Text.Replace("$", "")

Dim pPaidDate As DateTime

Dim pDateExists As Boolean = False

Dim cp As New ComponentDrVisits

'inorder to pass a null value to a datatime field in SQL You have to pass
system.dbnull value

'I set the flag pDateExists to true so I know I have a date to save

If CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("PaidDate"),
TextBox).Text.Length > 0 Then

pPaidDate =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("PaidDate"),
TextBox).Text

pDateExists = True

End If

Dim PaidBy As String =
CType(Me.DGDrVisits.Items(e.Item.ItemIndex).FindControl("PaidBy"),
TextBox).Text

'SelectDrVisits() 'Populates Dr Visits Relies on Data from DGPeople

Me.DsDrVisits1.SelectDrVisits.Clear()

Me.DsDrVisits1 = cp.SelectDrVisits(Me.DsDrVisits1,
DGPeople.DataKeys.Item(0))

'Get the row that was updated by the user

row = Me.DsDrVisits1.SelectDrVisits.Select("UniqueId=" &
Me.DGDrVisits.DataKeys.Item(e.Item.ItemIndex))(0)

'set all the values

row.Item("ApprovedDate") = pAppropedDate

row.Item("Clerk") = pClerk

row.Item("DoctorName") = pDoctorName

row.Item("VisitDate") = pVisitDate

row.Item("PrimaryDr") = pPrimaryDr

row.Item("SpecialistDr") = pSpecialistDr

row.Item("ApprovedAmount") = pApprovedAmount

If pDateExists Then 'they supplied a valid date

row.Item("PaidDate") = pPaidDate

Else 'they left this field blank so pass dbnull

row.Item("PaidDate") = System.DBNull.Value

End If

'str = CType(e.Item.Cells(7).Controls(0), TextBox).Text

row.Item("PaidBy") = PaidBy

'update the dataset

cp.UpdateDataSet(Me.DsDrVisits1)

'turn edit mode off

Me.DGDrVisits.EditItemIndex = -1

'rebind the datagrid

Me.DGDrVisits.DataBind()

Catch ex As Exception

Alert(ex.Message)

End Try

End Sub



'The data component code

Public Sub UpdateDataSet(ByRef WrkingDS As DSDrVisits)

SetConnection()

'Create a new dataset to hold the changes that have been made to the main
dataset.

Dim objDataSetChanges As DSDrVisits = New DSDrVisits

'Get the changes that have been made to the main dataset.

objDataSetChanges = CType(WrkingDS.GetChanges, DSDrVisits)

'Check to see if any changes have been made.

If (Not (objDataSetChanges) Is Nothing) Then

Try

'There are changes that need to be made, so attempt to update the datasource
by

'calling the update method and passing the dataset and any parameters.

Me.UpdateDataSource(objDataSetChanges)

WrkingDS.Merge(objDataSetChanges)

WrkingDS.AcceptChanges()

Catch eUpdate As System.Exception

'Add your error handling code here.

Throw eUpdate

End Try

'Add your code to check the returned dataset for any errors that may have
been

'pushed into the row object's error.

End If

End Sub

Private Sub UpdateDataSource(ByRef ChangedRows As DSDrVisits)

Try

SetConnection()

'The data source only needs to be updated if there are changes pending.

If (Not (ChangedRows) Is Nothing) Then

'Open the connection.

Me.SqlConnection1.Open()

'Attempt to update the data source.

Me.DataAdapterDrVisits.Update(ChangedRows)

End If

Catch updateException As System.Exception

'Add your error handling code here.

Throw updateException

Finally

'Close the connection whether or not the exception was thrown.

Me.SqlConnection1.Close()

End Try

End Sub



'

'SqlUpdateCommand1

'

Me.SqlUpdateCommand1.CommandText = "[UpdateDrVisit]"

Me.SqlUpdateCommand1.CommandType = System.Data.CommandType.StoredProcedure

Me.SqlUpdateCommand1.Connection = Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@MemberID",
System.Data.SqlDbType.BigInt, 8, "MemberID"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@VisitDate",
System.Data.SqlDbType.DateTime, 8, "VisitDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DoctorName",
System.Data.SqlDbType.VarChar, 50, "DoctorName"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PaidDate",
System.Data.SqlDbType.DateTime, 8, "PaidDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ApprovedAmount",
System.Data.SqlDbType.Money, 4, "ApprovedAmount"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Clerk", System.Data.SqlDbType.VarChar,
2, "Clerk"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PrimaryDr", System.Data.SqlDbType.Bit,
1, "PrimaryDr"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@SpecialistDr",
System.Data.SqlDbType.Bit, 1, "SpecialistDr"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ApprovedDate",
System.Data.SqlDbType.DateTime, 8, "ApprovedDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@PaidBy", System.Data.SqlDbType.VarChar,
2, "PaidBy"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@UniqueID",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(9, Byte), CType(0, Byte), "UniqueID",
System.Data.DataRowVersion.Current, Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_MemberID",
System.Data.SqlDbType.BigInt, 8, "MemberID"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_VisitDate",
System.Data.SqlDbType.DateTime, 8, "VisitDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_DoctorName",
System.Data.SqlDbType.VarChar, 50, "DoctorName"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_PaidDate",
System.Data.SqlDbType.DateTime, 8, "PaidDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ApprovedAmount",
System.Data.SqlDbType.Money, 4, "ApprovedAmount"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Clerk",
System.Data.SqlDbType.VarChar, 2, "Clerk"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_PrimaryDr",
System.Data.SqlDbType.Bit, 1, "PrimaryDr"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_SpecialistDr",
System.Data.SqlDbType.Bit, 1, "SpecialistDr"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ApprovedDate",
System.Data.SqlDbType.DateTime, 8, "ApprovedDate"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_PaidBy",
System.Data.SqlDbType.VarChar, 2, "PaidBy"))

Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_UniqueID",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, CType(9, Byte), CType(0, Byte), "UniqueID",
System.Data.DataRowVersion.Current, Nothing))

End Class


'The Stored Procedure that I am calling

CREATE PROCEDURE UpdateDrVisit
@MemberID bigint,
@VisitDate datetime,
@DoctorName varchar(50),
@PaidDate datetime,
@ApprovedAmount money,
@Clerk varchar(2),
@PrimaryDr bit,
@SpecialistDr bit,
@ApprovedDate datetime,
@PaidBy varchar(2),
@UniqueID numeric(9),
@Original_MemberID bigint,
@Original_VisitDate datetime,
@Original_DoctorName varchar(50),
@Original_PaidDate datetime,
@Original_ApprovedAmount money,
@Original_Clerk varchar(2),
@Original_PrimaryDr bit,
@Original_SpecialistDr bit,
@Original_ApprovedDate datetime,
@Original_PaidBy varchar(2),
@Original_UniqueID numeric(9)
AS

UPDATE tblDoctorVisits
SET MemberID = @MemberID, VisitDate = @VisitDate, DoctorName = @DoctorName,
PaidDate = @PaidDate, ApprovedAmount = @ApprovedAmount, Clerk = @Clerk,
PrimaryDr = @PrimaryDr, SpecialistDr = @SpecialistDr, ApprovedDate =
@ApprovedDate,
PaidBy = @PaidBy
WHERE (UniqueID = @Original_UniqueID) AND
(ApprovedAmount = @Original_ApprovedAmount OR @Original_ApprovedAmount IS
NULL AND ApprovedAmount IS NULL)
AND (ApprovedDate = @Original_ApprovedDate) AND (Clerk = @Original_Clerk)
AND
(DoctorName = @Original_DoctorName) AND (MemberID = @Original_MemberID) AND
(PaidBy = @Original_PaidBy OR @Original_PaidBy IS NULL AND PaidBy IS NULL)
AND
(PaidDate = @Original_PaidDate OR @Original_PaidDate IS NULL AND PaidDate IS
NULL) AND
(PrimaryDr = @Original_PrimaryDr OR @Original_PrimaryDr IS NULL AND
PrimaryDr IS NULL) AND
(SpecialistDr = @Original_SpecialistDr OR @Original_SpecialistDr IS NULL AND
SpecialistDr IS NULL) AND
(VisitDate = @Original_VisitDate);
SELECT UniqueID, MemberID, VisitDate, DoctorName, PaidDate, ApprovedAmount,
Clerk, PrimaryDr,
SpecialistDr, ApprovedDate, PaidBy FROM tblDoctorVisits
WHERE (UniqueID = @UniqueID)
GO

Thanks,
Brad Shook
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top