DataGrid Update

  • Thread starter Thread starter Reney
  • Start date Start date
R

Reney

I am using an access db with a table named "TimeLog". The table has the
following columns:

index {PK} - autoNumber
employeeID {PK} - string
dayOfWeek {PK} - date/time with short date (i.e. 6/19/1994)
clockStart - date/time with short time (i.e. 17:34)
clockEnd - date/time with short time (i.e. 17:34)

To fill the dataset, I am using a format function in the sql statement for
clockStart and clockEnd columns. For the other columns, I am not using any
expression, but filling the dataset directly with the values in the columns.
And I am connecting the dataset to a datagrid. The users can see the
values(except the index column, it's hided), and the user can modify or
delete, or add new records. Everything works fine. The user can change the
clockStart and clockEnd values, and update it perfectly. They can delete, or
add new records and still update it. The problem is, if there is a record
with a value in clockStart, and a null in clockEnd, the user cannot enter a
new value for the null. Same is true for the opposite.(a value in clockEnd,
and a null in clockStart). The error given for this procedure is :

"Concurrency Violation: the updatecommand affected 0 records."

Here are some codes from my program:

Private Sub btnModificationScreenThis_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnModificationScreenThis.Click

Dim weekOneStartDay As Date
GetWeekOneStartDay(weekOneStartDay)

Dim userName As String
GetUserName(userName)

If userName <> "" Then
Dim sql As String
sql = "SELECT index, dayOfWeek As [Dates], employeeID As [User
Name], " _
& "FORMAT([clockStart], 'hh:mm') AS [Clock In], " _
& "FORMAT([clockEnd], 'hh:mm') AS [Clock Out] " _
& "FROM TimeLog " _
& "WHERE employeeID = " & "'" & userName & "' " _
& "AND dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY dayOfWeek"

Try
conn.Open()
daThisWeekMod.SelectCommand = New OleDbCommand(sql, conn)
ds = New DataSet("TimeLogThisMod")
daThisWeekMod.FillSchema(ds, SchemaType.Mapped, "TimeLog")
daThisWeekMod.Fill(ds, "TimeLogThisMod")
ds.Tables("TimeLogThisMod").Columns("index").ColumnMapping =
MappingType.Hidden
dgrTimeLog.DataSource = ds.Tables("TimeLogThisMod")
dgrTimeLog.ReadOnly = False
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try
Else
MessageBox.Show("Select an Employee from the list", "No Selection
Made", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

End Sub

Private Sub btnCommitChangesThis_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnCommitChangesThis.Click

Dim cmdBuilderThis As New OleDbCommandBuilder(daThisWeekMod)
daThisWeekMod.InsertCommand = cmdBuilderThis.GetInsertCommand
daThisWeekMod.DeleteCommand = cmdBuilderThis.GetDeleteCommand
daThisWeekMod.UpdateCommand = cmdBuilderThis.GetUpdateCommand

Try
conn.Open()
daThisWeekMod.Update(ds, "TimeLogThisMod")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try

End Sub

I would really appreciate any ideas to correct this problem.
Thanks in advance,
Reney
 
It may be that your select statement is too complex for the command builder.
Have you looked at the update command generated ?


Reney said:
I am using an access db with a table named "TimeLog". The table has the
following columns:

index {PK} - autoNumber
employeeID {PK} - string
dayOfWeek {PK} - date/time with short date (i.e. 6/19/1994)
clockStart - date/time with short time (i.e. 17:34)
clockEnd - date/time with short time (i.e. 17:34)

To fill the dataset, I am using a format function in the sql statement for
clockStart and clockEnd columns. For the other columns, I am not using any
expression, but filling the dataset directly with the values in the columns.
And I am connecting the dataset to a datagrid. The users can see the
values(except the index column, it's hided), and the user can modify or
delete, or add new records. Everything works fine. The user can change the
clockStart and clockEnd values, and update it perfectly. They can delete, or
add new records and still update it. The problem is, if there is a record
with a value in clockStart, and a null in clockEnd, the user cannot enter a
new value for the null. Same is true for the opposite.(a value in clockEnd,
and a null in clockStart). The error given for this procedure is :

"Concurrency Violation: the updatecommand affected 0 records."

Here are some codes from my program:

Private Sub btnModificationScreenThis_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnModificationScreenThis.Click

Dim weekOneStartDay As Date
GetWeekOneStartDay(weekOneStartDay)

Dim userName As String
GetUserName(userName)

If userName <> "" Then
Dim sql As String
sql = "SELECT index, dayOfWeek As [Dates], employeeID As [User
Name], " _
& "FORMAT([clockStart], 'hh:mm') AS [Clock In], " _
& "FORMAT([clockEnd], 'hh:mm') AS [Clock Out] " _
& "FROM TimeLog " _
& "WHERE employeeID = " & "'" & userName & "' " _
& "AND dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY dayOfWeek"

Try
conn.Open()
daThisWeekMod.SelectCommand = New OleDbCommand(sql, conn)
ds = New DataSet("TimeLogThisMod")
daThisWeekMod.FillSchema(ds, SchemaType.Mapped, "TimeLog")
daThisWeekMod.Fill(ds, "TimeLogThisMod")
ds.Tables("TimeLogThisMod").Columns("index").ColumnMapping =
MappingType.Hidden
dgrTimeLog.DataSource = ds.Tables("TimeLogThisMod")
dgrTimeLog.ReadOnly = False
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try
Else
MessageBox.Show("Select an Employee from the list", "No Selection
Made", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

End Sub

Private Sub btnCommitChangesThis_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnCommitChangesThis.Click

Dim cmdBuilderThis As New OleDbCommandBuilder(daThisWeekMod)
daThisWeekMod.InsertCommand = cmdBuilderThis.GetInsertCommand
daThisWeekMod.DeleteCommand = cmdBuilderThis.GetDeleteCommand
daThisWeekMod.UpdateCommand = cmdBuilderThis.GetUpdateCommand

Try
conn.Open()
daThisWeekMod.Update(ds, "TimeLogThisMod")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try

End Sub

I would really appreciate any ideas to correct this problem.
Thanks in advance,
Reney
Click to expand...
 
The update command generated is as follows:
UPDATE TimeLog
SET clockStart = ?
WHERE ( (index = ?)
AND (dayOfWeek = ?)
AND (employeeID = ?)
AND ((? IS NULL AND clockStart IS NULL) OR (clockStart = ?))
AND ((? IS NULL AND clockEnd IS NULL) OR (clockEnd = ?)) )

This seems corect to me. And the update only fails in one circumstance. Only
if either of "ClockStart" or "ClockEnd" has null values and I change it to a
time value. In all other updates, or additions or deletions, it is working
fine. So I was wondering if it is related to the expression I used in SQL
query or what could it be?
 
Back
Top