必须声明标量变量@ for insert [英] Must declare the scalar variable @ for insert

问题描述

我正在尝试将数据插回到mysql数据库中，但我不知道我从哪里得到此错误。



我继续收到错误...

发生了'System.Data.SqlClient.SqlException'类型的未处理异常System.Data.dll



附加信息：'nvarchar'附近的语法不正确。



必须声明标量变量@。

I'm trying to insert data back into mysql database but i'm not sure where I'm getting this error from.

I keep on getting the error of ...
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'nvarchar'.

Must declare the scalar variable "@".

private void SubmitBtn_Click(object sender, EventArgs e)
 {
     SqlCommand cmd = new SqlCommand ("INSERT into [Materials Inventory] (Item, Barcode, Material, Location, Amount, [Date Added]) "+
          " Values (@Item,@Barcode,@Material,@Location,@Amount,@[Date Added])", sqlcon);   // add the parameter names

     sqlcon.Open();  //open the connection
     cmd.Parameters.AddWithValue("@Item", SqlDbType.VarChar).Value = comboBox1.Text;
     //comboBox1.Text);
     cmd.Parameters.AddWithValue("@Barcode", textBox3.Text);  // parameter name with respect to the value
     cmd.Parameters.AddWithValue("@Material", textBox4.Text);
     cmd.Parameters.AddWithValue("@Location", textBox5.Text);
     cmd.Parameters.AddWithValue("@Amount", textBox6.Text);
     cmd.Parameters.AddWithValue("@Date Added", textBox7.Text);

     cmd.ExecuteNonQuery();
     sqlcon.Close();  // close the connection

            }

我的尝试：



代码重写，取出@

What I have tried:

code rewrite, taking out the @

推荐答案

名称很重要！

比较：

Names are important!
Compare:

Values (@Item,@Barcode,@Material,@Location,@Amount,@[Date Added])

使用您设置的参数：

cmd.Parameters.AddWithValue("@Date", textBox7.Text);

看到区别？

尝试：

See the difference?
Try:

Values (@Item,@Barcode,@Material,@Location,@Amount,@Date)

它应该可以工作。



但是......你会遇到问题。

两个会发生，都是由同一件事引起的：你没有验证。

1）如果用户在textBox6中输入一个数字，它就会起作用。但是，如果他们犯了错误，你的应用程序将崩溃，出现SQL转换错误。

2）textBox7和日期相同，但更糟糕的是 - 你不能保证用户输入日期SQL Server实例期望的格式相同，因此即使用户输入了有效日期，您的应用程序仍可能会崩溃，因为SQL无法识别它，或者更糟糕的是，它会错误地将其转换为DATE并将其存储为错误的值。



始终使用int.TryParse，double.TryParse和DateTime.TryParse将用户输入转换为实际值，然后将转换后的值作为参数传递给SQL。



BTW：帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得今天的TextBox8是手机号码，但是当你有在三周后修改它，你会吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键......

and it should work.

But ... you are going to get problems.
Two that will occur, both caused by the same thing: you don't validate.
1) If the user enters a number in textBox6, it'll work. But if they make a mistake, your app will crash with an SQL conversion error.
2) The same thing with textBox7 and a date, but even worse - you have no guarantee that the user entered a date in the same format the SQL server instance expects, so even if the user enters a valid date, your app may still crash because SQL doesn't recognise it, or worse, converts it to a DATE incorrectly and stores it as the wrong value.

Always use int.TryParse, double.TryParse, and DateTime.TryParse to convert user inputs to actual values, then pass the converted values to SQL as parameters.

BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

这篇关于必须声明标量变量@ for insert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！