Syntax error in SQL Statement????

Dean Bolton

unread,

Mar 8, 2002, 3:53:05 PM3/8/02

to

I have the following SQL statement:

set rs = conn.execute("SELECT MatID, VendID FROM Material_Vendor WHERE MatID
= '" & ArrMatID(i) & "' AND VendID = '" & iVendValue & "'")

I get a syntax error saying: Incorrect syntax near '196' , which is the ID
held in ArrMatID(i)

When I look at the statement in SQL Profiler, it shows ' '196' '

If I remove the single quotes from around, like this - " & ArrMatID(i) & ",
then SQL Profiler shows no quotes around 196. I know that I need quotes
around 196, but not sure why the page is complaining.

Any ideas?

Thanks,
Dean

Manohar Kamath [MVP]

unread,

Mar 8, 2002, 4:05:02 PM3/8/02

to

How are you getting ArrMatID(i)? Maybe the value itself has apostrophes, so
adding one in your SQL is redundant, and hence the error.

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com

"Dean Bolton" <de...@vdubn.com> wrote in message
news:a6b8bh$6...@dispatch.concentric.net...

Aaron Bertrand [MVP]

unread,

Mar 8, 2002, 4:15:14 PM3/8/02

to

> set rs = conn.execute("SELECT MatID, VendID FROM Material_Vendor WHERE
MatID
> = '" & ArrMatID(i) & "' AND VendID = '" & iVendValue & "'")

is MatID an INT or VARCHAR column? If it is numeric, drop the single
quotes. Those are for strings. Otherwise, explain what you mean by "I know
that I need quotes" and exactly what profiler is showing in both cases.

www.aspfaq.com

Mikhail Berlyant

unread,

Mar 8, 2002, 4:19:57 PM3/8/02

to

BTW, what is the datatype of MatID?

Mikhail Berlyant
Data Integrator, Yahoo!Music www.yahoo.com
Brainbench MVP for Visual Basic www.brainbench.com

"Dean Bolton" <de...@vdubn.com> wrote in message
news:a6b8bh$6...@dispatch.concentric.net...

Dean Bolton

unread,

Mar 8, 2002, 4:20:02 PM3/8/02

to

Here is how I am collecting ArrMatID(i)

ArrMatID = Split(request.form("Attach"),", ")
For i=0 to ubound(ArrMatID)

I am pretty sure that all this does is separate the values separated by
commas.

Thanks,
Dean

"Manohar Kamath [MVP]" <mka...@TAKETHISOUTbeforeUMAILMEkamath.com> wrote in
message news:OvhouSuxBHA.2132@tkmsftngp02...

Dean Bolton

unread,

Mar 8, 2002, 4:42:22 PM3/8/02

to

MatID is a numeric field, hence the reason that I didn't run with quotes in
the statement. But, when I leave them out, the Profiler shows the following
statement (with the single quotes)...

SELECT MatID, VendID FROM Material_Vendor WHERE MatID = '196' AND VendID = 3

How can I remove those?

Thanks,
Dean

"Aaron Bertrand [MVP]" <aaronATaspfaq.com> wrote in message
news:O34F5YuxBHA.1556@tkmsftngp03...

Mikhail Berlyant

unread,

Mar 8, 2002, 5:17:24 PM3/8/02

to

Try this:

set rs = conn.execute("SELECT MatID, VendID FROM Material_Vendor WHERE MatID

= " & CLng(ArrMatID(i)) & " AND VendID = '" & iVendValue & "'")

Mikhail Berlyant
Data Integrator, Yahoo!Music www.yahoo.com
Brainbench MVP for Visual Basic www.brainbench.com

-----Original Message-----
From: Dean Bolton [mailto:de...@igi-hq.com]
Sent: Friday, March 08, 2002 2:04 PM
To: Berlyant, Mikhail
Subject: Re: Syntax error in SQL Statement????

It is Numberic, and I noticed if I just display ArrMatID(0) it give me '196'
as the value (with the single quotes). How can I get rid of the single
quotes, or a better question, why is the Array putting single quotes around
the ID?

Thanks,
Dean

"Mikhail Berlyant" <berl...@yahoo-inc.com> wrote in message
news:<#BsNqauxBHA.492@tkmsftngp02>...

> BTW, what is the datatype of MatID?
>
> Mikhail Berlyant
> Data Integrator, Yahoo!Music www.yahoo.com
> Brainbench MVP for Visual Basic www.brainbench.com
>

> "Dean Bolton" <de...@vdubn.com> wrote in message
> news:a6b8bh$6...@dispatch.concentric.net...

"Dean Bolton" <de...@vdubn.com> wrote in message
news:a6b8bh$6...@dispatch.concentric.net...

Aaron Bertrand [MVP]

unread,

Mar 8, 2002, 5:18:22 PM3/8/02

to

Looks like you removed them from iVendValue and not ArrMatID(i).

What happens when you response.write the SQL statement directly to the
screen, instead of executing it? This seems to be a more direct approach at
debugging than going to Profiler and assuming that something else is putting
quotes in there arbitrarily.

www.aspfaq.com

"Dean Bolton" <de...@vdubn.com> wrote in message

news:a6bb7u$6...@dispatch.concentric.net...

Dean Bolton

unread,

Mar 8, 2002, 5:24:42 PM3/8/02

to

When I do a response.write with ArrMatID(0), I get the number with quotes
around it, like '196'

I checked, I am pulling from the Array, any idea why it would add quotes? I
am using it after doing a Split function that looks like this:

ArrMatID = Split(request.form("Attach"),", ")

Is there anything in the Split function that could be causing this?

Thanks,
Dean

"Aaron Bertrand [MVP]" <aaronATaspfaq.com> wrote in message

news:uDgSL8uxBHA.2716@tkmsftngp04...

Mikhail Berlyant

unread,

Mar 8, 2002, 5:33:04 PM3/8/02

to

Problem is not in Split function.
Use CLng to Convert String to Long.
In ASP there is no types, so all variables are Variant and default subtype
for Variant is String.

Mikhail Berlyant
Data Integrator, Yahoo!Music www.yahoo.com
Brainbench MVP for Visual Basic www.brainbench.com

"Dean Bolton" <de...@vdubn.com> wrote in message
news:a6bdna$6...@dispatch.concentric.net...

Dean Bolton

unread,

Mar 8, 2002, 5:32:56 PM3/8/02

to

When I do that, I get a Type Mismatch: 'Clng' error on the page....

"Mikhail Berlyant" <berl...@yahoo-inc.com> wrote in message

news:Og0Cx6uxBHA.1340@tkmsftngp05...

Dean Bolton

unread,

Mar 8, 2002, 5:46:58 PM3/8/02

to

Where I was collecting the variable, was a set of single quotes inside of
the double quopes, hence the problem.

Thanks,
Dean

"Mikhail Berlyant" <berl...@yahoo-inc.com> wrote in message

news:uHt9gDvxBHA.2324@tkmsftngp03...

Aaron Bertrand [MVP]

unread,

Mar 8, 2002, 5:45:51 PM3/8/02

to

> ArrMatID = Split(request.form("Attach"),", ")
>
> Is there anything in the Split function that could be causing this?

NO, split() does not add single quotes for fun. Observe the following:

response.Write request.form("Attach")

You likely have something wrong with your <input type=text> or whatever is
passing the comma-separated list, e.g.

www.aspfaq.com