Using a checkbox input in Gridview and inserting is value in to SQL Server database

Question

I am trying to use a checkbox control in gridview. Basically the user would enter an Item Description, the Quantity of the Item used, and if it has already been "Sold" out of inventory. The checkbox when checked would indicate the item was sold. Unchecked it can be null or no. I can store the value in the database as 1/2, true/false, yes/no. It doesn't matter.

I have tried using bit type, int, and varchar in my database. And have tried multiple ways of getting the value for the check in C# and can't seem to get it to work.

I've searched all over and have found examples on how to use it to save or delete multiple rows. But I need it to actually store a value in my db.

Here's code I've scavenged from other posts and almost have something working.

public partial class WebForm1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            SetInitialRow();
        }
    }

    private void SetInitialRow()
    {
        DataTable dt = new DataTable();
        DataRow dr = null;

        dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));
        dt.Columns.Add(new DataColumn("Column1", typeof(string)));
        dt.Columns.Add(new DataColumn("Column2", typeof(string)));
        dt.Columns.Add(new DataColumn("Column3", typeof(string)));

        dr = dt.NewRow();

        dr["RowNumber"] = 1;
        dr["Column1"] = string.Empty;
        dr["Column2"] = string.Empty;
        dr["Column3"] = string.Empty;

        dt.Rows.Add(dr);

        ViewState["CurrentTable"] = dt;

        Gridview1.DataSource = dt;
        Gridview1.DataBind();
    }

    private void AddNewRowToGrid()
    {
        int rowIndex = 0;

        if (ViewState["CurrentTable"] != null)
        {
            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
            DataRow drCurrentRow = null;

            if (dtCurrentTable.Rows.Count > 0)
            {
                for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                {
                    TextBox itemDesc = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("txtItemDesc");
                    TextBox quantity = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("txtQuantity");
                    CheckBox sold = (CheckBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("alreadySold");
                    drCurrentRow = dtCurrentTable.NewRow();
                    drCurrentRow["RowNumber"] = i + 1;
                    dtCurrentTable.Rows[i - 1]["Column1"] = itemDesc.Text;
                    dtCurrentTable.Rows[i - 1]["Column2"] = quantity.Text;
                    dtCurrentTable.Rows[i - 1]["Column3"] = sold.Checked.ToString();
                 
                    rowIndex++;
                }

                dtCurrentTable.Rows.Add(drCurrentRow);

                ViewState["CurrentTable"] = dtCurrentTable;

                Gridview1.DataSource = dtCurrentTable;
                Gridview1.DataBind();
            }
        }
        else
        {
            Response.Write("ViewState is null");
        }

        // Set Previous Data on Postbacks
        SetPreviousData();
    }

    private void SetPreviousData()
    {
        int rowIndex = 0;

        if (ViewState["CurrentTable"] != null)
        {
            DataTable dt = (DataTable)ViewState["CurrentTable"];

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    TextBox itemDesc = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("txtItemDesc");
                    TextBox quantity = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("txtQuantity");
                    CheckBox sold = (CheckBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("alreadySold");

                    itemDesc.Text = dt.Rows[i]["Column1"].ToString();
                    quantity.Text = dt.Rows[i]["Column2"].ToString();
                    sold.Checked = dt.Rows[i]["Column3"].ToString().ToUpperInvariant() == "TRUE";

                    rowIndex++;
                }
            }
        }
    }

    protected void ButtonAdd_Click(object sender, EventArgs e)
    {
        AddNewRowToGrid();
    }

    protected void Save(object sender, System.EventArgs e)
    {
        if (ViewState["CurrentTable"] != null)
        {
            DataTable dt = new DataTable();
            dt = ViewState["CurrentTable"] as DataTable;

            if (dt.Rows.Count > 0)
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
                {
                    SqlCommand cmd = null;

                    foreach (GridViewRow row in Gridview1.Rows)
                    {
                        cmd = new SqlCommand("INSERT INTO SampleTest (ItemDesc, Quantity, Sold) VALUES (@Column1, @Column2, @Column3)", con);
                        string itemDesc = (row.FindControl("txtItemDesc") as TextBox).Text;
                        string quantity = (row.FindControl("txtQuantity") as TextBox).Text;
                        CheckBox sold = (row.FindControl("alreadySold") as CheckBox);

                        if (sold == null) 
                        {
                            sold.Checked = false;
                        }
                        else
                        {
                            sold.Checked = true;
                        }

                        cmd.Parameters.AddWithValue("@Column1", itemDesc);
                        cmd.Parameters.AddWithValue("@Column2", quantity);
                        cmd.Parameters.AddWithValue("@Column3", sold);

                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }

                    //cmd = new SqlCommand("SELECT Column1,Column2,Column3 FROM SampleTest", con);
                    //SqlDataAdapter da = new SqlDataAdapter(cmd);
                    //DataTable dt1 = new DataTable();
                    //da.Fill(dt1);
                    //this.gvSample.DataSource = dt1;
                    //this.gvSample.DataBind();
                }
            }
        }
    }
}

Answer 1

I'm an idiot. Thank you B.O.B. for explaining what was happening with the null value and the checkbox control. It really helped me understand what was happening. The reason the other things didn't work is because I had a type-o on the checkbox control on the HTML page. Once I fixed that everything works perfect.