Question 220757 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Update database when an checkbox or button clicks

I have a seperate column in my gridview and also I have added an checkbox/button. So when an user checks or clicks the checkbox or button it should update my oracle database. When an user checks the checkbox it should take invoices value which is present in the second column and based on that invoice numbers my database should get updated with value 1 for that particular invoice rows

I have added this function. Here I am using button feature.checkbox or button anyone is fine. Here is what I have implemented:

protected void GridViewRemittance_RowCommand(object sender, GridViewCommandEventArgs e) {
  if (e.CommandName == "UpdateRow") {
    ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + e.CommandArgument + "');", true);
    string invoiceID = Convert.ToString(e.CommandArgument);
    UpdateDatabase(invoiceID);
  }
}

protected void UpdateDatabase(string i) {
  string invoiceNumber = i;
  using (OracleConnection Conn = new OracleConnection(strCon)) {
    using (OracleCommand cmd = new OracleCommand()) {
      Conn.Open(); cmd.Connection = Conn;
      cmd.CommandText = "Update ap_lineitem_staging SET selected_invoice = 1 where pre_invoice_num='" + invoiceNumber + "'";
      cmd.ExecuteNonQuery();
    }
  }
}
Tags:

2

Answers


  1. 0 
    protected void GridViewRemittance_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "UpdateRow")
    {
        ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + e.CommandArgument + "');", true);
        string invoiceID = Convert.ToString(e.CommandArgument);
        UpdateDatabase(invoiceID);
    }
}

protected void UpdateDatabase(string i)
{
    string invoiceNumber = i;
    string connectionString = "YOUR_ORACLE_CONNECTION_STRING_HERE";

    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        conn.Open();
        using (OracleCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "UPDATE ap_lineitem_staging SET selected_invoice = 1 WHERE pre_invoice_num = :invoiceNumber";

            // Add parameter to the query to avoid SQL injection
            cmd.Parameters.Add("invoiceNumber", OracleDbType.Varchar2).Value = invoiceNumber;

            cmd.ExecuteNonQuery();
        }
    }
}

    try this

    Login or Signup to reply.
  2. 0

    I suggest you simply add to the GridView a check box control.

    So, in this example, we want to set the Hotel as "Active". (We thus assume we have a column in the database called Active.

    So, this GridView markup:

    <asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
    DataKeyNames="ID" CssClass="table" 
    Width="45%">
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
        <asp:BoundField DataField="Description" HeaderText="Description" />
        <asp:TemplateField HeaderText="Active" ItemStyle-HorizontalAlign="Center">
            <ItemTemplate>
                <asp:CheckBox ID="chkActive" runat="server"
                    Checked='<%# Eval("Active") %>'
                    />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

    And code behind:

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

    void LoadGrid()
    {
        string strSQL = @"SELECT * FROM tblHotelsA
                        ORDER BY HotelName";
        GVHotels.DataSource = General.MyRst(strSQL);
        GVHotels.DataBind();
    }

    And we now have this:

    enter image description here

    So, now let’s add an event for the check box in above.

    Since we can’t bring up the property sheet for the check box (since it is nested inside of the GridView), then we simply add the event to the check box by using markup.

    So, then this:

    enter image description here

    So, we now have an event code stub for the check box.

    Thus, the code behind for the check box then becomes this:

        protected void chkActive_CheckedChanged(object sender, EventArgs e)
    {
        CheckBox chkActive = (CheckBox)sender;
        GridViewRow gRow = (GridViewRow)chkActive.NamingContainer;

        int DataPK = (int)GVHotels.DataKeys[gRow.RowIndex]["ID"];

        SqlCommand cmdSQL = 
            new SqlCommand(@"UPDATE tblHotelsA SET Active = @Active
                             WHERE ID = @ID" );
        cmdSQL.Parameters.Add("@Active", SqlDbType.Bit).Value = chkActive.Checked;
        cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = DataPK;
        General.MyRstE(cmdSQL);

    }

    And for completeness, I’ll include my 2 "helper" routines that are global to the application. They are:

        public static DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();


        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                cmdSQL.Connection.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }


    public static void MyRstE(SqlCommand cmdSQL)
    {

        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                conn.Open();
                cmdSQL.ExecuteNonQuery();
            }
        }
    }

    In above, I used the DataKeys[] feature of the GridView, as that allows you to use the database primary key value, but does not require you to include or show the database PK value in the GridView.

    You can of course use the invoice number in the above SQL update, but in most cases, when possible, I do suggest using the DataKeys feature along with using the database primary key for the update.

    In above, I used SQL server syntax and not Oracle syntax, but the over all approach and concept is the same.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search