必须声明标量变量

本文关键字：变量 标量 声明 | 更新日期: 2023-09-27 18:12:24

好吧，我有一个艰难的时间，我花了几天时间与谷歌等试图找出我搞砸了。

我对c#非常陌生，我正在做的是试图从我的SQL Server中抓取Customer_ID (Primary Key)，将1添加到值，然后输入下一个客户id。如果我拿出的部分，将值添加到Customer_ID字段，我得到一个错误说我不能插入一个空值。任何帮助都将非常感激。

错误是:

必须声明标量变量"@custID"

下面是我的代码: 
string firstName = fName.Text;
string lastName = lName.Text;
string address1 = address.Text;
string city = addressCity.Text;
string state = addressState.Text;
string zip1 = addressZip.Text;
string phoneNum = phone.Text;
string emailadd = custEmail.Text;
int finalCustID = 0;
SqlCommand getMaxCustID = new SqlCommand("SELECT MAX(Customer_ID) FROM dbo.Customers", Form1.sqlConnection);
getMaxCustID.Parameters.AddWithValue("@custID", finalCustID);
Int32 count = Convert.ToInt32(getMaxCustID.ExecuteScalar());
finalCustID = (int)count + 1;
//Int32 newCustID = count + 1;
SqlCommand saveRecord = new SqlCommand("INSERT INTO dbo.Customers (Customer_ID,First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)" + "Values (@custID'firstName','lastName','address1','city','state','zip1','phoneNum','emailadd')", Form1.sqlConnection);
Console.Write(" This is the final ID " + finalCustID + " 'n");
try
{
    saveRecord.ExecuteNonQuery();
}
catch (Exception ex)
{ 
    Console.WriteLine(ex.Message);
    Console.Write(saveRecord);
}

这是我最后得到的。我不认为这是最好的，但它工作正常。谢谢大家的帮助:

        string firstName = fName.Text;
        string lastName = lName.Text;
        string address1 = address.Text;
        string city = addressCity.Text;
        string state = addressState.Text;
        string zip1 = addressZip.Text;
        string phoneNum = phone.Text;
        string emailadd = custEmail.Text;
        int finalCustID = 0;
        SqlCommand getMaxCustID = new SqlCommand("SELECT MAX(Customer_ID) FROM dbo.Customers", Form1.sqlConnection);
        //getMaxCustID.Parameters.AddWithValue("@custID", finalCustID);
        Int32 count = Convert.ToInt32(getMaxCustID.ExecuteScalar());
        finalCustID = (int)count + 1;
        //Int32 newCustID = count + 1;
        SqlCommand saveRecord = new SqlCommand("INSERT INTO dbo.Customers " + "(Customer_ID,First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)" + " Values (@custID,@fName,@lName,@address,@city,@state," + "@zip,@phoneNumber,@custEmail)", Form1.sqlConnection); ;
        Console.Write(" This is the final ID " + finalCustID + " 'n");
        try
        {
            saveRecord.Parameters.AddWithValue("@custID", finalCustID);
            saveRecord.Parameters.AddWithValue("@fName", firstName);
            saveRecord.Parameters.AddWithValue("@lName", lastName);
            saveRecord.Parameters.AddWithValue("@address", address1);
            saveRecord.Parameters.AddWithValue("@city", city);
            saveRecord.Parameters.AddWithValue("@state", city);
            saveRecord.Parameters.AddWithValue("@zip", zip1);
            saveRecord.Parameters.AddWithValue("@phoneNumber", phoneNum);
            saveRecord.Parameters.AddWithValue("@custEmail", emailadd);
            saveRecord.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.Write(saveRecord);
        }

必须声明标量变量

您正在向命令添加参数，但您根本没有在命令文本中指定它。

SqlCommand getMaxCustID = new SqlCommand("SELECT MAX(Customer_ID) FROM dbo.Customers",
                                  Form1.sqlConnection);
//getMaxCustID.Parameters.AddWithValue("@custID", finalCustID); //Comment that out

因为你没有使用你的参数@custID，你不需要传递给你的命令。把这行注释掉或者删除。

稍后，您将在您的插入语句中使用参数@custID，您需要将参数添加到该命令对象。

saveRecord.Parameters.AddWithValue("@custID", finalCustID);

(感谢@N55PEC)，您在参数和下一个值之间的INSERT命令文本中缺少逗号。它应该是这样的:

注意:额外的格式被用来保持答案在视窗内。

SqlCommand saveRecord = new SqlCommand("INSERT INTO dbo.Customers " +
       "(Customer_ID,First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)" + 
       " Values (@custID,'firstName','lastName','address1','city','state'," +
       "'zip1','phoneNum','emailadd')", Form1.sqlConnection);

需要@custID参数的命令称为saveRecord，您将其添加到称为getMaxCustID的命令

SqlCommand getMaxCustID = new SqlCommand("SELECT MAX(Customer_ID) FROM dbo.Customers", Form1.sqlConnection);
Int32 count = Convert.ToInt32(getMaxCustID.ExecuteScalar());
finalCustID = (int)count + 1;
SqlCommand saveRecord = new SqlCommand("INSERT INTO dbo.Customers (Customer_ID,First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)" + "Values (@custID', firstName','lastName','address1','city','state','zip1','phoneNum','emailadd')", Form1.sqlConnection);
saveRecord.Parameters.AddWithValue("@custID", finalCustID);

然而，正如我在上面的评论中所说，在多用户环境中，使用MAX来检索下一个号码以分配给下一个客户显然是错误的。如果在执行读取客户id和插入新记录之间，另一个用户添加了一个客户，该怎么办?您将以主键违反异常结束。

处理此场景的最佳方法是将Customer_ID列作为IDENTITY列，然后编写如下代码

SqlCommand saveRecord = new SqlCommand(@"INSERT INTO dbo.Customers 
       (First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)
        Values (@custID,'firstName','lastName','address1','city','state','zip1',
       'phoneNum','emailadd');
       SELECT SCOPE_IDENTITY()", Form1.sqlConnection);
int newCustomerID = Convert.ToInt32(saveRecord.ExecuteScalar());

注意，如果您将Customer_ID作为IDENTITY列，则不应该尝试为其传递值，但是您可以使用SELECT SCOPE_IDENTITY()

检索数据库引擎分配的值。

按原样使用以下行:

SqlCommand getMaxCustID = new SqlCommand("SELECT MAX(Customer_ID) FROM dbo.Customers", Form1.sqlConnection);
Int32 count = Convert.ToInt32(getMaxCustID.ExecuteScalar());

: 

int finalCustID = (int)count + 1;

然后:

SqlCommand saveRecord = new SqlCommand("INSERT INTO dbo.Customers (Customer_ID,First_Name, Last_Name, Customer_Address,City,St,Zip,Phone,Email)" + "Values ('finalCustID.toString() ','firstName','lastName','address1','city','state','zip1','phoneNum','emailadd')", Form1.sqlConnection);

最后:

saveRecord.ExecuteNonQuery();
相关文章：