Blog

how to insert,update and delete radio button values in db with stored procedure [Answered]RSS

6 replies

Last post Oct 12, 2016 12:42 PM by Brando ZWZ

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

- Reply
Shuklaji123
Member

82 Points

187 Posts

how to insert,update and delete radio button values in db with stored procedure

Sep 03, 2016 01:17 AM|Shuklaji123|LINK

hi there I have prepared an insert,update and delete stored procedure and want to use it in inserting, updating and deleting the radio button values in the database using c# in .Net? Can you please help me solve this scenario? Thanks in Advance!

Shuklaji123

Brando ZWZ

Star

8291 Points

2490 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Sep 03, 2016 07:09 AM|Brando ZWZ|LINK

Hi Shuklaji123,

Shuklaji123
hi there I have prepared an insert,update and delete stored procedure and want to use it in inserting, updating and deleting the radio button values in the database using c# in .Net? Can you please help me solve this scenario? Thanks in Advance!

According to your description, I suggest you could use ADO.NET's SqlCommand object to achieve using stored procedure CRUD in asp.net.

Like below:

//Cat_CRUD is SP name
SqlCommand cmd = new SqlCommand("Cat_CRUD")
// set type is StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
//Use cmd.ExecuteNonQuery or other method
...

More details, you could refer to follow sample codes:

Database table:

Table name: Cat

Column DataType:

ID int

Name nchar(10)

My test demo SP:

CREATE PROCEDURE [dbo].[Cat_CRUD]
      @Action VARCHAR(10)
      ,@Id INT = NULL
      ,@Name VARCHAR(100) = NULL
AS
BEGIN
      SET NOCOUNT ON;
      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT *
            FROM Cat
      END
 
      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Cat(Name)
            VALUES (@Name)
      END
 
      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE Cat
            SET Name = @Name
            WHERE ID = @Id
      END
 
      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM Cat
            WHERE ID = @Id
      END
END

Aspx page:

        <div>
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            <br />
            <hr />
            RedioButton:
        <asp:RadioButtonList ID="RadioButtonList1" runat="server"></asp:RadioButtonList>
            <hr />
            Updata Value:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <br />
            <asp:Button ID="Insert" runat="server" Text="Insert" OnClick="Insert_Click" />
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="Update" runat="server" Text="Update" OnClick="Update_Click" />
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="Delete" runat="server" Text="Delete" OnClick="Delete_Click" />
            <br />
        </div>

Code-behind:

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

        //select data
        private void BindDataControl()
        {
            string name = TextBox1.Text;
            string constr = ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Cat_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "SELECT");
                    cmd.Connection = con;
                    SqlDataAdapter ada = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    ada.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    RadioButtonList1.DataSource = dt;
                    RadioButtonList1.DataTextField = "ID";
                    RadioButtonList1.DataValueField = "ID";
                    RadioButtonList1.DataBind();
                }
            }
        }

        //insert data
        protected void Insert_Click(object sender, EventArgs e)
        {
            string name = TextBox1.Text;
            string constr = ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Cat_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "INSERT");
                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    BindDataControl();
                }
            }
        }

        //update data
        protected void Update_Click(object sender, EventArgs e)
        {
            string name = TextBox1.Text;
            string constr = ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Cat_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "UPDATE");
                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Parameters.AddWithValue("@Id", RadioButtonList1.SelectedValue);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    BindDataControl();
                }
            }
        }

        //delete data
        protected void Delete_Click(object sender, EventArgs e)
        {
            string name = TextBox1.Text;
            string constr = ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Cat_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "DELETE");
                    cmd.Parameters.AddWithValue("@Id", RadioButtonList1.SelectedValue);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    BindDataControl();
                }
            }
        }

Best Regards,

Brando

- Reply
Shuklaji123
Member

82 Points

187 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Sep 21, 2016 05:59 PM|Shuklaji123|LINK

Thanks a lot ! Will try this and let u know!

Shuklaji123
- Reply
Shuklaji123
Member

82 Points

187 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Oct 05, 2016 11:20 AM|Shuklaji123|LINK

I want to use Radio button inside GridView and perform all 4 operations (Insert, Update, Delete and Select) through stored procedure. Brando, You have misunderstood my question. Please give me the solution...

Shuklaji123
- Reply
Brando ZWZ
Star

8291 Points

2490 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Oct 07, 2016 04:18 AM|Brando ZWZ|LINK

Hi Shuklaji123,

Shuklaji123
I want to use Radio button inside GridView and perform all 4 operations (Insert, Update, Delete and Select) through stored procedure. Brando, You have misunderstood my question. Please give me the solution...

Could you please explain more about your requirement?

Do you mean you want each row has a rediobuttonlist to CRUD the row's data?

Could you please post some relevant codes?

If you could post more relevant codes, it will be more easily for us to reproduce your problem and find the solution.

Best Regards,

Brando
- Reply
Shuklaji123
Member

82 Points

187 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Oct 07, 2016 12:51 PM|Shuklaji123|LINK

Yes, now you got me right . I did your example, but it is not meeting my criteria... I want to say that, in a gridview, if there are 4 fields , say Name, Gender,Education and designation,so Gender is inserted/updated through radiobutton, Name is simply textbox, Education is again textbox for now and so is designation. And this is to be done through stored procedure. I hope, now you have understood the question...Looking for your quick reply. Thanks

Shuklaji123

Brando ZWZ

Star

8291 Points

2490 Posts

Re: how to insert,update and delete radio button values in db with stored procedure

Oct 12, 2016 12:42 PM|Brando ZWZ|LINK

Hi Shuklaji123,

Shuklaji123
I want to say that, in a gridview, if there are 4 fields , say Name, Gender,Education and designation,so Gender is inserted/updated through radiobutton, Name is simply textbox, Education is again textbox for now and so is designation. And this is to be done through stored procedure. I hope, now you have understood the question...Looking for your quick reply. Thanks

According to your description, I write a test demo.

More details about my test demo, you could refer to follow codes:

SP:

CREATE PROCEDURE [dbo].[Customers_CRUD]
      @Action VARCHAR(10)
      ,@CustomerId INT = NULL
      ,@Name VARCHAR(100) = NULL
      ,@Country VARCHAR(100) = NULL
AS
BEGIN
      SET NOCOUNT ON;
 
      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT CustomerId, Name, Country
            FROM Customers
      END
 
      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Customers(Name, Country)
            VALUES (@Name, @Country)
      END
 
      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE Customers
            SET Name = @Name, Country = @Country
            WHERE CustomerId = @CustomerId
      END
 
      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM Customers
            WHERE CustomerId = @CustomerId
      END
END

Html:

  <div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="CustomerId"
OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting" EmptyDataText="No records has been added.">
<Columns>
    <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
        <ItemTemplate>
            <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
        </EditItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
        <ItemTemplate>
            <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:RadioButtonList ID="RadioButtonList1" runat="server">
                <asp:ListItem Value="US" Text="US"></asp:ListItem>
                 <asp:ListItem Value="UK" Text="UK"></asp:ListItem>
            </asp:RadioButtonList>
        </EditItemTemplate>
    </asp:TemplateField>
    <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150"/>
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
    <td style="width: 150px">
        Name:<br />
        <asp:TextBox ID="txtName" runat="server" Width="140" />
    </td>
    <td style="width: 150px">
        Country:<br />
        <asp:TextBox ID="txtCountry" runat="server" Width="140" />
    </td>
    <td style="width: 100px">
        <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
    </td>
</tr>
</table>
    </div>

Code-behind:

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.BindGrid();
            }
        }
        private void BindGrid()
        {
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Customers_CRUD"))
                {
                    cmd.Parameters.AddWithValue("@Action", "SELECT");
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }

        protected void Insert(object sender, EventArgs e)
        {
            string name = txtName.Text;
            string country = txtCountry.Text;
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Customers_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "INSERT");
                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Parameters.AddWithValue("@Country", country);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            this.BindGrid();
        }

        protected void OnRowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            this.BindGrid();
        }

        protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            GridViewRow row = GridView1.Rows[e.RowIndex];
            int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
            string name = (row.FindControl("txtName") as TextBox).Text;
            System.Web.UI.WebControls.RadioButtonList r1 = (System.Web.UI.WebControls.RadioButtonList)row.FindControl("RadioButtonList1");
            string country = r1.SelectedValue;
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Customers_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "UPDATE");
                    cmd.Parameters.AddWithValue("@CustomerId", customerId);
                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Parameters.AddWithValue("@Country", country);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            GridView1.EditIndex = -1;
            this.BindGrid();
        }

        protected void OnRowCancelingEdit(object sender, EventArgs e)
        {
            GridView1.EditIndex = -1;
            this.BindGrid();
        }

        protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Customers_CRUD"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Action", "DELETE");
                    cmd.Parameters.AddWithValue("@CustomerId", customerId);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            this.BindGrid();
        }

        protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != GridView1.EditIndex)
            {
                (e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
            }
        }

Best Regards,

Brando

‹ Previous Thread|Next Thread ›