Must declare the scalar variable "@ID" for insert parameter

Question

I am receiving the error, Must declare the scalar variable "@ID". Pointing at ExecuteScalar line. I looked on goggle and I think it has something to do with insert parameters for ID. Then again I read there could be a typo error. In my db I have declare column name as ID and Data Type as int, setting 'Is Identity' as Yes. As I am not going to insert ID column manually I think this is why I am having problem(s) and I don't know how to solve this problem.

What I am trying to do is insert username, login date and time. Update on the same column (same id column) when user logs out. Create a new column when user log in again and So on. I am using the similar code that I asked here and here when D Stanley helped me.

Thanks in advance if anyone can help me.

 private int ID // forgot to add this. 
    { get; set; }

private void btnLogin_Click(object sender, RoutedEventArgs e)
    {
        string value = cbRoles.Text;

        switch (value)
        { 
            case "Manager":


                myCon.connectionString();
                string dString = string.Empty;

                SqlConnection thisConnection = myCon.dbCon;
                SqlCommand nonqueryCommand = thisConnection.CreateCommand();

                using (var command = myCon.dbCon.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM tblPrivileges";
                    command.Parameters.AddWithValue("UserName", (txtUserName.Text));
                    command.Parameters.AddWithValue("Password", (txtPassword.Text));

                    thisConnection.Open();
                    var reader = command.ExecuteReader(); //strcomp
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                txtUserName.Text = reader["UserName"].ToString();
                                txtPassword.Text = reader["Password"].ToString();
                                MainWindow gobackB = new MainWindow();
                                gobackB.Show();
                                LoginSample goback = new LoginSample();
                                goback.Hide();


                            }
                        }
                        else MessageBox.Show("You have entered incorrect credentials. Please try again", "error", MessageBoxButton.OK, MessageBoxImage.Error);
                    }
                }
                myCon.dbCon.Close();

                nonqueryCommand.CommandType = CommandType.Text;
                nonqueryCommand.CommandText = "INSERT tblLoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime)";
              //nonqueryCommand.Parameters.AddWithValue("@ID", SqlDbType.Int); this did not work
              //nonqueryCommand.Parameters["@ID"].Value = this.ID; this did not work
                nonqueryCommand.Parameters.AddWithValue("@UserName", txtUserName.Text);
                nonqueryCommand.Parameters.AddWithValue("@LoggedInDate", DateTime.Now);
                nonqueryCommand.Parameters.AddWithValue("@LoggedInTime", DateTime.Now.ToString("HH:mm"));
                thisConnection.Open();
                nonqueryCommand.ExecuteNonQuery(); // error pointing here
                nonqueryCommand.CommandText = "SELECT @ID = SCOPE_IDENTITY()";
                int id = (int)nonqueryCommand.ExecuteScalar();
               // int  id = Convert.ToInt32(nonqueryCommand.ExecuteScalar()); this line did not work
                this.ID = id;
                myCon.dbCon.Close();
                break;

What is `SELECT @ID = SCOPE_IDENTITY()` for? You are try to execute this command not inserting one.. — Soner Gönül, Dec 02 '13 at 14:11
From Mikes dotting blog and I think from D Stanley solution it suppose to get the identity of the most recently added record. I can see data saved in the database btw. — bucketblast, Dec 02 '13 at 14:18

D Stanley · Accepted Answer · 2013-12-02T16:17:11.630

5

The problem is still that you're trying to use the same "scope" with two different SQL commands. Even thought they are the same "variable" in C# in SQL they have different scope.

You'll need to execute both statements in one command and add the @ID parameter as an Output parameter in order to insert and get the identity out:

nonqueryCommand.CommandType = CommandType.Text;
nonqueryCommand.CommandText = "INSERT tblLoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime); " + 
                              "SELECT @ID = SCOPE_IDENTITY()";
nonqueryCommand.Parameters.AddWithValue("@UserName", txtUserName.Text);
nonqueryCommand.Parameters.AddWithValue("@LoggedInDate", DateTime.Now);
nonqueryCommand.Parameters.AddWithValue("@LoggedInTime", DateTime.Now);
nonqueryCommand.Parameters.Add("@ID",SqlDbType.Int).Direction = ParameterDirection.Output;

thisConnection.Open();
nonqueryCommand.ExecuteNonQuery(); 

int id = (int)nonqueryCommand.Parameters["@ID"];

edited Dec 02 '13 at 16:17

answered Dec 02 '13 at 14:40

D Stanley

149,601
11
178
240

What is ParameterDirection and Parameters on the last line? IntelliSense is not picking them up. – bucketblast Dec 02 '13 at 15:34
Well you're using Parameters on the 3 lies before it so I'm not sure why it's complaining now - `ParameterDirection` should just be `Direction` - I've updated my answer appropriately. – D Stanley Dec 02 '13 at 15:36
Thank you - it still complaining about the Parameters on the last line. – bucketblast Dec 02 '13 at 15:45
1

int id = (int)nonqueryCommand.Parameters["@ID"]; needs .value on the end or it will not cast right... int id = (int)nonqueryCommand.Parameters["@ID"].value; – Matt Mar 02 '14 at 20:44

score 0 · Answer 2 · answered Dec 02 '13 at 14:22

0

Here:

nonqueryCommand.CommandText = "SELECT @ID = SCOPE_IDENTITY()";

your SQL assigns a value to a variable that is not declared. Since you are using ExecuteScalar, you probably just mean:

nonqueryCommand.CommandText = "SELECT SCOPE_IDENTITY()";

Note that you might need to cast it - it may come back as decimal.

answered Dec 02 '13 at 14:22

Marc Gravell

1,026,079
266
2,566
2,900

Specified cast is not valid.? Is that what you meant by "might need to cast it". I need to into this because I don't know it means – bucketblast Dec 02 '13 at 14:25
@bucketblast yup; try `int id = (int)(decimal)nonqueryCommand.ExecuteScalar();` – Marc Gravell Dec 02 '13 at 14:32
That did not work either. I tried nonqueryCommand.CommandText = "SELECT @@IDENTITY"; int id = System.Convert.ToInt32(nonqueryCommand.ExecuteScalar()); This sort of works but two xaml windows open at the same time. Why is that? And when I enter incorrect username and password it does not recognise and just opens the windows xaml, why is that – bucketblast Dec 02 '13 at 15:37

Must declare the scalar variable "@ID" for insert parameter

2 Answers2

Linked