use variable for dynamic data selection

Question

User-628505736 posted

Hello,

 

I have a problem when I want to use a variable in order to make an MS SQL query in order to retrieve data from a database

 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:ConnectionString123 %>" 
                    SelectCommand="SELECT [column1], [column2], [column3], [column4], [column5], [column6] FROM [Table1] WHERE ([column1] =<%= variable1%>)">
                </asp:SqlDataSource>

The problem is that I do not want to use a parameter because I already have the variable behind in C# code. So I want to use something like "<%=variable1 %>" 

to insert the variable into the query. But once I write that inside the query, the whole thing does not work and comes with error messages.

 Is there any way to add a variable into that query? Remember I do not want to use parameters i.e.

<SelectParameters>
<asp:Parameter DefaultValue="1257" Name="column1" Type="String" />
</SelectParameters>

Answer 1

User761909039 posted

You can do like this,

SelectCommand="SELECT [column1], [column2], [column3], [column4], [column5], [column6] FROM [Table1] WHERE ([column1] ={0})"

and in your page_load

SqlDataSource1.SelectCommand = string.Format(SqlDataSource1.SelectCommand, variable1);

And if the column is varchar, dont forget to add codes ( ' )

Answer 2

User3866881 posted

HI：）

Hi：）

1) Please keep the changed select statement into the ViewState or SessionBag first.

2) In the  Page_Load event, please dynamically change your sql statement sentence——

protected void Page_Load（……）

｛

          if(ViewState["select"]!=null)

            {

                SqlDataSource1.SelectCommand = ViewState["select"].ToString();

            }

｝