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

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

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?
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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related