CODEWITHSUNDEEP
c#sql-serverms-access
bucketblast
bucketblast

Reputation: 447

Unable to Retrieve Microsoft Access Autonumber Values

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();

Upvotes: 0

Views: 501

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123419

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.

Upvotes: 2

StarPilot
StarPilot

Reputation: 2272

Typically, the way this is done is:

  1. Create the new log in record.
  2. 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.
  3. 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.

Upvotes: 0

Related Questions