davidxc davidxc - 1 month ago 6
SQL Question

Why won't my simple C# website UPDATE to db using GridView?

I have the following C# to UPDATE a record, however the textbox shows, but doesn't update to the database. Likewise, I cannot ADD a record either.

private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}


Add:

protected void AddNewMainPost(object sender, EventArgs e)
{
string postID = ((TextBox)GridView1.FooterRow.FindControl("txtPostID")).Text;
string Name = ((TextBox)GridView1.FooterRow.FindControl("txtSelect")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into homepageSelection(postID, selectionText) " +
"values(@postID, @selectionText,);" +
"select postID,selectionText, from homepageSelection";
cmd.Parameters.Add("@postID", SqlDbType.VarChar).Value = postID;
cmd.Parameters.Add("@selectionText", SqlDbType.VarChar).Value = Name;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}


Update

protected void UpdateMainPost(object sender, GridViewUpdateEventArgs e)
{
string postID = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblpostID")).Text;
string Name = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSelec")).Text;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update homepageSelection set selectionText=@selectionText, " +
"where postID=@postID;" +
"select postID,selectionText from homepageSelection";
cmd.Parameters.Add("@postID", SqlDbType.VarChar).Value = postID;
cmd.Parameters.Add("@selectionText", SqlDbType.VarChar).Value = Name;
GridView1.EditIndex = -1;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}


I have two fields in the database:

Table: homepageSelection Fields: postID and selectionText

Answer

As I can see from your code above, you have a syntax error in both queries, but most important thing is the fact that you don't associate your command to the connection. Thus, unless you recreate the connection inside the GetData method, your command cannot be executed.

So, to fix the syntax errors

"select postID,selectionText from homepageSelection";
                           ^^^ comma not valid here

cmd.CommandText = @"update homepageSelection set 
                    selectionText=@selectionText" +
                                               ^^^^  again comma not valid here

cmd.CommandText = "insert into homepageSelection(postID, selectionText) " +
                  "values(@postID, @selectionText);" +
                                                 ^^^ no comma here

EDIT: it seems that you create the connection inside the GetData method, thus you don't need it in the two calling methods.