Passing variables to a ms sql select line

saljas
saljas used Ask the Experts™
on
I am trying to pass the CustIdIs variable to the select line - I am using the following code which is not working

        <%
            Dim CustIdIs
            CustIdIs = Session("CustIdIs")
            Response.Write(CustIdIs)                  'this gives 2007-1 which is ok
        %>

        <asp:SqlDataSource ID="sourcename" runat="server"
            SelectCommand="SELECT Col1, col2, col3, col4 FROM Table WHERE Col0 = @CustIdIs "
            ConnectionString="<%$ ConnectionStrings:myConnectionString %>">
            <SelectParameters>
                <asp:Parameter DefaultValue="'<%=CustIdIs%>'" Name="CustIdIs" DbType="String" />  
            </SelectParameters>
        </asp:SqlDataSource>

But    if I replace @CustIdIs   with  '2007-1' it works fine
which means the value in the variable  CustIdIs  is not passed
Any Idea how to solve it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel Van Der WerkenIndependent Consultant
Commented:
Here is my reference.

I think what you need to add is the SelectParameter element such as:

    <SelectParameters>
        <asp:QueryStringParameter Name="CustIdIs" DbType = "String" Direction = "Input" QueryStringField="Col0" DefaultValue="" ConvertEmptyStringToNull="True" />
    </SelectParameters>

Open in new window


Ultimately, this:

<asp:SqlDataSource ID="sourcename" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
    SelectCommand="SELECT Col1, col2, col3, col4 FROM Table WHERE Col0 = @CustIdIs "CancelSelectOnNullParameter="false">
    <SelectParameters>
        <asp:QueryStringParameter Name="CustIdIs " DbType = "String" Direction = "Input" QueryStringField="Col0" DefaultValue="" ConvertEmptyStringToNull="True" />
    </SelectParameters>

Open in new window

</asp:SqlDataSource>

If that isn't what you're looking for, then my guess is that it's the quote or double-quote causing the issue here:

DefaultValue="'<%=CustIdIs%>'" 

Open in new window


Try it without the double-quote first. If that fails, try it without the single-quote.
Commented:
Many thanks Mr. Daniel Van Der Werken
I have tried all of what you said with much more experiments but did not work. At the end the following code works, which I did not like because in order to get returned data I have to add a gridview control!!

        <asp:SqlDataSource ID="sourcename" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>">            
        </asp:SqlDataSource>
        <%  
            sourcename.SelectParameters.Add("CustIdIs", Session("CustIdIs"))
            sourcename.SelectCommand = "SELECT Col1, col2, col3, col4 FROM Table WHERE Col0= @CustIdIs  "
         %>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="sourcename">
            <Columns>
                <asp:BoundField DataField="Col1" HeaderText="Txt1" SortExpression="ttt" />                
                <asp:BoundField DataField="Col2" HeaderText="Txt2" SortExpression="ttt" />                
                <asp:BoundField DataField="Col3" HeaderText="Txt3" SortExpression="ttt" />                
                <asp:BoundField DataField="Col4" HeaderText="Txt4" SortExpression="ttt" />                
            </Columns>
        </asp:GridView>

Author

Commented:
Close the question

Author

Commented:
I could not find a solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial