在'='附近帮助C＃SQL错误 [英] Help C# SQL error near '='

问题描述

 textBox11.Text = DateTime.Now.ToShortDateString（）; 
 
 if（textBox1.Text！=）
 {
 
 textBox5.Text = label7.Text; 
 
 con.Open（）; 
 string query =UPDATE Emp2 SET DateOut ='+ textBox11.Text +'& TimeOut ='+ textBox5.Text +'WHERE No ='+ textBox1.Text +'; 
 SqlDataAdapter SDA = new SqlDataAdapter（query，con）; 
 SDA.SelectCommand.ExecuteNonQuery（）; 
 con.Close（）; 
 MessageBox.Show（UPDATE SUCCESS！）; 
 formload（）; 
} 

类型'系统的未处理异常。 System.SData.dll中发生了Data.SqlClient.SqlException 
 
附加信息：'='附近的语法不正确。

我尝试了什么：



尝试了一切对我无效的我需要帮助谢谢

解决方案

两件事



1）你的SQL容易受到SQL注入攻击。简单的谷歌搜索将为您提供大量的信息。鉴于您的错误，您可能会遇到sql注入，具体取决于您尝试插入的数据类型...例如，如果我设置textBox1.Text ='This'Is'AwfulInput，则可能触发此错误也可能。



2）

 string query =UPDATE Emp2 SET DateOut ='+ textBox11.Text +'& TimeOut = '+ textBox5.Text +'WHERE No ='+ textBox1.Text +'; 

看看你的SQL语句，你有一个逗号应该是的&符号。鉴于我无法访问您的代码或数据库，这是可能的解决方案。

UPDATE Emp2 SET DateOut ='+ textBox11.Text +'，TimeOut ='+ textBox5.Text +'WHERE No ='+ textBox1.Text +'

一个基本的更新语句应该是这样的，UPDATE TableName SET Column1 =''，Column2 =''WHERE Id = 2.你表示你试过一切所以我建议你在你的旅程中遵循一些sql教程学习SQL作为UPDATE语句是相当直接的（这并不意味着讽刺或粗鲁，真正意味着有用的建议）。

代码的一些问题;漏洞，您的实际问题，效率低下以及一些注意事项/建议。



1.永远不应使用串联字符串，这是一个SQL注入漏洞;它已经有20多年的历史了，并且是不可接受的。使用SqlParamaters将值添加到命令对象。



2. SET列表中的项目应使用逗号（，）分隔 - 而不是放大器（& amp; ）。



3. SqlDataAdapter用于处理数据检索，并且有一个开销，这个应用程序不需要。运行它所需要的只是一个SqlCommand对象。



4. ExecuteNonQuery返回一个Int32，让你知道受影响的行数。您可以使用它来验证查询是否按预期运行。我把它扔进了你的消息框。



5.你应该使用我添加的try / catch块。理想情况下这应该都包含在<$ c中$ c>使用块来正确处理资源。



enuff of that ...这里是我做的代码重写< pre lang =c＃> 使用（SqlConnection con = new SqlConnection（connectionsting））{
尝试 {
string query = UPDATE Emp2 SET DateOut = @Dateout，TimeOut = @Timeout WHERE No = @No;
SqlCommand cmd = new SqlCommand（query，con）;
cmd.Parameters.AddWithValue（ @ Dateout，textBox11.Text）;
cmd.Parameters.AddWithValue（ @ Timeout，textBox5.Text）;
cmd.Parameters.AddWithValue（ @ No，textBox1.Text）;

con.Open（）;
int RowsAffected = cmd.ExecuteNonQuery（）;
con.Close（）;

MessageBox.Show（ string .Format（ UPDATE SUCCESS！{0}行已更新 .RowsAffected））;
}
catch （例外情况）{
// < span class =code-comment>您的错误处理代码
}
}

您不需要在更新查询中使用& 运算符，只需将其替换为，



更新你的SQL查询如下。

 string query =UPDATE Emp2 SET DateOut ='+ textBox11.Text +'，TimeOut ='+ textBox5.Text +'WHERE No ='+ textBox1.Text +'; 

表示最佳实践，不要使用内联查询，这会导致SQL注入，这是应用程序的主要安全漏洞。

textBox11.Text = DateTime.Now.ToShortDateString();

            if (textBox1.Text != "")
            {

                textBox5.Text = label7.Text;

                con.Open();
                string query = "UPDATE Emp2 SET DateOut ='" + textBox11.Text + "' & TimeOut ='" + textBox5.Text + "' WHERE No = '" + textBox1.Text + "'";
                SqlDataAdapter SDA = new SqlDataAdapter(query, con);
                SDA.SelectCommand.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("UPDATE SUCCESS!");
                formload();
            }

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near '='.

What I have tried:

tried everything nothing works for me i need help thanks

解决方案

Two things

1) Your SQL is vulnerable to SQL injection. Simple google search will provide you plenty of information on this. Given your error, it is possible you've encountered a sql injection depending on what kind of data you are attempting to insert...ex if i set textBox1.Text = "'This'Is'AwfulInput", that could trigger this error potentially as well.

2)

string query = "UPDATE Emp2 SET DateOut ='" + textBox11.Text + "' & TimeOut ='" + textBox5.Text + "' WHERE No = '" + textBox1.Text + "'";

Take a look at your SQL statement, you've got an ampersand where a comma should be. Given I don't have access to your code or DB, this is the likely fix.

"UPDATE Emp2 SET DateOut ='" + textBox11.Text + "', TimeOut ='" + textBox5.Text + "' WHERE No = '" + textBox1.Text + "'"

A basic update statement should look like this, UPDATE TableName SET Column1 = '', Column2 = '' WHERE Id = 2. You indicate you tried "everything" so i would recommend you follow some sql tutorials in your journey to learning sql as UPDATE statements are fairly straight forward (this wasn't meant as snarky or rude, truly meant to be a helpful recommendation).

A few problems with the code; a vulnerability, your actual problem, an inefficiency, and some notes/suggestions for you.

1. Concatenated strings should never be used, this is a SQL Injection vulnerability; it's been known for over 20 years now and is unacceptable. Use SqlParamaters to add the values to the command object.

2. Items in the SET list should be separated with a comma(,)- not an ampsersand (&).

3. The SqlDataAdapter is designed for working with data retrieval, and has an overhead to it which is not needed for this application. All you need to run this is a SqlCommand object.

4. ExecuteNonQuery returns an Int32 to let you know how many rows were affected. You can use this to verify the query was run as expected. I threw it into your message box.

5. You should be using try/catch blocks which I added in. Ideally this should all be wrapped in a using block to properly dispose of the resources.

enuff of that... here is the code rewrite that I did

using (SqlConnection con = new SqlConnection(connectionsting)) {
  try {
    string query = "UPDATE Emp2 SET DateOut = @Dateout, TimeOut = @Timeout WHERE No = @No";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.AddWithValue("@Dateout", textBox11.Text);
    cmd.Parameters.AddWithValue("@Timeout", textBox5.Text);
    cmd.Parameters.AddWithValue("@No", textBox1.Text);

    con.Open();
    int RowsAffected = cmd.ExecuteNonQuery();
    con.Close();

    MessageBox.Show(string.Format("UPDATE SUCCESS! {0} rows were updated". RowsAffected));
  }
  catch (Exception ex) { 
    // your error handling code
  }
}

You don't need to use & operator in update query, Just replace it with ,

Update your SQL query as below.

string query = "UPDATE Emp2 SET DateOut ='" + textBox11.Text + "', TimeOut ='" + textBox5.Text + "' WHERE No = '" + textBox1.Text + "'";

for best practices, Don't use inline queries, It leads to SQL injection which is a major security hole for your application.

这篇关于在'='附近帮助C＃SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！