locked
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 '' at line 1 RRS feed

  • Question

  • User849610253 posted

    Hi All,

    i have this code and i got error like this..

    this is my cs code...

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    string username = GridView1.Rows[e.RowIndex].Cells[1].Text.ToString();
    conn.Open();
    MySqlCommand cmd = new MySqlCommand("DELETE FROM userdata where username="+username,conn);
    cmd.Connection = conn;
    cmd.ExecuteNonQuery();//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 '' at line 1

    conn.Close();

    }

    Best regards,

    praveen

    Monday, August 25, 2014 2:55 AM

Answers

  • User1633621018 posted

    Hi Praveen,

    Change you code to : MySqlCommand cmd = new MySqlCommand("DELETE FROM userdata where username='"+username+"'",conn);

    Regards

    Pawan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2014 3:04 AM
  • User-1454326058 posted

    Hi praveen_527,

    After modify the code as PawanPal said that, please check whether the specific record is deleted form the database. You could connect to the database to check it directly or you may refresh the current page if the current page load that data.

    For your code, you don’t bind the data to the grid after delete the record in the GridView1_RowDeleting method. So it won’t show anything.

    For the other issue, you should to check whether the textboxUN, textboxEmail, textboxPwd and ddllist aren’t null.

    Best Regards,

    Starain Chen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 26, 2014 3:27 AM

All replies

  • User-1360095595 posted

    Use parameterized queries to add the username value. It'll solve your problem. 

    The issue is that you don't have username wrapped in single quotes to make it a string/varchar value.

    Monday, August 25, 2014 3:00 AM
  • User1633621018 posted

    Hi Praveen,

    Change you code to : MySqlCommand cmd = new MySqlCommand("DELETE FROM userdata where username='"+username+"'",conn);

    Regards

    Pawan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2014 3:04 AM
  • User849610253 posted

    Hi Pawan,

    I Change the code But it display nothing when i click on my delete in grid..

    it wont display any error and anything..

    Regards

    Praveen

    Monday, August 25, 2014 3:37 AM
  • User1633621018 posted

    Hi Praveen,

    Can you please post your code to look after?

    Thanks

    Monday, August 25, 2014 4:14 AM
  • User849610253 posted

    hi Pawan,

     my cs page data as following..

    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.Configuration;
    using System.Data;
    using System.Drawing;
    using System.Data.SqlClient;


    public partial class Manager : System.Web.UI.Page
    {
    MySqlConnection conn = new MySqlConnection("Server=localhost;Port=3306;Database=registrationconectionstring;Uid=root;Pwd=......;");
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {

    BindData();
    }

    }

    protected void BindData()
    {
    conn.Open();
    MySqlCommand cmd = new MySqlCommand();
    string mysql = "SELECT * FROM `userdata`";
    MySqlDataAdapter msda = new MySqlDataAdapter(mysql, conn);
    DataSet ds = new DataSet();
    msda.Fill(ds);
    lblresult.Text = "Total items found:" + ds.Tables[0].Rows.Count.ToString() + "Records.";

    GridView1.DataSource = ds.Tables[0];
    GridView1.DataSourceID = string.Empty;
    conn.Close();

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
    GridView1.EditIndex = -1;
    BindData();


    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    string username = GridView1.Rows[e.RowIndex].Cells[1].Text.ToString();
    conn.Open();
    MySqlCommand cmd = new MySqlCommand("DELETE FROM userdata where username="+username,conn);
    cmd.Connection = conn;
    cmd.ExecuteNonQuery();//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 '' at line 1

    conn.Close();

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
    GridView1.EditIndex = e.NewEditIndex;
    BindData();


    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    string username = GridView1.Rows[e.RowIndex].Cells[1].Text.ToString();
    TextBox textboxUN = (TextBox)GridView1.Rows[e.RowIndex].FindControl("textboxUN");
    TextBox textboxEmail = (TextBox)GridView1.Rows[e.RowIndex].FindControl("textboxEmail");
    TextBox textboxPwd = (TextBox)GridView1.Rows[e.RowIndex].FindControl("textboxPwd");
    TextBox ddllist = (TextBox)GridView1.Rows[e.RowIndex].FindControl("ddllist");
    conn.Open();
    MySqlCommand cmd = new MySqlCommand("UPDATE userdata set username='" + textboxUN.Text + "',email='" + textboxEmail.Text + "',password='" + textboxPwd.Text + "',country='" + ddllist.Text + "' where username=" + username, conn);// in this i got Object reference not set to an instance of an object.

    cmd.Connection = conn;

    cmd.ExecuteNonQuery();
    conn.Close();
    lblresult.Text = username + "Details Updated Successfully";
    lblresult.ForeColor = Color.GreenYellow;
    GridView1.EditIndex = -1;
    BindData();

    }
    }

    thank you.

    Monday, August 25, 2014 4:34 AM
  • User-1454326058 posted

    Hi praveen_527,

    After modify the code as PawanPal said that, please check whether the specific record is deleted form the database. You could connect to the database to check it directly or you may refresh the current page if the current page load that data.

    For your code, you don’t bind the data to the grid after delete the record in the GridView1_RowDeleting method. So it won’t show anything.

    For the other issue, you should to check whether the textboxUN, textboxEmail, textboxPwd and ddllist aren’t null.

    Best Regards,

    Starain Chen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 26, 2014 3:27 AM