必须声明标量变量“@ID"；用于插入参数 [英] Must declare the scalar variable "@ID" for insert parameter

问题描述

我收到错误，必须声明标量变量@ID".指向 ExecuteScalar 行.我看着护目镜，我认为它与 ID 的插入参数有关.然后我又读到可能有拼写错误.在我的数据库中，我将列名声明为 ID，数据类型声明为 int，将Is Identity"设置为 Yes.由于我不打算手动插入 ID 列，因此我认为这就是我遇到问题并且我不知道如何解决此问题的原因.

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.

我想做的是插入用户名、登录日期和时间.用户注销时更新同一列(同一 id 列).当用户再次登录时创建一个新列，依此类推.我正在使用我在此处询问的类似代码和这里，当时 D Stanley 帮助了我.

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;

推荐答案

问题仍然是您试图对两个不同的 SQL 命令使用相同的范围".即使认为它们在 SQL 中的 C# 中是相同的变量"，但它们具有不同的作用域.

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.

您需要在一个命令中执行这两个语句，并添加 @ID 参数作为 Output 参数，以便插入和取出标识:

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

这篇关于必须声明标量变量“@ID"；用于插入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！