SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sqlcommand object parameters

    I am trying to implement a search on a SQL database using a textbox and a dropdownlist. The sql command has two parameters in it that effectively says "SELECT * FROM myTable WHERE @Param1 = @Param2" where Param1 is being set from the drop down list that is a list of the table headers and Param2 is the string in the textbox. This is not working. It compiles but does not return any results.

    If I hard code Param1 or the column header in and just leave Param2 the search works fine. When stepping through the code both lines that assign a value to the parameters in the SqlCommand object are assigning the correct values but the commandtext property still shows the parameters as @Param1 and @Param2.

    Is there something inherently wrong with this.

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Based on your sample query it appears you are comparing both parameters in your query. Why are you doing this? If the two parameters don't match each other then your query won't return any results.

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I want to do is have the first parameter be the column to search and the second be the value to be looking up in that column. Here is the acctual code in question. if I take @SearchItem and replace it with the constant hostname the querey succesfully returns all row's where the hostname = @SearchQuery. What I am attempting to do though is let the user decide what the search on so they could search any of the columns not just hostname. I have a dropdownlist with the possible collumn names.

    Code:
        protected void Button1_Click(object sender, EventArgs e)
        {
                SqlConnection conn;
                SqlCommand comm;
                SqlDataReader reader;
                serverDetails.Visible = false;    
    
                string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString;
                string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery";
    
                conn = new SqlConnection(connection);
                comm = new SqlCommand(sqlquery, conn);
    
                String searchQuery = searchBox.Text;
                String searchItem = DropDownList1.SelectedValue.ToString();
    
                comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar);
                comm.Parameters["@SearchQuery"].Value = searchQuery;
                comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar);
                comm.Parameters["@SearchItem"].Value = searchItem;
    
                try
                {
                    conn.Open();
                    reader = comm.ExecuteReader();
                    GridView1.DataSource = reader;
                    GridView1.DataKeyNames = new string[] { "server_id" };
                    GridView1.DataBind();
                    reader.Close();
                }
                finally
                {
                    conn.Close();
                }
            
        }

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    New York
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would just concatenate sqlquery to get the field name from the dropdown. Then just worry about the actual value parameter - something like:

    string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE " + DropDownList1.SelectedValue.ToString() + " = @SearchQuery";

  5. #5
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks this works perfectly. Not sure why my first way did not but I am not picky, it works, Im happy.


Bookmarks

Posting Permissions

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