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

Question

User-521826128 posted

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!

Answer 1

User283571144 posted

Hi 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

Answer 2

User-521826128 posted

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

Answer 3

User-521826128 posted

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...

Answer 4

User283571144 posted

Hi 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

Answer 5

User-521826128 posted

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

Answer 6

User283571144 posted

Hi 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