Select Statement with Parameters

Discussion in 'ASP .Net' started by whoopes@gmail.com, Jun 15, 2006.

  1. Guest

    The following code returns no results. Removing the quotes from the
    question mark yields the following error: Incorrect syntax near '?'.
    Replacing the question mark with William does give results. Please
    help!!

    ://Bill



    <%@ Page language="VB"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <HTML>
    <script language="VB" runat="server">
    Sub Page_Load(sender As Object, e As EventArgs)
    Dim login_user As String
    login_user = Request.ServerVariables("LOGON_USER")
    Session.Add("User_ID", login_user)
    Response.Write("Session ID from the SCRIPT section is: <B>" &
    Session("User_ID") & "</B>. Moving on....")
    End Sub
    </script>
    <body>
    <form id="Form1" method="post" runat="server">
    <P>Show My Orders:</P>

    <asp:SqlDataSource
    id="OdbcDataSource1"
    runat="server"
    ProviderName="System.Data.SqlClient"
    ConnectionString="Data Source=XXX;Initial
    Catalog=Employee;Persist Security Info=True;User ID=YYY;Password=ZZZ"
    SelectCommand="SELECT EmpFirstname, EmpMiddlename,
    EmpLastName
    FROM Employee
    WHERE EmpFirstname = '?'
    ORDER BY EmpLastName;"/>
    <SelectParameters>
    <asp:SessionParameter
    Name="empid"
    SessionField="User_ID"
    DefaultValue="William" />
    </SelectParameters>


    </asp:SqlDataSource>

    <p>
    <asp:GridView
    id="GridView1"
    runat="server"
    DataSourceID="OdbcDataSource1" />
    </p>
    </form>
    </body>
    </HTML>
    , Jun 15, 2006
    #1
    1. Advertising

  2. On 15 Jun 2006 11:04:03 -0700, wrote:

    > The following code returns no results. Removing the quotes from the
    > question mark yields the following error: Incorrect syntax near '?'.
    > Replacing the question mark with William does give results. Please
    > help!!


    You can't use the ? syntax. Use named parameters.

    WHERE EmpFirstname = '@empid'
    Erik Funkenbusch, Jun 15, 2006
    #2
    1. Advertising

  3. Erik Funkenbusch wrote:
    > On 15 Jun 2006 11:04:03 -0700, wrote:
    >
    >> The following code returns no results. Removing the quotes from the
    >> question mark yields the following error: Incorrect syntax near '?'.
    >> Replacing the question mark with William does give results. Please
    >> help!!

    >
    > You can't use the ? syntax. Use named parameters.
    >
    > WHERE EmpFirstname = '@empid'


    The apostrophes is added automatically.

    WHERE EmpFirstname = @empid
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Jun 15, 2006
    #3
  4. Guest

    EmpFirstname = @empid generates a SQL error: "Must declare
    variable....."



    Göran Andersson wrote:
    > Erik Funkenbusch wrote:
    > > On 15 Jun 2006 11:04:03 -0700, wrote:
    > >
    > >> The following code returns no results. Removing the quotes from the
    > >> question mark yields the following error: Incorrect syntax near '?'.
    > >> Replacing the question mark with William does give results. Please
    > >> help!!

    > >
    > > You can't use the ? syntax. Use named parameters.
    > >
    > > WHERE EmpFirstname = '@empid'

    >
    > The apostrophes is added automatically.
    >
    > WHERE EmpFirstname = @empid
    , Jun 15, 2006
    #4
  5. On 15 Jun 2006 12:52:06 -0700, wrote:

    > EmpFirstname = @empid generates a SQL error: "Must declare
    > variable....."


    You need to set the name of the parameter to @empid as well.
    Erik Funkenbusch, Jun 15, 2006
    #5
  6. Guest

    Thanks for all the help, I must also add that I am new to all this.
    Still getting a 'must declare variable error...' (tried Name="@empid"
    and Name="empid")

    <%@ Page language="VB"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <HTML>
    <script language="VB" runat="server">
    Sub Page_Load(sender As Object, e As EventArgs)
    Dim login_user As String
    login_user =
    Request.ServerVariables("LOGON_USER")
    Session.Add("User_ID", login_user)
    Response.Write("Session ID from the SCRIPT
    section is: <B>" & Session("User_ID") & "</B>. Moving on....")
    End Sub
    </script>
    <body>
    <form id="Form1" method="post" runat="server">
    <P>Show My Orders:</P>

    <asp:SqlDataSource
    id="OdbcDataSource1"
    runat="server"
    ProviderName="System.Data.SqlClient"
    ConnectionString="Data Source=ZZZZ;Initial
    Catalog=Employee;Persist Security Info=True;User ID=XXXX;Password=XXXX"
    SelectCommand="SELECT EmpFirstname, EmpMiddlename, EmpLastName FROM
    Employee WHERE EmpFirstname = @empid ORDER BY EmpLastName;"/>
    <SelectParameters>
    <asp:SessionParameter
    Name="@empid"
    SessionField="User_ID"
    DefaultValue="William" />
    </SelectParameters>

    </asp:SqlDataSource>

    <p>
    <asp:GridView
    id="GridView1"
    runat="server"
    DataSourceID="OdbcDataSource1" />
    </p>
    </form>
    </body>
    </HTML>
    , Jun 15, 2006
    #6
  7. On 15 Jun 2006 13:26:29 -0700, wrote:

    > Thanks for all the help, I must also add that I am new to all this.
    > Still getting a 'must declare variable error...' (tried Name="@empid"
    > and Name="empid")


    Actually, i was mistaken. You don't need the @ on the name attribute of
    the parameter.

    Still, this doesnt' seem right. You only get that sort of error when using
    a stored procedure AFAIK.

    Are you sure it's not coming from somewhere else?
    Erik Funkenbusch, Jun 15, 2006
    #7
  8. Guest


    > Still, this doesnt' seem right. You only get that sort of error when using
    > a stored procedure AFAIK.


    This is what I thought as well but I am an amatuer. :)


    > Are you sure it's not coming from somewhere else?


    I don't think so based on the error message:

    System.Data.SqlClient.SqlException: Must declare the variable '@empid'.

    Looks like since the error includes, SqlClient.SqlException, the code
    isn't completing the SQL statement with the parameters before sending
    it to the server. Right?
    , Jun 16, 2006
    #8
  9. Guest

    Bueller, Bueller, Buuuuuuueller....
    , Jun 19, 2006
    #9
  10. I am having the same problem. When I look at the query as it is passed to
    SQLserver (in sql profiler), I can see that the query still has the @variable
    in the string. It should be replaced before that. Don't ask me why it isn't
    though I am new to .net too. BTW did you ever get this resolved?

    "" wrote:

    > Bueller, Bueller, Buuuuuuueller....
    >
    >
    =?Utf-8?B?bmF0ZQ==?=, Aug 29, 2006
    #10
  11. nate schrieb:

    > though I am new to .net too. BTW did you ever get this resolved?


    You may not use oledb - because it can't handle parameters. SQL works.
    Stephan Huewe, Sep 25, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    791
  2. Jay McGavren
    Replies:
    11
    Views:
    1,113
    Alan Krueger
    Jan 16, 2006
  3. tedsuzman
    Replies:
    2
    Views:
    7,066
    Michel Claveau, résurectionné d'outre-bombe inform
    Jul 21, 2004
  4. Ted
    Replies:
    1
    Views:
    453
    Duncan Booth
    Jul 22, 2004
  5. palmiere
    Replies:
    1
    Views:
    389
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page