Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am trying to create a search box in my application, in order to do so i need to modify the SqlDataSource.SelectCommand. I will appreciate any help!

For testing I did it like this, it works but it is vulnerable for sql injection

   SqlDataSource1.SelectCommand = "sp_offer_search '" + txtSearch.Text + "', " +   Session["customerId"] + " , '" + "Pending"+ "'";
   GridView1.DataBind();

This is What I tried so far but it is not working:

 if (txtSearch.Text != "")
        {                
         //open connection
          oCn.Open();
          SqlCommand com = new SqlCommand(query, oCn);
          com.CommandType = CommandType.StoredProcedure;

          com.Parameters.AddWithValue("@Variable", txtSearch.Text);  
          com.Parameters.AddWithValue("@CustomerId",Session["customerId"]);   
          com.Parameters.AddWithValue("@Status", txtStatus.Text);   


            DataTable dt = new DataTable();
            dt.Load(com.ExecuteReader());

            SqlDataSource1.SelectCommand = dt.ToString();
            GridView1.DataBind();
       }
share|improve this question
    
Exactly how does it now work? Do you get an error, or incorrect returns, or nothing returned? Something else? –  Matthew Jones Jan 2 at 16:28
    
it doesn't return anything, it like it doesn't find a match –  Carlos Jan 2 at 16:29
1  
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... –  marc_s Jan 2 at 16:35
    
Marc_s thank you for the hands up. I did not know that. –  Carlos Jan 2 at 16:40

2 Answers 2

If the GridView datasource is set to the SqlDataSource1 then you don't need the DataTable. And a DataTable.ToString() is not a selectCommand. Try:

 if (txtSearch.Text != "")
    {                        
      SqlCommand com = new SqlCommand(query, oCn);
      com.CommandType = CommandType.StoredProcedure;

      com.Parameters.AddWithValue("@Variable", txtSearch.Text);  
      com.Parameters.AddWithValue("@CustomerId",Session["customerId"]);   
      com.Parameters.AddWithValue("@Status", txtStatus.Text);   

      SqlDataSource1.SelectCommand = com;
      GridView1.DataBind();
   }
share|improve this answer
    
Hi Crowcoder, "com" is returning System.Data.SqlClient.SqlCommand and is causing an error:'System.Data.SqlClient.SqlCommand' is not supported in this version of SQL Server any ideas? –  Carlos Jan 2 at 16:37
    
What is your database? –  Crowcoder Jan 2 at 16:41
    
we are using SQL Azure –  Carlos Jan 2 at 16:44
1  
I've only used EntityFramework against SQL Azure. I found this (axian.com/2014/12/01/adjustments-for-sql-azure-no-ole-db) that implies the ADO.Net OleDB provider might work. But if you are going to switch the provider I would suggest EntityFramework. –  Crowcoder Jan 2 at 17:00
up vote 0 down vote accepted

Solved ! Here is what I tried and it works. I hope this can b helpful for someone.

  if (txtSearch.Text != "")
        {
            try
            {
                //  open connection
                oCn.Open();

                SqlDataAdapter da = new SqlDataAdapter("sp_offer_search", oCn);

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add("@Variable", SqlDbType.VarChar).Value = txtSearch.Text;
                da.SelectCommand.Parameters.Add("@CustomerId", SqlDbType.Int).Value = Session["customerId"];
                da.SelectCommand.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Pending";

                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView1.DataSourceID = String.Empty;
                GridView1.DataSource = dt;
                GridView1.DataBind();

            }
            catch(Exception ex)
            {
                Response.Write(ex.ToString());
            }
            finally
            {
                oCn.Close();
            }

        }
        else
        {
            GridView1.DataSourceID = "SqlDataSource1";
            SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand;
            GridView1.DataBind();
        }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.