0

Access 2003

VS 2010 C#

As subject title says I am having problems with. This is related to my previous question I asked, Here. I hope the mod's will be OK with this thread but I am not sure.

Martin Parkin advised not to close the connection between Insert and Select when using @@Identity with C# and MS-Access. I thought I got it working until I discovered that was not the case. To be honest I don't know how to solve this issue. So if anyone can help me I would appreciate it.

This is my btnLogin method..

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

        cmd.CommandText = "SELECT @ID = @@IDENTITY";
     // cmd.Parameters.AddWithValue("@ID", OleDbType.WChar); << tried this, unsuccessful
        int id = (int)cmd.ExecuteScalar(); // getting the same error? 
        myCon.Close();

This is my btnLogOut method...

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

         int id = 0;
        cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
        cmd.Parameters.AddWithValue("@ID", 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();

Or

In the btnLogin method if I do

cmd.CommandText = "SELECT @ID = @@IDENTITY";

and hide the cmd.ExecuteNonQuery(); after it. Then date and time will get logged in the database but the date and time will not get saved in the database, for logging out.

I am not sure if the problem is with btnLogin method or btnLogOut method, or both.

Working Solution

Originally I did

 cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate,   
 [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
 cmd.Parameters.AddWithValue("@ID", id);
 cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
 cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));

Then I did this 

 cmd.CommandText = " UPDATE [LoginLogTable] SET [UserName] = @UserName, [LoggedOutDate] = 
 @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE ID = @ID";
 cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
 cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
 cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));
 cmd.Parameters.AddWithValue("@ID", id);

Thanks to D Stanley and Gord Thompson.

Community
  • 1
  • 1
bucketblast
  • 437
  • 4
  • 14
  • 37

1 Answers1

3

The @ID variable does not persist in the database the way you seem to think it does. It will go out of scope when the connection is closed (possibly sooner). I would advise that you store the new identity within your application instead:

Assuming these are button handlers that are methods on the form, you could store the ID as a property of the form:

    // somewhere in the form definition:
    private int ID {get; set;}
    ...

    cmd.CommandText = "SELECT @@IDENTITY";
    int id = (int)cmd.ExecuteScalar();
    this.ID = id;

Then use the ID in your Logout method:

    // get the id from the form
    int id = this.ID;

    cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
    cmd.Parameters.AddWithValue("@ID", id);
    cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
    cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • @D Stantley - shouldn't it be int id = (int)cmd.ExecuteScalar(); and in the logout method how do I call id because it is giving me error of "The name 'id' does not exist in the current context"? – bucketblast Mar 26 '13 at 13:39
  • @bucketblast (fixed the casting error) you need to decide _how_ to save the ID. Since these seem to be in button handlers the easiest way is to store it as a property of the form. As your application (and your programming skill) matures, you will discover better ways, but having it as a property of the form will work. – D Stanley Mar 26 '13 at 13:43
  • OK - I did int id = 0; in that that method but I am still getting the same error. I will update at the top. – bucketblast Mar 26 '13 at 13:47
  • @bucketblast You need to change the second command as well (see my answer) – D Stanley Mar 26 '13 at 13:57
  • @D Stanley - date and time does not record for btnLogOut method. From the solution you are advising. – bucketblast Mar 26 '13 at 14:04
  • Are you still setting `id` to 0? What value are you getting back for `@@IDENTITY`? – D Stanley Mar 26 '13 at 14:10
  • Well in the database all I see is blank for both date and time for btnLogOut method. For btnLogIn that is fine. – bucketblast Mar 26 '13 at 14:13
  • @bucketblast learn to run the app in debug mode so you can see the variable values as the form executes. It will be very difficult to diagnose just by looking at the end results. – D Stanley Mar 26 '13 at 14:16
  • If you want me to debug tell me where to break etc. – bucketblast Mar 26 '13 at 14:17
  • Thanks D Stanley for your time, most appreciate it. – bucketblast Mar 26 '13 at 14:37
  • @D Stantley - your piece of code works. I have used data bindings for my components and originally I was putting at the beginning. I remember what Gord said about these commands with MS Access and I used that knowledge. I have update 2 to show what I mean. – bucketblast Apr 21 '13 at 17:47