Click here to Skip to main content
15,885,899 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I AM TRYING TO UPDATE AN MYSQL DATABASE...THE ERROR APPEARS SAYING
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''btechstudent_academics' set Tenth=@Tenth Where Regd_no=@Regd_no' at line 1"..PLEASE SUGGEST HOW TO CORRECT THIS ONE...
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.IO;

public partial class BtechSrtudentUpdate : System.Web.UI.Page
{
    MySqlConnection conn1 = new MySqlConnection("Server=localhost;Database=test;Uid=root;Pwd=1234");
    
    string r=" ";
    protected void Page_Load(object sender, EventArgs e)
    {
        
        TextBoxtRegd.Text = Request.QueryString["regd"];
        
        TextBoxTenth.Text = Request.QueryString["Tenth"];
       
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string Regd_no;
		Double Tenth;
        Regd_no = TextBoxtRegd.Text;
        Tenth = Convert.ToDouble(TextBoxTenth.Text);
         
        conn1.Open();
        string sql2 = "UPDATE 'btechstudent_academics' set Tenth=@Tenth" + " Where Regd_no=@Regd_no;";
        MySqlCommand cmd2 = new MySqlCommand(sql2, conn1);
        MySqlParameter param = new MySqlParameter("@Tenth",MySqlDbType.Double );
        param.Value = Tenth;
        cmd2.Parameters.Add(param );
        MySqlParameter param1 = new MySqlParameter("@Regd_no", MySqlDbType.VarChar,10);
        param.Value = Regd_no ;
        cmd2.Parameters.Add(param1);
     
        try
        {
            Response.Write("HI");
            cmd2.ExecuteNonQuery();

            Response.Write("HI2");
        }
        catch (Exception x)
        { Label1.Text = " " + x.Message; }
        finally
        {
            conn1.Close();
        }
        
    }
    protected void TextBox1_TextChanged(object sender, EventArgs e)
    {

    }
    protected void TextBoxBacklog_TextChanged(object sender, EventArgs e)
    {

    }
    protected void TextBoxTenth_TextChanged(object sender, EventArgs e)
    {

    }
    protected void TextBox9_TextChanged(object sender, EventArgs e)
    {

    }
}
Posted
Updated 9-Mar-15 18:06pm
v2

The problem is in your query, the name of the table is escaped with single quotes (').
This tells MySQL that there is a string value, so it doesn't recognize it as a table name.
Either change them to backticks (`):
C#
string sql2 = "UPDATE `btechstudent_academics` set Tenth=@Tenth" + " Where Regd_no=@Regd_no;";

or leave them out:
C#
string sql2 = "UPDATE btechstudent_academics set Tenth=@Tenth" + " Where Regd_no=@Regd_no;";
 
Share this answer
 
Comments
Somesh Dhal 10-Mar-15 2:58am    
Thank You....it solved my problem...i was confused between the ' and `...
To add a bit more explanations. You basically have three options. Since your table name isn't a reserved word, there's no need to escape it. Because of this the following should suffice:
C#
string sql2 = "UPDATE btechstudent_academics set Tenth=@Tenth Where Regd_no=@Regd_no;";

If it would be a reserved word you would need to escape it. For this you have two techniques. Backticking would always work and the query would look like
C#
string sql2 = "UPDATE `btechstudent_academics` set Tenth=@Tenth Where Regd_no=@Regd_no;";

On the other hand it you have set ANSI_QUOTES[^] on you can use quotation marks like this
C#
string sql2 = "UPDATE \"btechstudent_academics\" set Tenth=@Tenth Where Regd_no=@Regd_no;";

or
C#
string sql2 = @"UPDATE "btechstudent_academics" set Tenth=@Tenth Where Regd_no=@Regd_no;";

For more info, please refer to documentation Schema Object Names[^]
 
Share this answer
 
Hi,

I think the problem could be in update statement.
Try this:
C#
string sql2 = "UPDATE 'btechstudent_academics' set Tenth=" + @Tenth + " Where Regd_no=@Regd_no";
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900