C# SQL 插入语句语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17127187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Insert statement syntax
提问by NetUser101
Basically what I am doing is that upon clicking a button, the program will extract data from a particular row according to what the user chose and place it in a different table by using INSERT. The following is the code.
基本上我正在做的是，在单击按钮时，程序将根据用户选择的内容从特定行中提取数据，并使用INSERT. 以下是代码。
private void button3_Click(object sender, EventArgs e)
{
const String connectionString = "Data Source = Vanessa-PC\SQLEXPRESS; Initial Catalog = IUMDC; Connect Timeout = 15; Integrated Security = true";
SqlConnection con = new SqlConnection(connectionString);
//int SituationID1;
label24.Show();
foreach (SitID x in Sittbl)
{
if (x.ID == Convert.ToInt16(comboBox1.SelectedItem))
{
try
{
con.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Situation WHERE SituationID=" + x.SitIDs, con);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
sitid1 = Convert.ToInt32(dr[0]);
name1 = dr[4].ToString();
incident1 = Convert.ToDateTime(dr[1]);
charges1 = dr[5].ToString();
nature1 = dr[2].ToString();
}
con.Close();
}
catch (SqlException ex)
{
MessageBox.Show("Database failed to connect" + ex.Message);
}
//SituationID = x.SitIDs;
}
}
try
{
con.Open();
SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con);
SqlDataReader dr1 = command1.ExecuteReader();
con.Close();
}
catch (SqlException ex)
{
MessageBox.Show("Database failed to connect" + ex.Message);
}
//Situation Sit = new Situation();
//Sit.ShowDialog();
}
My code fails and says
我的代码失败并说
"Incorrect syntax near row item"
“行项目附近的语法不正确”
The types of the two tables are the same and I have tried to test this thoroughly!
两个表的类型相同，我已经尝试彻底测试！
采纳答案by Soner G?nül
Looks like your last sql statement is not correct. Maybe the reason is usage of apostrophe in your sql stament, but you should not care about it. I explan in the middle of my answer why you shouldn't care.
看起来您的最后一条 sql 语句不正确。也许原因是在你的 sql 语句中使用了撇号，但你不应该关心它。我在回答的中间解释了为什么你不应该关心。
SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con);
To find out what is exactly the problem, you can specify column names instead. But I suggest you to use parameterized querynot even necessary to specify your column names.
要找出究竟是什么问题，您可以改为指定列名。但我建议您使用参数化查询，甚至不需要指定列名。
SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES(@sitid1, @incident1, @nature1, @name1, @charges1)", con);
command1.Parameters.AddWithValue("@sitid1", sitid1);
command1.Parameters.AddWithValue("@incident1", incident1.ToString("YYYY-mm-DD"));
command1.Parameters.AddWithValue("@nature1", nature1);
command1.Parameters.AddWithValue("@name1", name1);
command1.Parameters.AddWithValue("@charges1", charges1);
command1.ExecuteNonQuery();
You should always use parameterized queries. This kind of codes are open for SQL Injectionattacks.
您应该始终使用参数化查询。这种代码对SQL 注入攻击是开放的。
Also as Marc mentioned, there is no point to use ExecuteReader()for this sql statement because it is only INSERTdata, doesn't returns any data. Because of that, you only need to use ExecuteNonQuery()in this case.
同样正如 Marc 所提到的，ExecuteReader()这个 sql 语句没有意义，因为它只是INSERT数据，不返回任何数据。因此，您只需要ExecuteNonQuery()在这种情况下使用。
回答by Darren
Try specifying the columns in your query:
尝试在查询中指定列：
"INSERT INTO CurrentSit (sitid, incident, nature, name1, charges) VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con)";
As an additional note, also learn to use paramerterized queries. For instance:
作为附加说明，还可以学习使用参数化查询。例如：
command1.Parameters.AddWithValue("@name1", name1);
command1.Parameters.AddWithValue("@charges1", charges1);
http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/
http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/
回答by Michael Edenfield
Your immediate problem is that your INSERTstatement has some invalid syntax it in; most likely one of the values you are using to build it has an apostrophe in it that's terminating your string early.
您的直接问题是您的INSERT语句中有一些无效的语法；您用来构建它的值之一很可能有一个撇号，它会提前终止您的字符串。
Your bigger problem is that you should never everbuild insert statements this way. Besides being highly prone to errors of the sort you're seeing, it's also a classic opening to SQL injection attacks. (Imagine, for example, if a situation's nature foo'); drop table situation; --).
你更大的问题是你永远不应该以这种方式构建插入语句。除了非常容易出现您所看到的那种错误之外，它也是 SQL 注入攻击的经典开端。（想象一下，例如，如果情况的性质foo'); drop table situation; --）。
You should be using parameterized queries:
您应该使用参数化查询：
var sql = "INSERT INTO CurrentSit VALUES (@sitid, @incident, @nature, @name, @charges)"
var cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@Sitid", SqlDbType.Int).Value = sitid1;
// etc.
