Unable to Retrieve Microsoft Access Autonumber Values

Question

Access 2003

VS 2010 C#

As subject title says I am having a problem with this. It's creating a new field to print date and time when it should be stamping the date and time in the current ID. I have also tried UPDATE command parameter without success.

I have a different method (btnloggedIn) which saves Usernames, Logged In Date and Logged In Time. This works as it should be. I have created another method (btnLoggedOut) which I am having problems with. The purposes is to save Logged Out Date and Logged Out Time when user who logged out, in the came column in Access where Auto ID is created when logged in.

Table Name - LoginTable

>

FieldName   Data Type
UserName    Text
Password    Text

Table name - LoginLogTable

 FieldName     Data Type

 ID            AutoNumber
 UserName      Text
 LoggedInDate  Date/Time
 LoggedInTime  Date/Time
 LoggedOutDate Date/Time
 LoggedOutTime Date/Time

ID is PK. Its one to many relationship. User who logs in can have many details about the date and time details

If anyone can help me here I would be grateful.

 private void btnLogOut_Click(object sender, EventArgs e)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;

       cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = ?, [LoggedOutTime] = ?  
       WHERE ID = ?";

        cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
        cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
            Close();
   }

This the partial code for btnLogin method...

 OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "INSERT INTO LoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime)";

        cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
        cmd.Parameters.AddWithValue("@LoggedInDate", DateTime.Now.ToShortDateString());
        cmd.Parameters.AddWithValue("@LoggedInTime", DateTime.Now.ToString("HH:mm"));
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        myCon.Close();

You have to construct *and execute* your INSERT first, then run a `SELECT @@IDENTITY` query to retrieve the ID value that the INSERT just created. They are separate operations. Your code, as it stands now, is (apparently) trying to combine the two operations and that won't work. Also, bear in mind that `SELECT @@IDENTITY` will return a single (scalar) value, and not a rowset, so that may affect the method you call when you invoke it. — Gord Thompson, Mar 25 '13 at 22:32

score 2 · Accepted Answer · answered Mar 25 '13 at 19:58

2

If you execute a SELECT @@IDENTITY query when the user clicks the "Log out" button you'll not likely get the value you're hoping for. SELECT @@IDENTITY is intended to be called immediately after the INSERT that creates the record (in this case, when the user logs in). You can then stash that value away in your application and use it to select that same record when the user logs out.

If your application inserts any other records (in other tables) that cause a new Identity (a.k.a. "AutoNumber") value to be created then SELECT @@IDENTITY will return the most recent one of those values. So, just grab the @@IDENTITY value when the user logs in and save it for when the user logs out again.

answered Mar 25 '13 at 19:58

Gord Thompson

116,920
32
215
418

I think I am reading it as if it is quite simple, wink. Could you confirm your last sentence, about the grapping the @@IDENTITY, I think I know what you mean is in the btnLogOut method, but I will Google anyway. I will need time to work on this. So thanks in advance. – bucketblast Mar 25 '13 at 21:04
My point was that your `SELECT @@IDENTITY` call needs to be made in your 'LoggedIn' code, immediately after the `INSERT` that creates the record for that login. You can save the value returned by `SELECT @@IDENTITY` in a variable within your own application, and then use it in the `WHERE` clause of your `UPDATE` statement in your 'LogOut' code. – Gord Thompson Mar 25 '13 at 21:14
I searching how to save returned value from @@IDENTITY in a variable. – bucketblast Mar 25 '13 at 21:41
Thanks gord. Because I am receiving a error message I think I can close this thread and open a new. Hopefully. – bucketblast Mar 25 '13 at 22:45

score 0 · Answer 2 · answered Mar 25 '13 at 18:55

0

Typically, the way this is done is:

Create the new log in record.
Get its auto-generated record ID by running a new select asking for the newest log in entry for that particular user. You can sort descending to guarantee it is the first record in the recordset.
Use that record ID to specify the log in record you want to update using WHERE ID = ? instead and fill in the ID value with the record ID.

This is a very typical pattern for database record creation when you do not know what the auto-generated primary record ID will be. You create your new record, you read it back in the record ID to get its auto-generated primary key ID value, and then use the record ID from then on to refer to it.

answered Mar 25 '13 at 18:55

StarPilot

2,246
1
16
18

re: step 2 - Generally speaking the `SELECT MAX(ID)...` type of approach works fine in single-user databases but is unreliable in multi-user applications. Granted, in this particular case we have the UserName to mitigate that, but it could still fail if multiple concurrent logins are allowed (e.g., more than one login as 'guest'). `SELECT @@IDENTITY`, when used correctly, is much more reliable. – Gord Thompson Mar 25 '13 at 21:08
I was answering from the pattern usage. You don't perform a `SELECT MAX(ID)`, you perform a `SELECT ID WHERE ...` and give the particulars of the fields you just used to create the entry, ordering it by descending, so if there are repeats, you get the very latest entry. In specific cases where you have an `IDENTIY` function, you should use it unless there is a particular reason not to. – StarPilot Mar 25 '13 at 21:15

Unable to Retrieve Microsoft Access Autonumber Values

2 Answers2

Linked