Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Fot
    Fot is offline
    Regular Coder
    Join Date
    Mar 2011
    Posts
    111
    Thanks
    88
    Thanked 0 Times in 0 Posts

    Problem with delete command

    Hello, the delete command operate with this:
    PHP Code:
    DELETE FROM Catalogue WHERE [CodeLIKE 'examplevalue' 
    The above operate with one value-name of field in database.
    I want to delete according value from a dropdown list (id)but there is a problem(cannot see it).Thanks

    PHP 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.SqlClient;
    using System.Web.UI.HtmlControls;
    using System.Configuration;
    using System.Collections;

    namespace WebApplication21
    {
        public 
    partial class WebForm2 System.Web.UI.Page
        
    {
            protected 
    void Page_Load(object senderEventArgs e)
            {
                
    SqlConnection MyConn;
                
    SqlCommand MyCmd;
                
    SqlDataReader MyRdr;
                
    String MyString "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
                
    MyConn = new SqlConnection(MyString);
               
                if (!
    IsPostBack)
                {
                    
    MyConn.Open();

                    
    MyCmd = new SqlCommand("SELECT * FROM Catalogue"MyConn);
                    
    MyRdr MyCmd.ExecuteReader();
                    
    DropDownList1.DataSource MyRdr;
                    
    DropDownList1.DataValueField "Price";
                    
    DropDownList1.DataTextField "Code";
                    
    DropDownList1.DataBind();
                    
    MyRdr.Close();
                }  
            }
            protected 
    void rebind()
            {

            }

            protected 
    void DropDownList1_SelectedIndexChanged(object senderEventArgs e)
            {
                
    SqlConnection MyConn;
                
    SqlCommand MyCmd;
                
    SqlDataReader MyRdr;
                
    String MyString "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
                
    MyConn = new SqlConnection(MyString);
                
    MyCmd = new SqlCommand("SELECT * FROM Catalogue WHERE [Code] LIKE @Code"MyConn);
                
    MyCmd.Parameters.AddWithValue("@Code"DropDownList1.SelectedItem.Value);
                
    MyConn.Open();
                
    MyRdr MyCmd.ExecuteReader();
                while (
    MyRdr.Read())
                {
                    
    product_NamTB.Text = (string)MyRdr["Product_Name"];
                    
    pricTB.Text = (string)MyRdr["Price"];
                }
                
    MyRdr.Close();
                
    MyConn.Close();
            }

            protected 
    void Button1_Click(object senderEventArgs e)
            {
            
    SqlConnection MyConn;
            
    SqlCommand MyCmd;
            
    String MyString "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
            
    MyConn = new SqlConnection(MyString);
            
    MyCmd = new SqlCommand("INSERT INTO Catalogue (Product_Name, Code, Price) VALUES (@Product_Name, @Code, @Price)"MyConn);
    // update the command with the parameters from the text box’s
            
    MyCmd.Parameters.AddWithValue("@product_Name"product_NameTB.Text);
            
    MyCmd.Parameters.AddWithValue("@Code"CodeTB.Text);
            
    MyCmd.Parameters.AddWithValue("@Price"PriceTB.Text);
            
    MyConn.Open(); // open the connection
    // run the query, this method is used as it does not return a value
            
    MyCmd.ExecuteNonQuery(); 
            
    MyConn.Close(); // close the connection
           
    }

           protected 
    void Button2_Click(object senderEventArgs e)
           {
           
    SqlConnection MyConn;
           
    SqlCommand MyCmd;
           
    String MyString "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
           
    MyConn = new SqlConnection(MyString);
           
    MyCmd = new SqlCommand("UPDATE Catalogue SET [Product_Name] =@Product_Name, [Price] =@Price WHERE Code LIKE @Code"MyConn);
           
    MyCmd.Parameters.AddWithValue("@code"DropDownList1.SelectedItem.Value);
           
    MyCmd.Parameters.AddWithValue("@Product_Name"product_NamTB.Text);
           
    MyCmd.Parameters.AddWithValue("@Price"pricTB.Text);
           
    MyConn.Open();
           
    MyCmd.ExecuteNonQuery();
           
    MyConn.Close();
           
    Response.Redirect("WebForm2.aspx");
           }

           protected 
    void Button3_Click(object senderEventArgs e)
           {
               
    SqlConnection MyConn;
               
    SqlCommand MyCmd;
               
    String MyString "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
               
    MyConn = new SqlConnection(MyString);
               
    MyCmd = new SqlCommand("DELETE FROM Catalogue WHERE [Code] LIKE @Code "MyConn);
               
    MyCmd.Parameters.AddWithValue("@code"DropDownList1.SelectedItem.Value);
               
    MyConn.Open(); 
    // run the query, this method is used as it does not return a value
               
    MyCmd.ExecuteNonQuery();
               
    MyConn.Close(); 
           }
        }


    and

    Code:
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication21.WebForm2" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
         <div>
         Product_Name<br />
         <asp:TextBox ID="product_NameTB" runat="server" >
         </asp:TextBox>
         <br />Code<br />
         <asp:TextBox ID="CodeTB" runat="server" >
         </asp:TextBox>
         <br />Price<br />
         <asp:TextBox ID="PriceTB" runat="server" >
         </asp:TextBox>
         <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add" />
         <br />
         <br />
         <br />
         <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
         </asp:DropDownList>
         <br />Product_Name<br />
         <asp:TextBox ID="product_NamTB" runat="server"></asp:TextBox>
         <br />Price<br />
         <asp:TextBox ID="pricTB" runat="server"></asp:TextBox>
         <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Update" />
         <br />
         <br />
         <br />
         <asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="Delete" />
         
         </div>
         </form>
    </body>
    </html>
    Last edited by Brandoe85; 04-27-2011 at 05:16 PM.

  • #2
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hey Fot,

    You mean you want to do this inside your Button3_Click handler?

    Code:
    DropDownList1.Items.Remove(DropDownList1.SelectedItem)
    Mike

  • Users who have thanked Mike_O for this post:

    Fot (05-01-2011)

  • #3
    Fot
    Fot is offline
    Regular Coder
    Join Date
    Mar 2011
    Posts
    111
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Hello, sorry for the annoyance but I use it and I change this

    Code:
      MyCmd.Parameters.AddWithValue("@code", DropDownList1.SelectedItem.Value);
    into this and is wrong. I would help.Thanks

    Code:
    MyCmd.Parameters("@code",DropDownList1.Items.Remove(DropDownList1.SelectedItem));

  • #4
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hi Fot,

    I don't get it, what was your reasoning to change that to that? In any case, the Remove method doesn't return anything, so if you want to add the selected dropdown item value to the SQL, do so like in your first line. Then if you want to delete the selected item from the dropdown, do so like in my previous reply.

    I guess you were trying to put all that into one line of code, is that the case?

    Regards,
    Mike

  • Users who have thanked Mike_O for this post:

    Fot (05-04-2011)

  • #5
    Fot
    Fot is offline
    Regular Coder
    Join Date
    Mar 2011
    Posts
    111
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Yes you have right I make a mistake now its work. I would like your opinion because update works only with spaces ,cannot write in already existing rows.



    Database
    Code:
    Handmade_Earring	ZA	65.00€
    Handmade_Earring	BG	60.00€
    Handmade_Earring	PO	40.00€
    Handmade_Earring	YA	60.00€
    example space

    [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.SqlClient;
    using System.Web.UI.HtmlControls;
    using System.Configuration;
    using System.Collections;

    namespace WebApplication21
    {
    public partial class WebForm2 : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    SqlConnection MyConn;
    SqlCommand MyCmd;
    SqlDataReader MyRdr;
    String MyString = "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
    MyConn = new SqlConnection(MyString);

    if (!IsPostBack)
    {
    MyConn.Open();

    MyCmd = new SqlCommand("SELECT * FROM Catalogue", MyConn);
    MyRdr = MyCmd.ExecuteReader();
    DropDownList1.DataSource = MyRdr;
    DropDownList1.DataValueField = "Price";
    DropDownList1.DataTextField = "Code";
    DropDownList1.Items.Remove(DropDownList1.SelectedItem);
    DropDownList1.DataBind();
    MyRdr.Close();
    }
    }
    protected void rebind()
    {

    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
    SqlConnection MyConn;
    SqlCommand MyCmd;
    SqlDataReader MyRdr;
    String MyString = "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
    MyConn = new SqlConnection(MyString);
    MyCmd = new SqlCommand("SELECT * FROM Catalogue WHERE [Code] LIKE @Code", MyConn);
    MyCmd.Parameters.AddWithValue("@Code", DropDownList1.SelectedItem.Value);
    MyConn.Open();
    MyRdr = MyCmd.ExecuteReader();
    while (MyRdr.Read())
    {
    product_NamTB.Text = (string)MyRdr["Product_Name"];
    pricTB.Text = (string)MyRdr["Price"];
    }
    MyRdr.Close();
    MyConn.Close();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
    SqlConnection MyConn;
    SqlCommand MyCmd;
    String MyString = "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
    MyConn = new SqlConnection(MyString);
    MyCmd = new SqlCommand("INSERT INTO Catalogue (Product_Name, Code, Price) VALUES (@Product_Name, @Code, @Price)", MyConn);
    // update the command with the parameters from the text box’s
    MyCmd.Parameters.AddWithValue("@product_Name", product_NameTB.Text);
    MyCmd.Parameters.AddWithValue("@Code", CodeTB.Text);
    MyCmd.Parameters.AddWithValue("@Price", PriceTB.Text);
    MyConn.Open(); // open the connection
    // run the query, this method is used as it does not return a value
    MyCmd.ExecuteNonQuery();
    MyConn.Close(); // close the connection
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
    SqlConnection MyConn;
    SqlCommand MyCmd;
    String MyString = "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
    MyConn = new SqlConnection(MyString);
    MyCmd = new SqlCommand("UPDATE Catalogue SET [Product_Name] =@Product_Name, [Price] =@Price WHERE Code LIKE @Code", MyConn);
    MyCmd.Parameters.AddWithValue("@code", DropDownList1.SelectedItem.Value);
    MyCmd.Parameters.AddWithValue("@Product_Name", product_NamTB.Text);
    MyCmd.Parameters.AddWithValue("@Price", pricTB.Text);
    MyConn.Open();
    MyCmd.ExecuteNonQuery();
    MyConn.Close();
    Response.Redirect("WebForm2.aspx");
    }

    protected void Button3_Click(object sender, EventArgs e)
    {
    SqlConnection MyConn;
    SqlCommand MyCmd;
    String MyString = "Data Source= .\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\Databasebest.mdf; Integrated Security=true; User Instance=true";
    MyConn = new SqlConnection(MyString);
    MyCmd = new SqlCommand("DELETE FROM Catalogue WHERE
    Code:
     LIKE @Code ", MyConn);
               MyCmd.Parameters.AddWithValue("@code", DropDownList1.SelectedItem.Value);
               DropDownList1.Items.Remove(DropDownList1.SelectedItem);
               MyConn.Open(); 
    // run the query, this method is used as it does not return a value
               MyCmd.ExecuteNonQuery();
               MyConn.Close(); 
           }
        }
    
    }


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •