populating sql database from gridview checkbox

Question

I am trying to add a checkbox to the end of the gridview section that once checked it updates the sql database with a "1" or "0". Yes the it is done in bit with column name posFill.

Here is code....

protected void gvPerson_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ToString()))
        {

            SqlCommand cmd = new SqlCommand();


            cmd.Connection = conn;


            cmd.CommandText = "UPDATE requests SET date = @date, submitted_by = @submitted_by, position = @position, district = @district, base_store = @base_store, travel_to = @travel_to, open_til6 = @open_til6, email_add = @email_add, comments = @comments, posFill = @posFill, interviewDate = @interviewDate  WHERE _id = @_id";


            cmd.CommandType = CommandType.Text;

            // Get the PersonID of the selected row.
            string strID = gvPerson.Rows[e.RowIndex].Cells[2].Text;
            string strPosition = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox1")).Text;
            string strEmail_add = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox2")).Text;
            string strDate = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox3")).Text;
            string strSubBy = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox4")).Text;
            string strDist = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox5")).Text;
            string strBase = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox6")).Text;
            string strTravel = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox7")).Text;
            string strOpen = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox8")).Text;
            string strComments = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox9")).Text;
            //string strFilled = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox10")).Text;
            string strIntDate = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox11")).Text;
            string strLblFilled = ((Label)gvPerson.Rows[e.RowIndex].FindControl("lblFilled")).Text;

            // Append the parameters.
            cmd.Parameters.Add("@_id", SqlDbType.Int).Value = strID;
            cmd.Parameters.Add("@position", SqlDbType.NVarChar, 50).Value = strPosition;
            cmd.Parameters.Add("@email_add", SqlDbType.NVarChar, 50).Value = strEmail_add;
            cmd.Parameters.Add("@date", SqlDbType.Date).Value = strDate;
            cmd.Parameters.Add("@submitted_by", SqlDbType.NVarChar, 50).Value = strSubBy;
            cmd.Parameters.Add("@district", SqlDbType.NVarChar, 50).Value = strDist;
            cmd.Parameters.Add("@base_store", SqlDbType.NVarChar, 50).Value = strBase;
            cmd.Parameters.Add("@travel_to", SqlDbType.NVarChar, 50).Value = strTravel;
            cmd.Parameters.Add("@open_til6", SqlDbType.NVarChar, 50).Value = strOpen;
            cmd.Parameters.Add("@comments", SqlDbType.NVarChar, 50).Value = strComments;
            //cmd.Parameters.Add("@posFilled", SqlDbType.NVarChar, 50).Value = strFilled;
            cmd.Parameters.Add("@interviewDate", SqlDbType.Date).Value = strIntDate;
            cmd.Parameters.Add("@posFill", SqlDbType.Bit).Value = strLblFilled;

            // Open the connection.
            conn.Open();

            // Execute the command.
            cmd.ExecuteNonQuery();
        }

        // Exit edit mode.
        gvPerson.EditIndex = -1;

        // Rebind the GridView control to show data after updating.
        BindGridView();

        // Show the Add button.
        lbtnAdd.Visible = true;
    }

Here is the function for the checkbox...

protected void posFilled_CheckChanged(object sender, System.EventArgs e)
    {
        if (chkFilled.Checked == true)
        { lblFilled.Text = "1"; }
        else
        { lblFilled.Text = "0"; }


    }

the checkbox is on the gridview and functioning but here is the code for it too.

<asp:TemplateField HeaderText="Filled" SortExpression="posFill">
                <EditItemTemplate>
                    <asp:CheckBox ID="chkFilled" runat="server" AutoPostBack="true" />
                    <asp:Label ID="lblFilled" runat="server" Visible="true"></asp:Label>
                    <%--<asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("posFilled") %>'></asp:TextBox>--%>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="chkFilled2" runat="server"/>
                    <asp:Label ID="lblFilled" runat="server" Visible="true"></asp:Label>
                    <%--<asp:Label ID="Label10" runat="server" Text='<%# Bind("posFilled") %>'></asp:Label>--%>
                </ItemTemplate>

thank you in advance. i added a label to see what the response would be or if it even responded and if it did then just use label contents. but would rather not.

Is there a question? Please clearly state what it is you are asking — Pseudonym
– Pseudonym, Commented Apr 1, 2014 at 16:16
I am trying to add a checkbox to the end of the gridview section that once checked it updates the sql database with a "1" or "0". Yes the it is done in bit with column name posFill. — bpaz23
– bpaz23, Commented Apr 1, 2014 at 18:00

G3n1us · Accepted Answer · 2014-04-01 20:50:03Z

1

Instead of using "1".ToString() or "0".ToString(), why don't you just use the control itself? You don't need the label.

    cmd.Parameters.Add("@posFill", SqlDbType.Bit).Value = chkFilled.Checked;

EDIT:

 cmd.Parameters.Add("@posFill", SqlDbType.Bit).Value = ((CheckBox)gvPerson.Rows[e.RowIndex].FindControl("chkFilled")).Checked;

EDIT2:

You only have one int column. You should do this to make sure you're putting in a value that matches the datatype of the parameter:

 int tempInt = -1;

 if (int.TryParse(strID, out tempInt))
 {
      cmd.Parameters.Add("@_id", SqlDbType.Int).Value = tempInt;

      cmd.ExecuteNonquery();
 }

In fact, you should validate all of your data before you attempt to execute the command. You should be assigning all of your parameters the correct data types. If you're passing a SqlDbType.Date, you should pass it a DateTime variable and it should actually parse out as a DateTime.

edited Apr 1, 2014 at 20:50

answered Apr 1, 2014 at 17:11

G3n1us

261 silver badge4 bronze badges

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

7 Comments

bpaz23 Over a year ago

thank you for your response. I have tried that but for some ungodly reason whenever i put that in there im getting chkFilled.Checked does not exist in current context.

G3n1us Over a year ago

I've edited my response. That should now give you the CheckBox you need within that specific row. Let me know if that works for you. If you can find your label and get its text, then you should be able to find your CheckBox and get whether or not it's checked. =)

bpaz23 Over a year ago

I believe this is working BUT.... when i submit the update it is kicking out a cant convert string to int32 error.

G3n1us Over a year ago

You only have one int column. You should do this to make sure you're putting in a value that matches the datatype of the parameter: int tempInt = -1; if (int.TryParse(strID, out tempInt)) { command.ExecuteNonquery(); } In fact, you should validate all of your data before you attempt to execute the command.

bpaz23 Over a year ago

thats what im not understanding because up until i added the checkbox and the line that you suggested everything worked flawlessly. im wondering if its tied to the "bit" in the database for posFill. and when the checkbox is checked it just does true or false. correct?

|

Collectives™ on Stack Overflow

populating sql database from gridview checkbox

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related