Select Statement with Parameters

  • Thread starter Thread starter whoopes
  • Start date Start date
W

whoopes

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>
 
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!!
Click to expand...

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

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

WHERE EmpFirstname = '@empid'
Click to expand...

The apostrophes is added automatically.

WHERE EmpFirstname = @empid
 
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>
 
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")
Click to expand...

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?
 
Still, this doesnt' seem right. You only get that sort of error when using
a stored procedure AFAIK.
Click to expand...

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

Are you sure it's not coming from somewhere else?
Click to expand...

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?
 
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?
 
Back
Top