VB ADO.NET Concurrency, Access, date fields and parameters

[Soop]

I am having lots of problems with an Access DB and updating datasets
with date fields. Even on a very simple test DB of just ID & date
fields, I get a "Concurrency violation" when upding the data source
after changing the data from a datagrid.

I bind a datagrid to the dataset and populate the grid by a
dataAdaptor.fill
I then edit a date in the grid.
Then I update the data source using dataAdaptor.Update
That is the point I get my Concurrency Violation.
If I remove all Date fields from the database, I have no errors when
adding, deleting or updating records.

My question is, does anyone know of a way to see the actual sql
command that is being run on each datasource row update? I mean with
the parameters replaced so I can see what values are acutally being
inserted?

Any help very much appreciated.

Thanks

 

[William Ryan eMVP]

Hi Soop:

I am having lots of problems with an Access DB and updating datasets
with date fields. Even on a very simple test DB of just ID & date
fields, I get a "Concurrency violation" when upding the data source
after changing the data from a datagrid.

I bind a datagrid to the dataset and populate the grid by a
dataAdaptor.fill
I then edit a date in the grid.
Then I update the data source using dataAdaptor.Update
That is the point I get my Concurrency Violation.
If I remove all Date fields from the database, I have no errors when
adding, deleting or updating records.

Not sure what your update command looks like but that's the likely source.
Are you using a CommandBuilder, DataAdapterConfiguration Wizard or did you
write your own logic?

My question is, does anyone know of a way to see the actual sql
command that is being run on each datasource row update? I mean with
the parameters replaced so I can see what values are acutally being
inserted?

You can add a handler for RowUpdating on the DataAdapter and you can discern
what you need from there.
http://msdn.microsoft.com/library/d...SystemDataOleDbOleDbDataAdapterClassTopic.asp
There's no profiler that I konw of for Access, but if you ever use SQL
Server you can use the SQL Profiler to see the exact text being sent back.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Soop]

Hi Soop:

Hi Bill, thank you very much for your help, it is much apreciated.

Not sure what your update command looks like but that's the likely source.
Are you using a CommandBuilder, DataAdapterConfiguration Wizard or did you
write your own logic?

In my original code I am using the DACW to generate the code and then
I modify it to suit my runtime dataset creation etc. I can post this
if it may help but...

I have set up a test project with a grid bound to my dataset. That has
all been generated at design time by the DACW and not edited. I have
an access DB with 3 fields tmp_id(autonumber), tmp_date(date/time) and
tmp_name(text). I have added 1 record to the db from within Access.
When I edit that record from the grid, the ad.update fails with the
concurrency invalid error.

Would it be helpful to post the update command? I assumed that the
DACW would get it right for me

You can add a handler for RowUpdating on the DataAdapter and you can discern
what you need from there.
http://msdn.microsoft.com/library/d...SystemDataOleDbOleDbDataAdapterClassTopic.asp
There's no profiler that I konw of for Access, but if you ever use SQL
Server you can use the SQL Profiler to see the exact text being sent back.

That's just what I needed thank you Bill. I had seen people around on
the Net with output from a profiler and couldnt find one myself, that
explains why!

I will attempt to get the actual sql string in the RowUpading event.

At this time, I would simply like to see a simple project using grids
and dates with an access db! It surely cant all be this hard

Many thanks again.

 

[Soop]

Update:
My date field in my Access DB is this format: 31/12/2004 13:23:00
It seems that if I set my DB date filed to just 31/12/2004, all the
update code works and no error is raised.

This is good news as at least I now have a working ad.update.

However, I need the "Date/Time" field in Access to keep times (I have
Start Time and End Time fields in my DB) and for those fields to be
shown in my grid as times, for the user to edit those times and for
the grid changes to then be updated in the datasource.

I have read in other posts that some people believe that the .Net
OleDBDataAdaptor has a "bug" that strips the times from the values it
updates. They have said that they get aound this by using
integer/double fields in their place to represent times.

What I dont understand is how I show those values in my grid as times.
When I populate my grid it shows those fields as simple number fields,
not as a time field.

Does anyone have any suggestions or insights?

Many thanks

 

[Soop]

I finally sorted out my problems.

The problem was that the OleDBDataType chosen by the DACW for my
date/time field was "DBDate". This was unfortunately truncating the
time and therefore not matching the original data value, thus a
concurrency violation.

I changed the type to a "Date" type and all is well.

Hope this helps others that may come across this anoying problem.

Regards