locked
Update and delete data from dropdownlist RRS feed

  • Question

  • User-2094959909 posted
    Hello everyone,
    I have a dropdownlist who get names of users from databasa so when one user had been selected i get all of his data under in texteboxes ( name, surname, username, password, role )
    So i want to add two buttons update and delete and yhe idea is when a user is selected i can delete this user or change a textbox and update the new data in database.

    Any thoughts please ?

    Thanks in advance.
    Thursday, July 19, 2018 7:49 AM

All replies

  • User-821857111 posted

    My recommendation is that you spend a little time familiarising yourself with the basics of ASP.NET. You should run through a CRUD-based tutorial like this one: https://docs.microsoft.com/en-us/aspnet/web-forms/overview/getting-started/getting-started-with-aspnet-45-web-forms/introduction-and-overview. It covers all the basic scenarios like the one you describe.

    Thursday, July 19, 2018 8:23 AM
  • User-330142929 posted

    Hi Omar27,

    According to your description, I have made a demo, wish it is useful to you.

    Aspx.

    <div>
    
                <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
    
                <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateRows="False" DataKeyNames="Id" DataSourceID="SqlDataSource2">
    
                    <Fields>
    
                        <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
    
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
    
                        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
    
                        <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True"/>
    
                    </Fields>
    
                </asp:DetailsView>
    
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues" ConnectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DataStore;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" DeleteCommand="DELETE FROM [Products] WHERE [Id] = @original_Id AND [Name] = @original_Name AND [Price] = @original_Price" InsertCommand="INSERT INTO [Products] ([Name], [Price]) VALUES (@Name, @Price)" OldValuesParameterFormatString="original_{0}" ProviderName="System.Data.SqlClient" SelectCommand="SELECT [Id], [Name], [Price] FROM [Products] WHERE ([Id] = @Id)" UpdateCommand="UPDATE [Products] SET [Name] = @Name, [Price] = @Price WHERE [Id] = @original_Id AND [Name] = @original_Name AND [Price] = @original_Price">
    
                    <DeleteParameters>
    
                        <asp:Parameter Name="original_Id" Type="Int32" />
    
                        <asp:Parameter Name="original_Name" Type="String" />
    
                        <asp:Parameter Name="original_Price" Type="Int32" />
    
                    </DeleteParameters>
    
                    <InsertParameters>
    
                        <asp:Parameter Name="Name" Type="String" />
    
                        <asp:Parameter Name="Price" Type="Int32" />
    
                    </InsertParameters>
    
                    <SelectParameters>
    
                        <asp:ControlParameter ControlID="DropDownList1" Name="Id" PropertyName="SelectedValue" Type="Int32" />
    
                    </SelectParameters>
    
                    <UpdateParameters>
    
                        <asp:Parameter Name="Name" Type="String" />
    
                        <asp:Parameter Name="Price" Type="Int32" />
    
                        <asp:Parameter Name="original_Id" Type="Int32" />
    
                        <asp:Parameter Name="original_Name" Type="String" />
    
                        <asp:Parameter Name="original_Price" Type="Int32" />
    
                    </UpdateParameters>
    
                </asp:SqlDataSource>
    
     

    Code behind.

         protected void Page_Load(object sender, EventArgs e)
    
            {
    
                if (!IsPostBack)
    
                {
    
                    BindData();
    
                }
    
            }
    
            public void BindData()
    
            {
    
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DataStoreConnectionString"].ConnectionString);
    
                SqlCommand command = new SqlCommand();
    
                command.Connection = connection;
    
                command.CommandText = "select * from Products";
    
                SqlDataAdapter sda = new SqlDataAdapter(command);
    
                DataTable dt = new DataTable();
    
                sda.Fill(dt);
    
                this.DropDownList1.DataTextField = "Name";
    
                this.DropDownList1.DataValueField = "Id";
    
                this.DropDownList1.DataSource = dt;
    
                this.DropDownList1.DataBind();
    
            }

    How it works.

    Feel free to let me know if you have any questions.

    Best Regards

    Abraham

    Friday, July 20, 2018 8:21 AM
  • User-1171043462 posted

    Share your code and HTML

    Friday, July 20, 2018 8:23 AM
  • User-2094959909 posted

    mudassarkhan her's my code ===>

    HTML:

    <div>

    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList_SelectedIndexChanged" Height="17px" Width="249px">
    </asp:DropDownList>
    <br />
    <br />
    Name<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <br />
    <br />
    Surname<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
    <br />
    <br />
    Username<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
    <br />
    <br />
    Password<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
    <br />
    <br />
    Role<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Update" />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="Button2" runat="server" Text="delete" />
    </div>

    Behind code :

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
    DropDownList1.Items.Add(new ListItem("--Select--", ""));
    DropDownList1.AppendDataBoundItems = true;
    String strConnString = ConfigurationManager
    .ConnectionStrings["parallelConnectionString"].ConnectionString;
    String strQuery = "select name, surname from tblusers";
    MySqlConnection con = new MySqlConnection(strConnString);
    MySqlCommand cmd = new MySqlCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strQuery;
    cmd.Connection = con;
    try
    {
    con.Open();
    DropDownList1.DataSource = cmd.ExecuteReader();
    DropDownList1.DataTextField = "name";
    DropDownList1.DataValueField = "surname";
    DropDownList1.DataBind();
    }
    catch (Exception ex)
    {
    throw ex;
    }
    finally
    {
    con.Close();
    con.Dispose();
    }
    }
    }

    protected void DropDownList_SelectedIndexChanged(object sender, EventArgs e)
    {
    String strConnString = ConfigurationManager
    .ConnectionStrings["parallelConnectionString"].ConnectionString;
    string strQuery = "select name, surname, username, password, role from tblusers where name = @name";
    MySqlConnection con = new MySqlConnection(strConnString);
    MySqlCommand cmd = new MySqlCommand();
    cmd.Parameters.AddWithValue("@name", DropDownList1.SelectedItem.Text);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = strQuery;
    cmd.Connection = con;

    try
    {
    con.Open();
    MySqlDataReader sdr = cmd.ExecuteReader();
    if (sdr.HasRows) {
    while (sdr.Read())
    {
    TextBox1.Text = sdr[0].ToString();
    TextBox2.Text = sdr[1].ToString();
    TextBox3.Text = sdr[2].ToString();
    TextBox4.Text = sdr[3].ToString();
    TextBox5.Text = sdr[4].ToString();
    }
    }
    else
    {
    TextBox1.Text = "not found";
    }

    }
    finally
    {
    con.Close();
    con.Dispose();
    }

    }

    i added two buttons UPDATE and DELETE and hopefuly work .

    Friday, July 20, 2018 5:03 PM
  • User-1171043462 posted

    For Update and Delete you need Primary key like UserId.

    I don't see anything in your Table design.

    So how will you update?

    Sunday, July 22, 2018 9:57 AM
  • User-2094959909 posted

    For Update and Delete you need Primary key like UserId.

    for this i created a new table with userid as auto increment and primary key and i did this on the sqldatasource code :

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DBcon %>" ProviderName="<%$ ConnectionStrings:DBcon.ProviderName %>"
    SelectCommand="SELECT * FROM tblusers" UpdateCommand="UPDATE tblusers SET name=@name, surname=@surname, username=@username, password=@password, role=@role where userid=@userid" DeleteCommand="DELETE from tblusers where userid=@userid"></asp:SqlDataSource>
    <asp:GridView ID="GridView2" runat="server" CssClass="table table-striped table-bordered table-hover"
    AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource2">

    so when i execute the code it gave this message:

    Parameter '@userid' must be defined.

    Description : Une exception non gérée s'est produite au moment de l'exécution de la requête Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code. 

    Détails de l'exception: MySql.Data.MySqlClient.MySqlException: Parameter '@userid' must be defined.

    Sunday, July 22, 2018 10:27 AM
  • User-1171043462 posted

    From where UserId you will get?

    Sunday, July 22, 2018 10:37 AM
  • User-2094959909 posted

    i have in databse userid auto increment and in showing the users in gridview it even doesn't show the userid i don't know why 

    Sunday, July 22, 2018 10:43 AM
  • User-1171043462 posted

    Do this way. I have fetched the UserId from DropDownList and passed as Update and Delete Parameters

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DBcon %>" ProviderName="<%$ ConnectionStrings:DBcon.ProviderName %>"
    SelectCommand="SELECT * FROM tblusers" UpdateCommand="UPDATE tblusers SET name=@name, surname=@surname, username=@username, password=@password, role=@role where userid=@userid" DeleteCommand="DELETE from tblusers where userid=@userid">
    <UpdateParameters>
      <asp:ControlParameter Name="userid" ControlID="DropDownList1" PropertyName="SelectedValue" />
        </UpdateParameters>
    <DeleteParameters>
            <asp:ControlParameter Name="userid" ControlID="DropDownList1" PropertyName="SelectedValue" />
        </DeleteParameters>
    </asp:SqlDataSource>

    Sunday, July 22, 2018 10:44 AM
  • User-2094959909 posted

    i tried your solution and it gave me now this error

    Erreur du serveur dans l'application '/'.


    Impossible de trouver le contrôle 'DropDownList1' dans ControlParameter 'userid'.

    Description : Une exception non gérée s'est produite au moment de l'exécution de la requête Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code. 

    Détails de l'exception: System.InvalidOperationException: Impossible de trouver le contrôle 'DropDownList1' dans ControlParameter 'userid'.

    impossible to find control 'dropdownlist1' in controlparameter 'userid'

    Sunday, July 22, 2018 10:57 AM
  • User-1171043462 posted

    impossible to find control 'dropdownlist1' in controlparameter 'userid'

    You tell whether DropDownList1 is correct ID, if not put correct ID

    Also is it present on page?

    Sunday, July 22, 2018 11:04 AM
  • User-2094959909 posted

    here's all my code 

    <div> <!--ADD USER-->
    <div class="form-group row">
    <label class="col-sm-2 col-form-label">Name</label>
    <div class="col-sm-10">
    <asp:TextBox ID="TextBox1" class="form-control" placeholder="Name" runat="server"></asp:TextBox>
    </div>
    </div>
    <div class="form-group row">
    <label class="col-sm-2 col-form-label">Surname</label>
    <div class="col-sm-10">
    <asp:TextBox ID="TextBox2" class="form-control" placeholder="Surname" runat="server"></asp:TextBox>
    </div>
    </div>
    <div class="form-group row">
    <label class="col-sm-2 col-form-label">Username</label>
    <div class="col-sm-10">
    <asp:TextBox ID="TextBox3" class="form-control" placeholder="Username" runat="server"></asp:TextBox>
    </div>
    </div>
    <div class="form-group row">
    <label class="col-sm-2 col-form-label">Password</label>
    <div class="col-sm-10">
    <asp:TextBox ID="TextBox4" class="form-control" placeholder="Password" runat="server"></asp:TextBox>
    </div>
    </div>
    <div class="form-group row">
    <label class="col-sm-2 col-form-label">Role</label>
    <div class="col-sm-10">
    <asp:TextBox ID="TextBox5" class="form-control" placeholder="Admin / User" runat="server"></asp:TextBox>
    </div>
    </div>
    <form class="form-inline my-2 my-lg-0">
    <div class="col-sm-10">
    <asp:Button ID="Button2" runat="server" Text="Ajouter" OnClick="Button2_Click"/>
    <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
    </div>
    </form>
    </div>
    <div><!--SHOW ALL USERS-->
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBcon %>" ProviderName="<%$ ConnectionStrings:DBcon.ProviderName %>" SelectCommand="SELECT * FROM tblusers"></asp:SqlDataSource>
    <asp:GridView ID="GridView1" CssClass="table table-striped table-bordered table-hover" runat="server" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
    <Columns>
    <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
    <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
    <asp:BoundField DataField="surname" HeaderText="surname" SortExpression="surname" />
    <asp:BoundField DataField="username" HeaderText="username" SortExpression="username" />
    <asp:BoundField DataField="password" HeaderText="password" SortExpression="password" />
    <asp:BoundField DataField="role" HeaderText="role" SortExpression="role" />
    </Columns>
    </asp:GridView>
    </div>
    <div><!--UPDATE AND DELETE USERS-->
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DBcon %>" ProviderName="<%$ ConnectionStrings:DBcon.ProviderName %>"
    SelectCommand="SELECT * FROM tblusers" UpdateCommand="UPDATE tblusers SET name=@name, surname=@surname, username=@username, password=@password, role=@role where userid=@userid" DeleteCommand="DELETE from tblusers where userid=@userid">
    <UpdateParameters>
    <asp:ControlParameter Name="userid" ControlID="DropDownList1" PropertyName="SelectedValue" />
    </UpdateParameters>
    <DeleteParameters>
    <asp:ControlParameter Name="userid" ControlID="DropDownList1" PropertyName="SelectedValue" />
    </DeleteParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView2" runat="server" CssClass="table table-striped table-bordered table-hover"
    AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource2">
    <Columns>
    <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
    <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
    <asp:BoundField DataField="surname" HeaderText="surname" SortExpression="surname" />
    <asp:BoundField DataField="username" HeaderText="username" SortExpression="username" />
    <asp:BoundField DataField="password" HeaderText="password" SortExpression="password" />
    <asp:BoundField DataField="role" HeaderText="role" SortExpression="role" />
    <asp:CommandField ShowDeleteButton="True" />
    <asp:CommandField ShowEditButton="True" />
    </Columns>
    </asp:GridView>
    </div>

    i'm stuck

    Sunday, July 22, 2018 11:14 AM
  • User-1171043462 posted

    Where is the DropDownList? DropDownList1

    Sunday, July 22, 2018 12:00 PM
  • User-2094959909 posted

    i don't have one because now i only show my users in datagridview directly ... i don't have dropdownlist 

    what shoud i do instead of it in ControlID ??

    Sunday, July 22, 2018 12:10 PM
  • User-1171043462 posted

    So from where UserId will come? You need to decide and tell

    Sunday, July 22, 2018 12:20 PM
  • User-2094959909 posted

    i have in my database userid, name, surname, username, password and role (userid is primary key, auto increment and not null) 

    userid will be from database or i'm wrong ?

    Sunday, July 22, 2018 12:26 PM
  • User-1171043462 posted

    userid will be from database or i'm wrong ?

    You are wrong. That works for Insert. Not for Update and Delete.

    How will database know which record you want to delete?

    You will need to pass that in where clause.

    Sunday, July 22, 2018 12:31 PM
  • User-2094959909 posted

    what should i pass that in where clause when i need to delete or update whoever the user i want to select

    where clause is like ===> where userid="1"

    how would it works ?? for me if you can show me please ?

    Sunday, July 22, 2018 12:44 PM
  • User-1171043462 posted

    From where and how you will select?

    Sunday, July 22, 2018 12:45 PM
  • User-2094959909 posted

    select * from tblusers

    deletecommand = "delete from tblusers where userid = @userid"

    it said that @userid must be defined

    i don't know what is that means or what should i do to define it 

    Sunday, July 22, 2018 1:37 PM
  • User-1171043462 posted

    Refer this article and learn the basics please.

    It explains where to put Primary Key

    SqlDataSource CRUD: Select Insert Edit Update and Delete using SqlDataSource in ASP.Net

    Sunday, July 22, 2018 4:04 PM
  • User-1171043462 posted

    Try

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DBcon %>" ProviderName="<%$ ConnectionStrings:DBcon.ProviderName %>"
    SelectCommand="SELECT * FROM tblusers" UpdateCommand="UPDATE tblusers SET name=@name, surname=@surname, username=@username, password=@password, role=@role where userid=@userid" DeleteCommand="DELETE from tblusers where userid=@userid">
    <UpdateParameters>
            <asp:Parameter Name="userid" Type="Int32" />
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="surname" Type="String" />
    <asp:Parameter Name="username" Type="String" />
    <asp:Parameter Name="password" Type="String" />
    <asp:Parameter Name="role" Type="String" />
        </UpdateParameters>
        <DeleteParameters>
            <asp:Parameter Name="userid" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>

    Sunday, July 22, 2018 4:07 PM