Dynamically using SQL statements for SQLDataSource

Question

I may or may not have a pretty simple question here for you guys. Basing my if-else statements off of the existence of a string, I am trying to either call a SELECT statement with a parameter or not and pass them both to the same resulting GridView.

Here is what I am trying to do:

 string query;
 if(BadgeNumLabel.Text != "")
 {
     query = "SELECT * FROM AUDITS";
 else
 {
     query = "SELECT * FROM AUDITS WHERE BADGENUM = :BadgeNumLabel";
     GridDataSource.SelectParameters.Add(new Parameter("BadgeNumLabel",TypeCode.String, BadgeNumLabel.Text));
 }

 GridDataSource.SelectCommand = query;
 GridView1.DataBind();

My .aspx code looks like this:

 <asp:SqlDataSource ID="GridDataSource" runat="server"
            ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>"  
            onselecting="GridDataSource_Selecting">


        </asp:SqlDataSource>

Is there something I am missing? I am so stumped. I think it has to do something with passing the parameter BadgeNumLabel around but I'm not sure.

Any help is greatly appreciated! Thanks!

where your're passing the parameter value?. Also do you test that sql query? — Jorge, May 23 '12 at 18:46
I tried using this: GridDataSource.SelectParameters.Add(new Parameter("BadgeNumLabel", TypeCode.String, BadgeNumLabel.Text)); But it didn't work. — se_brandon, May 23 '12 at 18:48

Luis · Answer 1 · 2012-05-23T19:20:53.923

1

You need to assign the result of the GridDataSource to the GridView1.DataSource

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand GridDataSource= new SqlCommand();

GridDataSource.CommandText = "SELECT * FROM AUDITS";
GridDataSource.CommandType = CommandType.Text;
GridDataSource.Connection = sqlConnection1;

sqlConnection1.Open();

GridView1.DataSource = GridDataSource.ExecuteReader();
GridView1.DataBind();

EDIT

It's not literally ExecuteCommand, what I try to say is that you need to assign the result of the SqlCommand to the GridView

edited May 23 '12 at 19:20

answered May 23 '12 at 19:00

Luis

5,786
8
43
62

It says I don't have an assembly reference to GridDataSource.ExecuteCommand when I enter that line. – se_brandon May 23 '12 at 19:03
Where would you use the 'SqlDataReader' object above. It looks like you have created it but haven't used it. Also, if I already have a SQLDataSource created, would I have to go about opening up a new connection? – se_brandon May 23 '12 at 19:18
Ok, I just deleted the `SqlDataReader`. You should run this fast example just add the connection String and if it works, then you're sure the problem is that you're not assigning the resulting query to the `GridView1.DataSource`. – Luis May 23 '12 at 19:25

Dynamically using SQL statements for SQLDataSource

1 Answers1