更新数据库相关问题 [英] Update database related issue

问题描述

我想更新数据库中的密码，我正在使用以下代码，但出现异常"UPDATE查询语法错误".
请帮我.

Hi,

I want to update my password in database ,I am using following code but getting exception "UPDATE query syntax error".
Pls help me out.

protected void  Button1_Click(object sender, EventArgs e)
{

    int flag = 0;
  OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\shreedharmanagementsystem\\App_Data\\my1.mdb");
    OleDbCommand com = new OleDbCommand("select * from Login", con);
    con.Open();
    if (con.State == ConnectionState.Open)
    {
        OleDbDataReader dtr;
        dtr = com.ExecuteReader();
        while (dtr.Read())
        {

            if (dtr[1].ToString().Equals(TextBox1.Text))
            {
                flag = 1;
                //break;
                if (TextBox2.Text == TextBox3.Text)
                {
                    string update = "UPDATE Login SET Password = ''" + TextBox2.Text + " '' WHERE UserId = ''" + dtr[0].ToString() + "''";
                    OleDbCommand cmd = new OleDbCommand(update, con);
                    cmd.ExecuteNonQuery();                     // EXCEPTION 
                    Response.Redirect("feemaster.aspx");
                }
            }
        }

        if (flag == 1)
        {
            HttpCookie uname = new HttpCookie("username");
            HttpCookie upass = new HttpCookie("userpass");
            HttpCookie rights = new HttpCookie("rights");
            uname.Value = TextBox1.Text;
            upass.Value = TextBox2.Text;
            //rights.Value = dtr[2].ToString();
            Response.Cookies.Add(uname);
            Response.Cookies.Add(upass);
            //Response.Cookies.Add(rights);
            Response.Redirect("feediposite.aspx");
        }
    }
    con.Close();

}

谢谢
Abhishek

Thanks
Abhishek

推荐答案

尝试关注

Try following

string update = "UPDATE [Login] SET Password = '" + TextBox2.Text + " ' WHERE UserId = '" + dtr[0].ToString() + "'";

使用OleDbParameter [配置参数和参数数据类型 [^ ] .

Use OleDbParameter[^], Configuring Parameters and Parameter Data Types[^].

// NOTE: The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement
string update = "UPDATE [Login] SET Password = ? WHERE UserId = ?";

OleDbCommand cmd = new OleDbCommand(update, con);

// The order of Parameters is important.
cmd.Parameters.AddWithValue("@textbox2", TextBox2.Text);
cmd.Parameters.AddWithValue("@id", dtr[0].ToString());

cmd.ExecuteNonQuery();

如果TextBox2.Text的值为 my''newPass; Word

The SQL UPDATE will fail, if TextBox2.Text has value my''newPass;Word

，则SQL UPDATE将失败.我刚刚创建了一个小型控制台应用程序，该应用程序再现了您的问题，并看到了CS2011和Prem Shanker Verma拥有正确的解决方案，但关键字错误:)

实际上是密码"导致了UPDATE语句中的问题.将其包装在方括号("[]")中，问题就消失了！

因此，更新后的语句应如下所示:

I just created a small console app that reproduces your problem, and saw that CS2011 and Prem Shanker Verma had the right solution, but wrong keyword :)

It''s actually "Password" that''s causing the problem in your UPDATE statement. Wrap that in square brackets ("[]") and the problem goes away!

So, your updated statement should look like this:

string update = "UPDATE Login SET [Password] = ''" + TextBox2.Text + "'' WHERE UserId = ''" + dtr[0].ToString() + "''";

并记住要删除TextBox2内容之后的多余空格(上面的语句是正确的):)

And remember to remove the extraneous space that is being appended to the password after the contents of TextBox2 (the above statement is correct) :)

这篇关于更新数据库相关问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！