Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Select Statement with Parameters (http://www.velocityreviews.com/forums/t300291-select-statement-with-parameters.html)

whoopes@gmail.com 06-15-2006 06:04 PM

Select Statement with Parameters
 
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>


Erik Funkenbusch 06-15-2006 06:53 PM

Re: Select Statement with Parameters
 
On 15 Jun 2006 11:04:03 -0700, whoopes@gmail.com 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'

=?ISO-8859-1?Q?G=F6ran_Andersson?= 06-15-2006 07:16 PM

Re: Select Statement with Parameters
 
Erik Funkenbusch wrote:
> On 15 Jun 2006 11:04:03 -0700, whoopes@gmail.com 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

whoopes@gmail.com 06-15-2006 07:52 PM

Re: Select Statement with Parameters
 
EmpFirstname = @empid generates a SQL error: "Must declare
variable....."



Göran Andersson wrote:
> Erik Funkenbusch wrote:
> > On 15 Jun 2006 11:04:03 -0700, whoopes@gmail.com 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



Erik Funkenbusch 06-15-2006 08:12 PM

Re: Select Statement with Parameters
 
On 15 Jun 2006 12:52:06 -0700, whoopes@gmail.com wrote:

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


You need to set the name of the parameter to @empid as well.

whoopes@gmail.com 06-15-2006 08:26 PM

Re: Select Statement with Parameters
 
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>


Erik Funkenbusch 06-15-2006 10:41 PM

Re: Select Statement with Parameters
 
On 15 Jun 2006 13:26:29 -0700, whoopes@gmail.com 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?

whoopes@gmail.com 06-16-2006 11:26 AM

Re: Select Statement with Parameters
 

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


whoopes@gmail.com 06-19-2006 05:53 PM

Re: Select Statement with Parameters
 
Bueller, Bueller, Buuuuuuueller....


=?Utf-8?B?bmF0ZQ==?= 08-29-2006 04:56 PM

Re: Select Statement with Parameters
 
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?

"whoopes@gmail.com" wrote:

> Bueller, Bueller, Buuuuuuueller....
>
>



All times are GMT. The time now is 10:44 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.