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

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

Show quoteHide quote

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")
---

I hate to believe that this is impossible in ADO.NET.

Dave

Show quoteHide quote

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

Show quoteHide quote

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.)

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.)

ODBC.NET
Any way to force an update after DBConcurrencyException thrown?
Using a message queue as a buffer
logging driver for ADO.Net?
Updating sql05 with Stored Procedure