Date/Time Update Problem

[MarkItZero]

Hello,

 

I have an Access database with a Date/Time field that I use to store time values (8:00am, 9:00pm etc).

 

I have no problem displaying the value on my form, however when I attempt to update any of the fields from the the table, I receive an error....

An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

Additional information: Concurrency violation: the UpdateCommand affected 0 records.

 

I am almost positive that the error is being caused by this date/time field.

 

If I remove the field from the table and refresh my datasets in the program, I can display and update without any problems. If I keep the date/time field in the table and populate it with a full date like 03/11/04, I can also display and update without any problems. However, as soon as I enter a time into the field, it begins to give me that error when I try to update the dataset (even though I am not even trying to update that field).

 

Here is the code I use for displaying and updating..

'Display Data
Sub DisplayData ()
       
       'Clear DataSets
       DstsTicket1.Clear()

       'Fill DataSet
       OleDbDataAdapter1.Fill(DstsTicket1, "Ticket")

       'DataRow
       Dim DRTSTicket As DataRow = DstsTicket1.Tables("Ticket").Rows.Find(ID)
       'Fill textbox
       txtName.Text = DRTSTicket("Name")
       
End Sub

'Update Data
Sub UpdateData ()

       'Clear DataSets
       DstsTicket1.Clear()

       'Fill DataSet
       OleDbDataAdapter1.Fill(DstsTicket1, "Ticket")

       'DataRow
       Dim DRTSTicket As DataRow = DstsTicket1.Tables("Ticket").Rows.Find(ID)

       'Set New Values
       DRTSTicket("Name") = txtName.Text
       
       'Update DataSet
       OleDbDataAdapter1.Update(DstsTicket1, "Ticket")
End Sub

 

What could I be doing wrong? And why does this problem only occur when I place time values (8:00am) in the table?

 

Sorry this was such a long question!

 

Please Help!

 

Thanks!

[HJB417]

The line after "'Set New Values" will throw an exception if no rows are returned, and it searches by primary key. Also, ID is an object, but of what type?

 

Otherwise, the code looks good to me, though I dont see where the date is getting inserted, unless txtName.Text is the date.

[Joe Mamma]

I am assuming that the ticket is for a spoecific date at a specific time. . .

Why dont you want the date part stored as well???

 

if you must have just a time, give the datetime a dummy date:

DateTime.Parse("1/1/1900 "+ TimeString),

insert and update using that.

 

On display just format the data to display only the time.

[MarkItZero]

To explain further...

 

I have an Access table "Tickets" with to 3 fields (ID, Name, Time).

 

Tickets

ID - Autonumber

Name - Text

Time - Date/Time

 

Sample Data

20 - Johnson - 1/1/04 11:00am

21 - Brown - 1/1/04 3:00pm

 

The date portion of the Time field is insignificant to me, however, since there is no time-only data type, having the date in the field wont hurt anything.

 

On my form, I display the Name in a text box based upon an ID passed from another form.

 

I have a Save button that allows the user to save any changes they make to the name field.

 

I am not attempting to read, display, update or do anything with the Time field on this form.

 

If the Time value of the record I am updating is set to 12:00am (no matter what the date), then the code works fine. But, if the Time value is anything else (12:01am, 11:00pm..) there will be an error whenever the user clicks the Save button.

 

exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

Additional information: Concurrency violation: the UpdateCommand affected 0 records.

 

I am totally baffled. Any suggestions?

 

Thanks.

[wessamzeidan]

Can you post your update query? Also, you don't need to use a date/time field, save it as normal text...just a suggestion

[MarkItZero]

Here is the system generated code for the Update Query...

 

'
       'OleDbUpdateCommand1
       '
       Me.OleDbUpdateCommand1.CommandText = "UPDATE Tickets SET Name = ?, [Time] = ? WHERE (ID = ?) AND (Name = ? OR ? IS NULL" & _
       " AND Name IS NULL) AND ([Time] = ? OR ? IS NULL AND [Time] IS NULL)"
       Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Name", System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Time", System.Data.OleDb.OleDbType.DBDate, 0, "Time"))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_ID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Name", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Name", System.Data.DataRowVersion.Original, Nothing))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Name1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Name", System.Data.DataRowVersion.Original, Nothing))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Time", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Time", System.Data.DataRowVersion.Original, Nothing))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Time1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Time", System.Data.DataRowVersion.Original, Nothing))

 

Here is my update data procedure...

   'Update Data
   Sub UpdateData()

       'Clear DataSets
       DsTickets1.Clear()

       'Fill DataSet
       OleDbDataAdapter1.Fill(DsTickets1, "Tickets")

       'DataRow
       Dim DRTicket As DataRow = DsTickets1.Tables("Tickets").Rows.Find(ID)

       'Set New Values
       DRTicket("Name") = txtName.Text

       'Update DataSet
       OleDbDataAdapter1.Update(DsTickets1, "Tickets")

   End Sub

 

I would use a text field instead of a time field, but later on in the program I want to be able to the calculate time differences between tickets. I suppose I could force the user to enter the time in a specified format which would allow me to convert it to an integer for the calculations. I may have to do that if I cant figure this problem out.