Passing variable to a SQL statement does not work?

21 Apr 2006 7:05 PM

glenn

Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

Dim queryString, q1, q2 As String

This works:
   querystring="SELECT * FROM USERS WHERE CNAME = Microsoft"

This does not work:
   Dim var as string
   var = "Microsoft"
   querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"

I have done a Response.Write (q1) and Response.Write (q2) where
q1 is a hard-coded SQL statement and where q2 passes a variable to a SQL
string

The values written to the screen are exactly the same.

Why does the hard-coded version work and the var version not work?

Thanks for any clues.
glenn

21 Apr 2006 7:15 PM

glenn

SORRY FOR THE DUPLICATE POST. IT'S MY MISTAKE.

Show quote

"glenn" wrote:

> Hi folks,
>
> The problem I have is that a query string works if hard-coded but
> if I pass a variable to it, it does not work as shown here.
>
> Dim queryString, q1, q2 As String
>
> This works:
>    querystring="SELECT * FROM USERS WHERE CNAME = Microsoft"
>
> This does not work:
>    Dim var as string
>    var = "Microsoft"
>    querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"
>
> I have done a Response.Write (q1) and Response.Write (q2) where
> q1 is a hard-coded SQL statement and where q2 passes a variable to a SQL
> string
>
> The values written to the screen are exactly the same.
>
> Why does the hard-coded version work and the var version not work?
>
> Thanks for any clues.
> glenn
>
>

21 Apr 2006 7:28 PM

Norman Yuan

From your post, I see the completely opposite result:

1. When print these two query string to the screen, they are NOT the same:

first one:

SELECT * FROM USERS WHERE CNAME = Microsoft

second one:

SELECT * FROM USERS WHERE CNAME = 'Microsoft'

2. It seems the column "CNAME" is text type. Most people who know SQL in
general, would say the second is correct, while the first is not, because it
misses the single quote mark around the text value in WHERE clause.

So, how come the first one works for you? What special database is it?

Show quote

"glenn" <gl***@discussions.microsoft.com> wrote in message
news:9BA29825-961F-45BC-BC01-33A5429FBBFE@microsoft.com...
> Hi folks,
>
> The problem I have is that a query string works if hard-coded but
> if I pass a variable to it, it does not work as shown here.
>
> Dim queryString, q1, q2 As String
>
> This works:
>   querystring="SELECT * FROM USERS WHERE CNAME = Microsoft"
>
> This does not work:
>   Dim var as string
>   var = "Microsoft"
>   querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"
>
> I have done a Response.Write (q1) and Response.Write (q2) where
> q1 is a hard-coded SQL statement and where q2 passes a variable to a SQL
> string
>
> The values written to the screen are exactly the same.
>
> Why does the hard-coded version work and the var version not work?
>
> Thanks for any clues.
> glenn
>
>

22 Apr 2006 5:34 AM

JT

Hi Glenn,
Your statement is actually querying for a row where CNAME = & var &. What
you want is:
Dim var as string = "'Microsoft'"
querystring="SELECT * FROM USERS WHERE CNAME = " & var

--
John

Show quote

"glenn" wrote:

> Hi folks,
>
> The problem I have is that a query string works if hard-coded but
> if I pass a variable to it, it does not work as shown here.
>
> Dim queryString, q1, q2 As String
>
> This works:
>    querystring="SELECT * FROM USERS WHERE CNAME = Microsoft"
>
> This does not work:
>    Dim var as string
>    var = "Microsoft"
>    querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"
>
> I have done a Response.Write (q1) and Response.Write (q2) where
> q1 is a hard-coded SQL statement and where q2 passes a variable to a SQL
> string
>
> The values written to the screen are exactly the same.
>
> Why does the hard-coded version work and the var version not work?
>
> Thanks for any clues.
> glenn
>
>