1

I can't figure out why the following code is not updating either my GridView nor my MySQL Database. Can anyone offer me some tips as to what I may be doing incorrectly? 

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

        connection();

        GridViewRow row = GridView1.Rows[e.RowIndex];
        Label lblID = (Label)row.FindControl("lblID");
        TextBox textName = (TextBox)row.Cells[3].Controls[0];
        TextBox textadd = (TextBox)row.Cells[4].Controls[0];
        TextBox textc = (TextBox)row.Cells[5].Controls[0];

        String query = "update employeeDB set [First Name:]='" + textName.Text + "', [Last Name:]='" + textadd.Text + "', [Email:]='" + textc.Text + "' where id='" + lblID + 1 + "'";

        SqlCommand com = new SqlCommand(query, con);        

        SqlDataReader dr;
        dr = com.ExecuteReader();

        GridView1.EditIndex = -1;

        bind();
    }

Here is my bind method as requested:

private void bind()
{
    connection();
    string query = "select * from employeeDB where [Last Name:] like'" + TextBox1.Text + "%'";

    SqlDataAdapter da = new SqlDataAdapter(query, con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
}
Improve this question
8
  • Be sure you check your query in a database tool to verify it's really doing what you want. Looks like the where clause might be a bit suspect. Oh, and then please do read up about SQL Injection Attacks. Commented Aug 4, 2014 at 1:19
  • Thanks, the query does appear to work manually, so that's not it. Commented Aug 4, 2014 at 1:26
  • Then I'd say Anri probably has your answer below. Commented Aug 4, 2014 at 1:28
  • Read these and fix the code: technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx , en.wikipedia.org/wiki/SQL_injection , msdn.microsoft.com/en-us/library/ff648339.aspx Commented Aug 4, 2014 at 1:34
  • can you try put a breakpoint to ensure the code behind is executing? and which line throwing error if any Commented Aug 4, 2014 at 1:41

4 Answers 4

Reset to default
3

Replace 

dr = com.ExecuteReader();

with

com.ExecuteNonQuery();

ExecuteReader is for SELECT queries.

Also, in real world application you should not build sql string like you do. Use SqlParameter instead to avoid sql injection and many other errors.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Unfortunately, that does not seem to have changed the result at all.
1 
GridViewRow row = GridView1.Rows[e.RowIndex];
            Label lblID = (Label)row.FindControl("lblID");
            TextBox textName = (TextBox)row.Cells[3].Controls[0];
            TextBox textadd = (TextBox)row.Cells[4].Controls[0];
            TextBox textc = (TextBox)row.Cells[5].Controls[0];

            /*are you sure column names are like [First Name:],[Last Name:] and [Email:] in the table*/
            /*Syntax for update command should be like this "UPDATE TableName SET ColumnName1=@Parameter1, ColumnName2=@Parameter2 ....
             * WHERE ColumnName=@ParameterName"
             */
            String query = "update employeeDB set [First Name:]=@FirstName, [Last Name:]=@LastName, [Email:]=@Email where id=@id";

            SqlCommand com = new SqlCommand(query, con);
            com.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = textName.Text;
            com.Parameters.Add("@LastName", SqlDbType.VarChar).Value = textadd.Text;
            com.Parameters.Add("@Email", SqlDbType.VarChar).Value = textc.Text;
            com.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(lblID.Text) + 1;
            con.Open();
            com.ExecuteNonQuery();
            con.Close();

            GridView1.EditIndex = -1;
            bind();
        }
Improve this answer

7 Comments

I get a NullReferenceException on the final parameter with this. But the semi-colons are part of the column name.
Looks like lblID is null
/*put a check*/Label lblID = (Label)row.FindControl("lblID"); if (lblID != null) { }
int id=0; if(!string.IsNullOrEmpty(lblID.Text)) id=Convert.ToInt32(lblID.Text) + 1;
I fixed that because it was supposed to be a text box, now I'm back to not getting errors, but my database not changing at all.
|
0

you should be doing something like this

//Retrieve the table from the session object. DataTable dt = (DataTable)Session["TaskTable"];

//Update the values.
GridViewRow row = TaskGridView.Rows[e.RowIndex];
dt.Rows[row.DataItemIndex]["Id"] = ((TextBox)(row.Cells[1].Controls[0])).Text;
dt.Rows[row.DataItemIndex]["Description"] = ((TextBox)(row.Cells[2].Controls[0])).Text;
dt.Rows[row.DataItemIndex]["IsComplete"] = ((CheckBox)(row.Cells[3].Controls[0])).Checked;

//Reset the edit index.
TaskGridView.EditIndex = -1;

//Bind data to the GridView control.
BindData();
Improve this answer

3 Comments

Should I bind the GridView to a DataSource with this method, or will that matter?
Check this out - msdn.microsoft.com/en-us/library/…
When I click update in my browser for the DB, I get a NullReferenceException on the first DataItem. Could I be selecting the incorrect cell?
0

What are you getting now? Exception or just no error and nothing is happening? Things to check are db connection string-make sure your connection string is pointing to the db you are targeting. And second i would like to point out that the query is open for sql injection attack (something you need to consider - if you are going to use it to production code).Third, what do you have in the bind method? What data source its trying to bind and with what control? From the sample code itself, it looks like no data is being returned from db.

Updated with: And by the way, should the colon be there in your query? See for instance the colon after first name ([First Name:] ) String query = "update employeeDB set [First Name:]='" + textName.Text + "', [Last Name:]='" + textadd.Text + "', [Email:]='" + textc.Text + "' where id='" + lblID + 1 + "'";

Improve this answer

7 Comments

I added in my bind method to the original post. And I guess its a good thing that this isn't production code. I'll also try the method @Tyche Infotech showed and see if it makes a difference.
Also, I think it's worth mentioning that deleting worked just fine, so it is definitely hitting my target DB.
By the way, should the colon be there in your query? See for instance the colon after first name ([First Name:] ) String query = "update employeeDB set [First Name:]='" + textName.Text + "', [Last Name:]='" + textadd.Text + "', [Email:]='" + textc.Text + "' where id='" + lblID + 1 + "'";
Yes, they are apart of the column names in the DB itself.
I see. Well, did you tried using profile from db side - just to see what was sent to the database?
|

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.