ASP。NET SQL -不正确的语法

Guid currentUserID = (Guid)Session["UserId"];
String accomid = (String)Session["AccomID"];
String schdid = (String)Session["SchdID"];
String schdprice = (String)Session["SchdPrice"];
con.Open();

cmd = new SqlCommand("insert into Transactions (Accom_ID, UserID, Schd_ID, Trans_CardNo, Trans_CardSecurity, Trans_CardName, Trans_Paid, Trans_Cost) values('" + accomid + "','" + currentUserID + "','" + schdid + "','" + txtCardNumber.Text + "','" + txtCardSecurityNumber.Text + "','" + txtName.Text + "','" + "Yes" + "','" + schdprice + "')", con);
cmd.ExecuteNonQuery(); 
cmd = new SqlCommand("UPDATE Schedule (Schd_Avaliable) values('" + "No" + "')", con);
cmd.ExecuteNonQuery();

I'm getting the error

我得到错误

Incorrect syntax near '('.

不正确的语法附近“(”。

If I remove these statements:

如果我删除这些语句:

cmd = new SqlCommand("UPDATE Schedule (Schd_Avaliable) values('" + "No" + "')", con);
cmd.ExecuteNonQuery();

I get no error. Any ideas what's wrong?

我没有错误。任何想法怎么了?

3 个解决方案

#1

INSERT and UPDATE syntax in SQL is different. Your update should be like this:

在SQL中插入和更新语法是不同的。你的更新应该是这样的:

UPDATE Table SET
   Column = Value
   ,Column2 = Value2
   ,Column3 = Value3
WHERE
    <constraint(s)>

You're missing any constraints from your UPDATE statement. Executing it the way its written currently would update every single row in your table. Also, I would recommend that you change your SQL to use prepared statements. You're currently wide open to SQL injection attacks.

您缺少UPDATE语句中的任何约束。按照它当前编写的方式执行它，可以更新表中的每一行。另外，我建议您更改SQL以使用准备好的语句。您现在对SQL注入攻击非常开放。

A couple of other things: if you are using Yes/No values, I would recommend switching to using a bit (boolean) column. Are the "Yes" and "No" values going to be dynamic? If not, you don't need to do this:

还有一些其他的事情:如果您正在使用Yes/No值，我建议您切换到使用bit (boolean)列。“是”和“否”的值是动态的吗?如果不是，你不需要这样做:

... "'" + "No" + "'" ...

You can just do

你可以做

... "'No'" ...

And lastly, you have a spelling mistake: "Avaliable" > "Available"

最后，你有一个拼写错误，"Avaliable" > "Available"

#2

Your SQL is wrong.

SQL是错误的。

"UPDATE Schedule SET Schd_Available = 'No'"

或

"UPDATE Schedule SET Schd_Available = '" + "No" + "'"

Notice the SET and =

注意SET和=。

#3

Update your UPDATE statement to the following.

将更新语句更新为以下内容。

cmd = new SqlCommand("UPDATE Schedule SET Schd_Available = '" + "No" + "'", con);
        cmd.ExecuteNonQuery();

You might also want to consider a WHERE clause here. Otherwise Schd_Available wil be set to No for all rows.

您可能还需要考虑这里的WHERE子句。否则，所有行都将设置为No。

For a reference to the UPDATE syntax, check out http://www.w3schools.com/sql/sql_update.asp

有关更新语法的引用，请参阅http://www.w3schools.com/sql/sql_update.asp。

EDIT: Added in the =, originally had it, then changed format and forgot. Thanks for the heads up.

编辑:添加在=中，原本有它，然后更改格式和忘记。谢谢你的提醒。

#1