Modify SqlDataSource.SelectCommand using Stored Procedure with parameters

Question

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

Exactly how does it now work? Do you get an error, or incorrect returns, or nothing returned? Something else? — Matthew Jones
– Matthew Jones, Commented Jan 2, 2015 at 16:28
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Jan 2, 2015 at 16:35

Crowcoder · Accepted Answer · 2015-01-02 16:30:27Z

5

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

answered Jan 2, 2015 at 16:30

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Carlos Over a year ago

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?

Crowcoder Over a year ago

What is your database?

Carlos Over a year ago

we are using SQL Azure

Crowcoder Over a year ago

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.

Carlos · Accepted Answer · 2015-01-02 20:53:27Z

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

Collectives™ on Stack Overflow

Modify SqlDataSource.SelectCommand using Stored Procedure with parameters

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related