How to update data in texbox control by dropdownlist selected value using stored procedure ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

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 7-Aug-19 4:06am

Member 14534831

Updated 7-Aug-19 7:03am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vincent Maverick Durano · Answer 1 · 2019-08-07T07:03:00

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:

HTML

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

C#

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:

C#

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:

C#

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:

C#

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));
}