Syntax error (missing operator) in query expression '@ID = @@IDENTITY'

Question

I have given valuable advise from here

But as subject title says I am receiving error in the btnLogin method, related to my previous question but really the same problem, I think? I say this because I was not getting error from my pervious question. Thanks in advance if anyone can help me here.

access 2003 VS 2010 c#

This is my btnLogin method

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

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

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

This is my btnLogOut method.

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

       cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = ?, [LoggedOutTime] = ? WHERE 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();

The purpose of the code the code is record the date and time when user logs in and the date and time when the user logs out.

Update 2 I have tried this as well...

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

Then I am receiving error of...

Missing semicolon (;) at end of SQL statement.

Update 3

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

I have placed a ; and I am getting error of...

Only assignment, call, increment, decrement, and new object expressions can be used as a statement

Looking at other solutions provided my other members

Update 4

I think some of the advise here may only work for SQL Server. I am using MS Access 2003.

Update 5

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

receiving error...

Characters found after end of SQL statement.

But what are you trying to do with that second statement? You don't read back that value `(@ID)` anywhere. — Steve, Mar 25 '13 at 23:08
What version of SQL Server are you running this against? Why do you have ms-access in your tags? — Nick.Mc, Mar 25 '13 at 23:16
I am using MS ACCESS - I thought perhaps someone with SQL Server knowledge maybe able to help me, another words widening the tags to reach further a field — bucketblast, Mar 25 '13 at 23:24

score 4 · Accepted Answer · answered Mar 25 '13 at 22:48

4

It appears that you may have an error in your code. You set the Text of the cmd to the first line, then completely replace it with the second one:

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

So you are effectively only executing:

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

answered Mar 25 '13 at 22:48

Martin

16,093
1
29
48

thanks for your quick reply if you don't mind could you have a look at my update? – bucketblast Mar 25 '13 at 23:05
1

@bucketblast Then put a `;` at the end of your first statement. Hint: after the `@LoggedInTime)`. – DaveShaw Mar 25 '13 at 23:06
@bucketblast DaveShaw meant to include it in your SQL statement: `cmd.CommandText = "INSERT INTO LoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime); SELECT @ID = @@IDENTITY"` – Martin Mar 25 '13 at 23:27
@MartinParkin - Receiving error - Characters found after end of SQL statement. I will update at the top – bucketblast Mar 25 '13 at 23:30
@bucketblast If you are using MS Access you need to carry out the `INSERT` and the `SELECT @ID = @@IDENTITY` statement as two separate commands, one after the other. You cannot execute them in the same statement against MS Access. The `@@IDENTITY` will be preserved across the connection object so you won't lose it. – Martin Mar 25 '13 at 23:32
@MartinParkin - Is that not what I was doing originally. I am confused from your original answer? Could advise or show how it could be done, pls? – bucketblast Mar 25 '13 at 23:34
@bucketblast You were not doing this originally, as I said in my answer, you were overwriting the original statement with another. You were not executing both, only the second one. You must first execute the `INSERT` (by running `cmd.ExecuteNonQuery();`) then execute the `SELECT`. Do not close the connection in between or you will lose the `@@IDENTITY`. – Martin Mar 25 '13 at 23:36
@MartinParkin - It seems your solution has been solved. As I used to say when I was teen, wicked. Thanks. – bucketblast Mar 25 '13 at 23:41

score 0 · Answer 2 · edited May 23 '17 at 12:28

In first login method, you override the commandtext, by setting it twice:

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

In 'update 2', you are concatting the SELECT-statement right after the INSERT-statement, resulting in the following SQL:

INSERT INTO LoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime)SELECT @ID = @@IDENTITY

You must set an ';' at the end of the INSERT-statement, before the 'SELECT'.

And btw. use SCOPE_IDENTITY(), instead of @@IDENTITY, see http://msdn.microsoft.com/en-us/library/ms190315.aspx and SQL: How to get the id of values I just INSERTed?

Also, here is an similar question, showing the correct way to execute the command: How to get last inserted id?

It doesn't matter. I am going back to original plan. That is when user logs out, date and time will be logged to new id (autonumber). It seems MS-Access and C# is OK for basics only. — bucketblast, Mar 26 '13 at 22:39

score 0 · Answer 3 · answered Mar 25 '13 at 23:09

0

If you are thinking of fetching the user id, you have to combine both the statments in to a single batch

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

answered Mar 25 '13 at 23:09

TrustyCoder

4,749
10
66
119

TrustyCoder - I done @LoggedInTime); SELECT but getting error – bucketblast Mar 25 '13 at 23:20

Syntax error (missing operator) in query expression '@ID = @@IDENTITY'

3 Answers3

Linked