0

In my application am allowing the user to delete an entire row from the grid view when he clicks on delete button. but am getting an error in delete query. Error is "Invalid column name" my code is as follows:

<asp:GridView ID="GridView1" runat="server"  DataKeyNames="Username" AutoGenerateColumns="false" OnPageIndexChanging="GridView1_PageIndexChanging" OnRowDeleting="GridView1_RowDeleting" >
<Columns>
<asp:BoundField DataField="UserName" HeaderText="UserName" ReadOnly="true" />
<asp:BoundField DataField="Name" HeaderText="Name" ReadOnly="true" />
<asp:BoundField DataField="Password" HeaderText="Password" ReadOnly="true" />
<asp:BoundField DataField="Email" HeaderText="Email" ReadOnly="true" />
<asp:CommandField ShowDeleteButton="true"  />
</Columns>
</asp:GridView>

C# code is:

SqlConnection conn = new SqlConnection(@"Data Source=CHINNU-PC\SQLEXPRESS; Initial Catalog= CarDetails; Integrated Security=True");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvbind();
        }
    }

    protected void gvbind()
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("Select * From [User]", conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        conn.Close();
        if (ds.Tables[0].Rows.Count > 0)
        {
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }

 public void Delete(string UserName)
        {
            string sql = "Delete From [User] Where UserName=" + UserName;

            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            Delete(GridView1.DataKeys[e.RowIndex].Values[0].ToString());
            gvbind();
        }
1
  • Side note: You could write this (@"Data Source=CHINNU-PC\SQLEXPRESS; Initial Catalog= CarDetails; Integrated Security=True"); like this (@"Data Source=.\SQLEXPRESS; Initial Catalog= CarDetails; Integrated Security=True"); - see how I replaced the computer name with a .? Now your code wont break if you try to set it up on a computer that isn't called CHINNU-PC. Commented Aug 22, 2013 at 16:40

2 Answers 2

4

You are going to get lots of comments about SQL injection etc....

However, for the sake of answering - you need to wrap UserName in single quotes in your SQL statement.

So this -

string sql = "Delete From [User] Where UserName=" + UserName;

Should be this:

string sql = "Delete From [User] Where UserName='" + UserName + "'";
Sign up to request clarification or add additional context in comments.

1 Comment

SQL INJECTION!! SQL INJECTION!! SQL INJECTION!! :) But seriously don't just take Darren's reply, you need to fix that security hole - your site could be hacked through it. I'm sure this particular page is within a locked down admin panel but that doesn't mean youre safe and it doesn't mean you haven't potentially made this mistake in front end code.
0

This code is wide open to SQL Injection attacks and can reveal all usernames and passwords simply by entering ' OR 1=1;-- as a username or password. The way you create your SQL statements makes your application vulnerable to SQL injection, one of the most common ways for someone to hack a site. Instead of doing this, prefer parameterized queries.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.