How can I use a variable in a SqlDataSource SelectCommand?

Question

So making a .Net site with VB and running off of a MySQL server.

Trying to use a variable within the connectionstring to only retrieve certain data.

I Currently have in the header:

<script language="vbscript">
     Dim varSQLcommand as String

     varSQLcommand = "1"
</script>

and the connection string of

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:emergency systemsConnectionString3 %>" 
    ProviderName="<%$ ConnectionStrings:emergency systemsConnectionString3.ProviderName %>" 
    SelectCommand= "SELECT * from tbldisaster WHERE Disaster_ID = " & varSQLcommand & "" >
</asp:SqlDataSource>

Going to replace varSQLcommand = "1" with varSQLcommand = Request.QueryString("ID") and make the url disasterdetails.aspx?ID={1}.

is this the Correct way to do what I want to do?

Answer 1

There is another question on here that may help you. You could either use the SelectParameters as stated in another reply, or you could declare them in your code-behind using the following:

Dim myDisasterID As String = Request.Querystring("id")
'Do whatever you may have to do with the variable

SqlDataSource1.SelectParameters.Add("variablename", myDisasterID);
SqlDataSource1.SelectCommand = "SELECT * FROM tbldisaster where disasterid=@variablename"

Here

Answer 2

You can use the SelectParameters to set custom variables in your SQL:

SelectCommand="SELECT * FROM tblDisaster WHERE Disaster_ID = @Disaster_ID">
    <SelectParameters>
        <asp:QueryStringParameter QueryStringField="ID" Name="Disaster_ID" />
    </SelectParameters>

In this case, the filter criteria comes from the Query String (?id=123), so we use the QueryStringParameter, and bind it to parameter with name @Disaster_ID. Then the query will pull the id value from the url and replace it where @Disaster_ID appears in the query.