Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
I want to update data into database from textbox control with dropdownlist selected value using stored procedure.

Can any one give solution?

What I have tried:

[Web Design]

<table align="center">
                <tr>
                    <td>Id</td>
                    <td><asp:DropDownList ID="ddlId" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlId_SelectedIndexChanged" /></td>
                </tr>
              <tr>
                  <td>Name</td>
                  <td><asp:TextBox ID="txtName" runat="server" /></td>
              </tr>
                <tr>
                    <td>Mobile</td>
                    <td><asp:TextBox ID="txtMobile" runat="server" /></td>
                </tr>
                <tr>
                    <td>Email</td>
                    <td><asp:TextBox ID="txtEmail" runat="server" /></td>
                </tr>
                <tr>
                    <td align="right"><asp:Button ID="btnUpdate" runat="server" Text="Update" Width="70" OnClick="btnUpdate_Click" /></td>
                    <td><asp:Button ID="btnDelete" runat="server" Text="Delete" Width="70" OnClick="btnDelete_Click" /></td>
                </tr>
            </table>


[Source Code]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace ASPNETMVC
{
    public partial class test1 : System.Web.UI.Page
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader dr;
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                con = new SqlConnection("Data Source=.\\SQLEXPRESS;Database=sample3;Integrated Security=true");
                cmd = new SqlCommand("select * from test", con);
                con.Open();
                dr = cmd.ExecuteReader();
                ddlId.DataSource = dr;
                ddlId.DataTextField = "Id";
                ddlId.DataValueField = "Id";
                ddlId.DataBind();
                ddlId.Items.Insert(0, "---Select---");
                con.Close();
            }
        }

        protected void ddlId_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(ddlId.SelectedIndex>0)
            {
               
                cmd.CommandText = "select * from test where Id="+ddlId.SelectedValue;
                cmd.CommandType = CommandType.Text;
                con.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    txtName.Text = dr["Name"].ToString();
                    txtMobile.Text = dr["Mobile"].ToString();
                    txtEmail.Text = dr["Email"].ToString();
                }
                con.Close();
            }
            else
            {
                txtName.Text = txtMobile.Text = txtEmail.Text = "";
                ddlId.SelectedIndex = 0;
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd.CommandText = "TestUpdate_Delete";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
            cmd.Parameters.AddWithValue("@Id", ddlId.SelectedValue);
            int count = cmd.ExecuteNonQuery();
            if (count > 0)
                Response.Write("Data Updated Successfully");
            con.Close();
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd.CommandText = "TestUpdate_Delete";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Id", ddlId.SelectedValue);
            int count = cmd.ExecuteNonQuery();
            if (count > 0)
                Response.Write("<script>alert('Are you sure of deleting this record!</Script>");
            con.Close();
        }
    }
}
Posted
Updated 7-Aug-19 7:03am
Comments
Richard MacCutchan 7-Aug-19 10:17am
   
You need to get the values from the textbox and dropdown, and pass them to the SP. What is the problem?
Member 14534831 7-Aug-19 10:24am
   
I want to update data into database .
ex: dropdownlist selected value -> 1
data will disply into textbox like this
ID - 1( ddlId.selectedValue)
Name - Nag
Mobile - 9876543213
Email - nag135@gmail.com
Update(btn) Delete (btn)
when I click update button for dropdownlist selected value , data will update and store in database
In the same way when I click delete button for dropdownlist selected value , data will delete in database

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

First off, you should never hard code your database connection string. Instead define your connection string in the web.config and reference the value from there. See: Creating a Connection String and Working with SQL Server LocalDB | Microsoft Docs[^]

For example:
<connectionStrings>
  <add name="MyDBConnectionString" connectionString="Data Source=.\\SQLEXPRESS;Database=sample3;Integrated Security=true" providerName="System.Data.SqlClient" />
</connectionStrings>


You can then reference your Connection String value via ConfigurationManager class like this:

string dbConnectionString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


Second, I've seen that you have appended the value from your DropDownList directly to your SQL query which can potentially leads you to SQL Injection. You should never append the values directly to your SQL query even if the value came from a DropDownList. Instead, use SQL paramterize query always. See: Protect Your Data: Prevent SQL Injection[^]

Here's an example:

private void PopulateForm(int id){
	using (SqlConnection connection = new SqlConnection(GetDBConnectionString())) {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM test WHERE Id= @Id", connection)) {
                        connection.Open();
 			cmd.CommandType = CommandType.Text;
            		cmd.Parameters.AddWithValue("@Id", id);
			SqlDataReader rdr = cmd.ExecuteReader();

        		while (rdr.Read())
        		{
            			txtName.Text = dr["Name"].ToString();
                    		txtMobile.Text = dr["Mobile"].ToString();
                    		txtEmail.Text = dr["Email"].ToString();
        		}
                }
 	}
}

protected void ddlId_SelectedIndexChanged(object sender, EventArgs e){
	PopulateForm(Convert.ToInt32(ddlId.SelectedValue));
}


Third, you should initialize your SQL connection when doing a database operation such as Update and Delete. In your code, it seems like you were reusing/referencing the variable con directly within your Update and Delete button event which that event will never be triggered, and your SQL connection object is no longer being initialized.

Fourth, make it a habit to put objects that eat resources such as SqlConnection, SqlCommand within a using block to ensure that objects will be properly disposed and after they are used.

Finally, I would recommend you to separate your code/logic for updating /deleting data to database and keep them out from your Button click event for the ease of maintenance and separation of concerns.

Your Update and Delete code should now be something like this:

private string GetDBConnectionString(){
 return ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
}


private void UpdateRecord(int id, string name, string email, string mobile){

	using (SqlConnection connection = new SqlConnection(GetDBConnectionString())) {
                using (SqlCommand cmd = new SqlCommand("YourUpdateStoredProcedureName", connection)) {

                        connection.Open();
 			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.AddWithValue("@Name", name);
            		cmd.Parameters.AddWithValue("@Mobile", mobile);
            		cmd.Parameters.AddWithValue("@Email", email);
            		cmd.Parameters.AddWithValue("@Id", id);
			cmd.ExecuteNonQuery();
                }
 	}
}

private void DeleteRecord(int id){
	using (SqlConnection connection = new SqlConnection(GetDBConnectionString())) {
                using (SqlCommand cmd = new SqlCommand("YourDeleteStoredProcedureName", connection)) {
                        connection.Open();
 			cmd.CommandType = CommandType.StoredProcedure;
            		cmd.Parameters.AddWithValue("@Id", id);
			cmd.ExecuteNonQuery();
                }
 	}
}


You can then call those methods something like this:

protected void btnUpdate_Click(object sender, EventArgs e){
        UpdateRecord(Convert.ToInt32(ddlId.SelectedValue), txtName.Text, txtEmail.Text, txtMobile.Text);
}

protected void btnUpdate_Click(object sender, EventArgs e){
        DeleteRecord(Convert.ToInt32(ddlId.SelectedValue));
}
   
v3
Comments
Member 14534831 8-Aug-19 12:03pm
   
I was tried your code , but eventhough I got error within dropdownlist event handler.

will you upload code inside ddlId_SelectedIndexChanged event handler
Vincent Maverick Durano 8-Aug-19 12:49pm
   
I've updated the solution. See the PopulateForm() method from the solution.
Member 14534831 8-Aug-19 13:29pm
   
I was tried populateform code, but it only displays ID's in dropdownlist, will not display selected data into textboxes(Name,Mobile,Email)
Vincent Maverick Durano 8-Aug-19 17:42pm
   
You have to debug your code. Set a a breakpoint at SelectedIndexChanaged and step into your code to figure out what went wrong. Also have you set AutoPostback to True for your DropDownList?
Member 14534831 11-Aug-19 8:40am
   
I was tried but failed to get data.

I am facing problem in another program will you give solution -

[Web Design]


Enter Student Name <asp:textbox id="txtName" runat="server"> Enter Student Number <asp:textbox id="txtNumber" runat="server"> Enter Student Percentage <asp:textbox id="txtPercentage" runat="server"> <asp:button id="btnSave" runat="server" text="Save" onclick="btnSave_Click">
<asp:dropdownlist id="ddlSNum" runat="server" autopostback="true" onselectedindexchanged="ddlSNum_SelectedIndexChanged">
<asp:listitem>---Select SNum ---

<asp:gridview id="gvTest" runat="server">

-----

[Source Code]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Test
{
public partial class SystemTest1 : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection(GetDBConnectionString());
cmd = new SqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "Select S_Number from S_Test";
dr = cmd.ExecuteReader();
ddlSNum.DataSource = dr;
ddlSNum.DataTextField = "S_Number";
ddlSNum.DataValueField = "S_Number";
ddlSNum.DataBind();
ddlSNum.Items.Insert(0, "--- Select SNum ---");
con.Close();
}
private string GetDBConnectionString()
{
return ConfigurationManager.ConnectionStrings["SagarDBCS"].ConnectionString;
}


protected void btnSave_Click(object sender, EventArgs e)
{
cmd.CommandText = $"Insert into S_Test(S_Name,S_Number,S_Percentage) values('{txtName.Text}',{txtNumber.Text},{txtPercentage.Text})";
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
Response.Write("Data Inserted Succesfully");
txtName.Text = txtNumber.Text = txtPercentage.Text = "";
con.Close();
}
private void DropDownData(int S_Number)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SagarDBCS"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("Select * from S_Test where S_Number=" + ddlSNum.SelectedValue, con))
{
con.Open();
dr = cmd.ExecuteReader();
if(dr.Read())
{
txtName.Text = dr["S_Name"].ToString();
txtNumber.Text = dr["S_Number"].ToString();
txtPercentage.Text = dr["S_Percentage"].ToString();
}
con.Close();
}
}
}

protected void ddlSNum_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownData(Convert.ToInt32(ddlSNum.SelectedValue));
gvTest.DataSource = dr;
gvTest.DataBind();
}
}
}
Vincent Maverick Durano 12-Aug-19 12:04pm
   
Seems like you haven't tried what i've suggested. We're not here to spoon feed someone. You have to understand the suggested solution first and then apply it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100