Access Autonumber problem

[Guest]

Hello,

I am working on a C# application that uses an Access 2003 database. The
database is also the back end for a much larger (and much older) VB6 project.
The primary key to each table is an ID field that is built from two fields,
CounterID and StoreID. CounterID is an auto number field, and StoreID is a
configurable number that is incremented manually every time a compact and
repair is performed on the database.

I can not figure out how to build the ID field in ADO.NET. I have tried
using the data adapters OnRowUpdate event, but my ID field is 0 in the
database. Here is an example of what I've tried:

static void oDataAdapter_OnRowUpdate(object sender,
OleDbRowUpdatedEventArgs e)
{
OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection);

e.Row["COUNTERID"] = oCmd.ExecuteScalar();

//Temp
e.Row["STOREID"] = 1;

string temp = "0";
e.Row["ID"] = e.Row["COUNTERID"].ToString() +
e.Row["STOREID"].ToString().PadLeft(2, temp[0]);

e.Row.AcceptChanges();
}

What am I doing wrong?

Regards,
David

 

[Cor Ligthert [MVP]]

Dcali,

In fact everything, you get only back the latest ID. In SQLServer the ID is
giving back automaticly in Access not. Therefore in my idea is eithter to
change to SQLServer (Express) or to refresh everytime your dataset
completely.

Although you have first to do an update of the dataset of course and can
than try to get the id, will the ident that you only be the latest, not all
the ones that are new in the same dataset.

While the event that you are using is typical in a binding situation, where
the datarow is updated from a binded control, it is than of course still not
in the database.

Does not help much, but I hope a little bit.

Cor

 

[Guest]

I would use SQLServer Express if I could, but unfortunatly this is not the
primary application that the database uses. I am actually writing web
services to expose functionality for a VB6 application that already has a
fairly large customer base. We use Oracle for our larger customers and
Access for the smaller ones.

In traditional ADO, we would use something similar to the following:

---
rs!ID = CLng(Rnd() * -10000000)
rs.Update

rs.Resync adAffectCurrent, adResyncAllValues

rs!ID = rs!CounterID & Format(StoreInfo.StoreNum, "00")

 

[William \(Bill\) Vaughn]

If you insist on using Access, you'll find that JET engine's inability to
execute more than one operation at a time forces you to either fetch the
@@Identity value post INSERT or use another scheme. I've been writing a lot
about identity issues lately so check the archives of this list. I expect
that a better alternative would be to use a GUID as the row identifier. This
is also supported in Oracle (and SQL Server). In this case your application
simply generates a GUID in code and uses that as the PK in place of an
engine-generated identity value.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Kevin Yu [MSFT]]

Hi David,

If you would like to refresh the DataSet manually, you have to handle the
OleDbDataAdapter.RowUpdated event. It will be

void oleDbDataAdapter1_RowUpdated(object sender,
System.Data.OleDb.OleDbRowUpdatedEventArgs e)
{
//....
}

Also, in the event handler, you will need to check for statement type. In
this case, it might be only the insert statement do we need to update the
PK.

if(e.StatementType == StatementType.Insert)
{
//......
}

I tried this on my machine, it seems to be working fine. Also, if you need
to keep the DataSet and database synchronized, you can do a SELECT again to
get value from database, then refresh the row. The disadvantage is that you
need one more round-trip to the server.

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Kevin Yu [MSFT]]

Hi David,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)