Question about selectcommand when using sqldatasource

I'm try to achieve the following ;

<asp:SqlDataSourceID="CardDataSource"runat="server"ConnectionString="my connectionSelectCommand="String.Format("Select*fromcardswhereactivenotlike'0'oractiveisNULLand(cardlike' {0}%'orcardlike'{0}%')", Request.QueryString("db"))"/>

I'm trying to pass a value into my query from the sqldatasource, but I'm having trouble properly using the string. This code used to be in a vb code, but theres a considerable performance difference when I have it load from my vb file and when I front load it here.

Anyone know what i would have to add or remove to make the above script work? Do I have my single/double quotes mixed up?

Why don't you try just calling a Stored Procedure?

|||

For some reason it slows up the modules that I am using. I if I embed the select string iinto sqldatasource directly the modules work quickly, but if I do it any other way it locks up. I'm using DevExpress. They don't know why it does it. They looked at my code and everything checks out. But when I do this way, everything works fine. However, I can't seem to structure this string properly.

|||

Hi,

From the code you provided, actually you are passing a parameter to the select command and make your sqldatasource to retrieve data, right?

There two ways to declare the SqlDataSource object and parameters. One is in code behind, and another way is inline. In your case, it belongs to the second scenario.

You may declare your parameters in the SelectParameters node so that it can work.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [TABLE] whereUser=@.ParameterName">
<SelectParameters>
<asp:QueryStringParameter Name="ParameterName" QueryStringField="StringField" DefaultValue="DefaultVlaue" />
</SelectParameters>
</asp:SqlDataSource>

Thanks.

|||

Well the parameter is the table itself. Does this work in that scenario as well? I have three tables but only want to use one page. So I pass the table name through the url.

|||

Hi,

If what the parameter passes is the tablename, you should assign the select statement in your code behind file, see:

string sqlstr = "select * from ";
string TableName = Request.QueryString["db"].ToString(); // After it, you should remove all the special chars like '-','*' in variable TableName in order to prevent the sql injection.

sqlstr += TableName;
this.SqlDataSource1.SelectCommand = sqlstr; // Assume that you have declared a SqlDataSource called SqlDataSource1 on your webform.

Thanks.