Must declare the scalar variable “@ID” for insert

问题:

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;

回答1:

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"];

回答2:

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.