Problem w/ VBScript running against SQL Server 2000

I have been getting the following error when I run some VBScript code on an
ASP page that modifies values in a SQL Server 2000 database:

Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near
','. /manProds.asp, line 185

POST Data:
bayID=105700112&bayName=Another+New+Test+Today&baycat=Laptops&baysubcat=Database+SW&bayPicture=Illustration+Not+Available&bayPrice=35.58&bayBriefDesc=another%0D%0A%09%09%09&bayFullDesc=Product+Descrip . . .

This same code worked fine w/ SQL7, but for some reason won't work w/
SQL2000. The code is as follows:

<%
IF addProduct <> "" THEN

sqlString = "INSERT INTO bayServ " &_
"( bay_name, bay_cat, bay_subcat, bay_picture, " &_
"bay_price, bay_briefDesc, " &_
"bay_fullDesc, bay_status ) VALUES ( " &_
" '" & fixQuotes( bayName ) & "', " &_
" '" & ( bayCat ) & "', " &_
" '" & ( baySubCat ) & "', " &_
" '" & fixQuotes( bayPicture ) & "', " &_
bayPrice & ", " &_
" '" & fixQuotes( bayBriefDesc ) & "', " &_
" '" & fixQuotes( bayFullDesc ) & "', " &_
bayStatus & " )"

bayDB.Execute sqlString
%>

...(redacted web code)

<%
END IF

IF updateProduct <> "" THEN
sqlString = "UPDATE bayServ SET " &_
"bay_name='" & fixQuotes( bayName ) & "'," &_
"bay_picture='" & fixQuotes( bayPicture ) & "'," &_
"bay_price=" & cCUR( bayPrice ) & "," &_
"bay_cat='" & fixQuotes( bayCat ) & "'," &_
"bay_subcat='" & fixQuotes( baySubCat ) & "'," &_
"bay_briefDesc='" & fixQuotes( bayBriefDesc ) & "'," &_
"bay_fullDesc='" & fixQuotes( bayFullDesc ) & "'," &_
"bay_status=" & bayStatus &_
"WHERE bay_id=" & bayID

bayDB.Execute sqlString
%>

The last bayDB.execute.sqlString is line 185. I've included the addProduct
snippet because it works just fine, but maybe the problem is passed from
there--I don't know. From what the error notes, I'm guessing the problem
might be w/ the variable bayPrice, which is defined as type 'money.' But
everything I've done to try to fix it returns the same error. The post data
string suggests it might be a problem w/ bayBriefDesc, but I can't see any
problem w/ how I have it set up in the code (and after all, it works fine in
fine in addProduct). The FixQuotes function is as follows:

FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION

I can't figure out what exactly the problem is, and would much appreciate it
if someone could review my code and let me know what I might have done wrong
or what I could possibly do to fix it. TIA...

ba

Bob
Mon Jul 23 17:28:44 CDT 2007

BA wrote:
>
> IF updateProduct <> "" THEN
> sqlString = "UPDATE bayServ SET " &_
> "bay_name='" & fixQuotes( bayName ) & "'," &_
> "bay_picture='" & fixQuotes( bayPicture ) & "'," &_
> "bay_price=" & cCUR( bayPrice ) & "," &_
> "bay_cat='" & fixQuotes( bayCat ) & "'," &_
> "bay_subcat='" & fixQuotes( baySubCat ) & "'," &_
> "bay_briefDesc='" & fixQuotes( bayBriefDesc ) & "'," &_
> "bay_fullDesc='" & fixQuotes( bayFullDesc ) & "'," &_
> "bay_status=" & bayStatus &_
> "WHERE bay_id=" & bayID
>
> bayDB.Execute sqlString
> %>
>
> The last bayDB.execute.sqlString is line 185.

There was no need to post the whole thing.
While it does help to see the code that is supposed to result in a sql
statement, we cannot debug a sql statement without knowing what the result
of that code is. Write the contents of sqlString to Response (Response.Write
sqlString) and look at the statement you are trying to execute in the
browser window. If the problem is not obvious to you, open Query Analyzer,
copy the statement from the browser window into QA and try to execute it. If
it is correctly built, it should run without modification. If you still
can't figure it out post the statement here.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection (yes, even the quote fixing can be defeated):
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures,
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

The other benefit of using parameters is that you can ditch that FixQuotes
function.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Mon Jul 23 17:48:05 CDT 2007

I remember you--you told me how to debug a similar problem I had a few years
ago (and yes, it worked fine). I'll give what you are suggesting a try, but
that's also why I included the post data string; I just couldn't figure out
from it what exactly the problem was. unfortunately, I don't work w/ this
everyday.

I have done a number of things to limit sql injection attacks, such as
setting up (and limiting) a special acct. for public user access and running
the isNumeric function on queries against id numbers and other fields that
are defined as int, bigint. numeric, etc. The page I have here is actually an
admin-access only page, which requires a windows-encripted login to access
(no public access via IUSR). I'm sure there are other things I can do and
I'll give the articles you note a look.

Thanks...

ba

"Bob Barrows [MVP]" wrote:

> BA wrote:
> >
> > IF updateProduct <> "" THEN
> > sqlString = "UPDATE bayServ SET " &_
> > "bay_name='" & fixQuotes( bayName ) & "'," &_
> > "bay_picture='" & fixQuotes( bayPicture ) & "'," &_
> > "bay_price=" & cCUR( bayPrice ) & "," &_
> > "bay_cat='" & fixQuotes( bayCat ) & "'," &_
> > "bay_subcat='" & fixQuotes( baySubCat ) & "'," &_
> > "bay_briefDesc='" & fixQuotes( bayBriefDesc ) & "'," &_
> > "bay_fullDesc='" & fixQuotes( bayFullDesc ) & "'," &_
> > "bay_status=" & bayStatus &_
> > "WHERE bay_id=" & bayID
> >
> > bayDB.Execute sqlString
> > %>
> >
> > The last bayDB.execute.sqlString is line 185.
>
> There was no need to post the whole thing.
> While it does help to see the code that is supposed to result in a sql
> statement, we cannot debug a sql statement without knowing what the result
> of that code is. Write the contents of sqlString to Response (Response.Write
> sqlString) and look at the statement you are trying to execute in the
> browser window. If the problem is not obvious to you, open Query Analyzer,
> copy the statement from the browser window into QA and try to execute it. If
> it is correctly built, it should run without modification. If you still
> can't figure it out post the statement here.
>
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection (yes, even the quote fixing can be defeated):
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:
> http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
>
> Personally, I prefer using stored procedures,
> http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&
>
> The other benefit of using parameters is that you can ditch that FixQuotes
> function.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob
Mon Jul 23 18:30:51 CDT 2007

BA wrote:
> I remember you--you told me how to debug a similar problem I had a
> few years
> ago (and yes, it worked fine). I'll give what you are suggesting a
> try, but
> that's also why I included the post data string;

How does that help? i don't have your database so there is no way I can run
your sql statement against it to see what's wrong. Oh! you're expecting me
to run your code on my web server to see the result? Why should I go to that
trouble when it's a matter of seconds for you to do it?

> I just couldn't
> figure out
> from it what exactly the problem was. unfortunately, I don't work w/
> this
> everyday.
>
> I have done a number of things to limit sql injection attacks, such as
> setting up (and limiting) a special acct. for public user access and
> running
> the isNumeric function on queries against id numbers and other fields
> that
> are defined as int, bigint. numeric, etc. The page I have here is
> actually an
> admin-access only page, which requires a windows-encripted login to
> access (no public access via IUSR). I'm sure there are other things I
> can do and
> I'll give the articles you note a look.
>
I think you will find when you read those articles that the measures you
have taken, while effective against lazy hackers, will not deter somebody
determined to penetrate your defenses. Parameters will stop sql injection
cold. Of course, there are other techniques you need to guard against, but
using dynamic sql leaves a large door ajar.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Mon Jul 23 19:38:02 CDT 2007

I think you misunderstood me. What I was saying was that I included the post
data string so that someone who works with this all the time (I'm a network
engineer and only got involved in this because I wrote the original code 7
years ago) could perhaps look at it and tell what the problem is. There isn't
much diff between this and what you get from response.write. I certainly
wasn't intending for anyone to try to recreate the entire scenario.

I did try it with response.write, and it returns much the same info:
________________________________________________________________
UPDATE bayServ SET bay_name='a new test',bay_picture='Illustration Not
Available',bay_price=0 ,bay_cat='Software',bay_subcat='Database
SW',bay_briefDesc='Not Listed ',bay_fullDesc='Product Description Not
Available ',bay_status=1WHERE bay_id=105700117, 105700117

a new test was updated in the database
________________________________________________________________
i.e., it indicates a successful update. I think I'll need to go back and
look at how all the variables are declared and how the fields are defined in
sql, along w/ some articles on the changes in this area over the last few
years. I just haven't worked w/ this stuff much in the intervening years and
have focused more on my area.

ba

"Bob Barrows [MVP]" wrote:

> BA wrote:
> > I remember you--you told me how to debug a similar problem I had a
> > few years
> > ago (and yes, it worked fine). I'll give what you are suggesting a
> > try, but
> > that's also why I included the post data string;
>
> How does that help? i don't have your database so there is no way I can run
> your sql statement against it to see what's wrong. Oh! you're expecting me
> to run your code on my web server to see the result? Why should I go to that
> trouble when it's a matter of seconds for you to do it?
>
> > I just couldn't
> > figure out
> > from it what exactly the problem was. unfortunately, I don't work w/
> > this
> > everyday.
> >
> > I have done a number of things to limit sql injection attacks, such as
> > setting up (and limiting) a special acct. for public user access and
> > running
> > the isNumeric function on queries against id numbers and other fields
> > that
> > are defined as int, bigint. numeric, etc. The page I have here is
> > actually an
> > admin-access only page, which requires a windows-encripted login to
> > access (no public access via IUSR). I'm sure there are other things I
> > can do and
> > I'll give the articles you note a look.
> >
> I think you will find when you read those articles that the measures you
> have taken, while effective against lazy hackers, will not deter somebody
> determined to penetrate your defenses. Parameters will stop sql injection
> cold. Of course, there are other techniques you need to guard against, but
> using dynamic sql leaves a large door ajar.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob
Mon Jul 23 19:57:57 CDT 2007

BA wrote:
> I think you misunderstood me. What I was saying was that I included
> the post data string so that someone who works with this all the time
> (I'm a network engineer and only got involved in this because I wrote
> the original code 7 years ago) could perhaps look at it and tell what
> the problem is. There isn't much diff between this and what you get
> from response.write.

I beg to differ. There definitely is a difference. One look at the sql
statement and a couple problems screamed out at me, one of which I
definitely would not have seen from just looking at the code.

> I certainly wasn't intending for anyone to try
> to recreate the entire scenario.
>
> I did try it with response.write, and it returns much the same info:
> ________________________________________________________________
> UPDATE bayServ SET bay_name='a new test',bay_picture='Illustration Not
> Available',bay_price=0 ,bay_cat='Software',bay_subcat='Database
> SW',bay_briefDesc='Not Listed ',bay_fullDesc='Product Description Not
> Available ',bay_status=1WHERE bay_id=105700117, 105700117
>
> a new test was updated in the database
> ________________________________________________________________
> i.e., it indicates a successful update. I think I'll need to go back
> and look at how all the variables are declared and how the fields are
> defined in sql, along w/ some articles on the changes in this area
> over the last few years. I just haven't worked w/ this stuff much in
> the intervening years and have focused more on my area.
>
You're saying you ran the code and it works now? With that sql statement? I
don't think so. Try it yourself in Query Analyzer.

The problems I see are:

1. You need a space between "bay_status=1" and "WHERE ... "

That's easily fixed by changing this:
"WHERE bay_id=" & bayID
to
" WHERE bay_id=" & bayID

2. The WHERE clause itself cannot work. "WHERE bay_id=105700117, 105700117"
is illegal syntax. The problem is, I see nothing in the code you provided to
cause bayID to contain "105700117, 105700117"



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Tue Jul 24 07:06:05 CDT 2007

The field bayID is an identity seed that is automatically generated and
incremented for each instance. I had already tried it w/ a space before the
WHERE clause, but it still returns the same error on execute. I tried it
again and same deal:
____________________________________________________________________
UPDATE bayServ SET bay_name='test 24',bay_picture='Illustration Not
Available',bay_price=24.99,bay_cat='Software',bay_subcat='Database
SW',bay_briefDesc='test24 ',bay_fullDesc='test24 full description
',bay_status=1 WHERE bay_id=105700108, 105700108

test 24 was updated in the database
____________________________________________________________________

From what I recall, if the update is unsuccessful, it should never return
that last line, and when I run it w/ execute, it doesn't. Now you see why I'm
perplexed. I think I need to go back and look at everything again, esp. since
I will likely be modifying the code to account for sql injection anyway.
Since this page is already secured via windows encrypted login it isn't so
much an issue, but for the public pages it will be.

Thanks...

ba


"Bob Barrows [MVP]" wrote:

> BA wrote:
> > I think you misunderstood me. What I was saying was that I included
> > the post data string so that someone who works with this all the time
> > (I'm a network engineer and only got involved in this because I wrote
> > the original code 7 years ago) could perhaps look at it and tell what
> > the problem is. There isn't much diff between this and what you get
> > from response.write.
>
> I beg to differ. There definitely is a difference. One look at the sql
> statement and a couple problems screamed out at me, one of which I
> definitely would not have seen from just looking at the code.
>
> > I certainly wasn't intending for anyone to try
> > to recreate the entire scenario.
> >
> > I did try it with response.write, and it returns much the same info:
> > ________________________________________________________________
> > UPDATE bayServ SET bay_name='a new test',bay_picture='Illustration Not
> > Available',bay_price=0 ,bay_cat='Software',bay_subcat='Database
> > SW',bay_briefDesc='Not Listed ',bay_fullDesc='Product Description Not
> > Available ',bay_status=1WHERE bay_id=105700117, 105700117
> >
> > a new test was updated in the database
> > ________________________________________________________________
> > i.e., it indicates a successful update. I think I'll need to go back
> > and look at how all the variables are declared and how the fields are
> > defined in sql, along w/ some articles on the changes in this area
> > over the last few years. I just haven't worked w/ this stuff much in
> > the intervening years and have focused more on my area.
> >
> You're saying you ran the code and it works now? With that sql statement? I
> don't think so. Try it yourself in Query Analyzer.
>
> The problems I see are:
>
> 1. You need a space between "bay_status=1" and "WHERE ... "
>
> That's easily fixed by changing this:
> "WHERE bay_id=" & bayID
> to
> " WHERE bay_id=" & bayID
>
> 2. The WHERE clause itself cannot work. "WHERE bay_id=105700117, 105700117"
> is illegal syntax. The problem is, I see nothing in the code you provided to
> cause bayID to contain "105700117, 105700117"
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob
Tue Jul 24 08:54:37 CDT 2007

bayID is a variable in vbscript. Based on the result of that response.write,
this variable seems to contain "105700108, 105700108", not "105700108" which
is probably what it should contain.
bay_id is the identity field in your database.

Frankly, I don't see how this update statement could be working. If you have
an "on error resume next" statement anywhere in the code on that page,
comment it out so real errors are not masked.

BA wrote:
> The field bayID is an identity seed that is automatically generated
> and incremented for each instance. I had already tried it w/ a space
> before the WHERE clause, but it still returns the same error on
> execute. I tried it again and same deal:
> ____________________________________________________________________
> UPDATE bayServ SET bay_name='test 24',bay_picture='Illustration Not
> Available',bay_price=24.99,bay_cat='Software',bay_subcat='Database
> SW',bay_briefDesc='test24 ',bay_fullDesc='test24 full description
> ',bay_status=1 WHERE bay_id=105700108, 105700108
>
> test 24 was updated in the database
> ____________________________________________________________________
>
> From what I recall, if the update is unsuccessful, it should never
> return that last line, and when I run it w/ execute, it doesn't. Now
> you see why I'm perplexed. I think I need to go back and look at
> everything again, esp. since I will likely be modifying the code to
> account for sql injection anyway. Since this page is already secured
> via windows encrypted login it isn't so much an issue, but for the
> public pages it will be.
>
> Thanks...
>
> ba
>
>
> "Bob Barrows [MVP]" wrote:
>
>> BA wrote:
>>> I think you misunderstood me. What I was saying was that I included
>>> the post data string so that someone who works with this all the
>>> time (I'm a network engineer and only got involved in this because
>>> I wrote
>>> the original code 7 years ago) could perhaps look at it and tell
>>> what
>>> the problem is. There isn't much diff between this and what you get
>>> from response.write.
>>
>> I beg to differ. There definitely is a difference. One look at the
>> sql statement and a couple problems screamed out at me, one of which
>> I definitely would not have seen from just looking at the code.
>>
>>> I certainly wasn't intending for anyone to try
>>> to recreate the entire scenario.
>>>
>>> I did try it with response.write, and it returns much the same info:
>>> ________________________________________________________________
>>> UPDATE bayServ SET bay_name='a new test',bay_picture='Illustration
>>> Not Available',bay_price=0 ,bay_cat='Software',bay_subcat='Database
>>> SW',bay_briefDesc='Not Listed ',bay_fullDesc='Product Description
>>> Not Available ',bay_status=1WHERE bay_id=105700117, 105700117
>>>
>>> a new test was updated in the database
>>> ________________________________________________________________
>>> i.e., it indicates a successful update. I think I'll need to go back
>>> and look at how all the variables are declared and how the fields
>>> are defined in sql, along w/ some articles on the changes in this
>>> area
>>> over the last few years. I just haven't worked w/ this stuff much in
>>> the intervening years and have focused more on my area.
>>>
>> You're saying you ran the code and it works now? With that sql
>> statement? I don't think so. Try it yourself in Query Analyzer.
>>
>> The problems I see are:
>>
>> 1. You need a space between "bay_status=1" and "WHERE ... "
>>
>> That's easily fixed by changing this:
>> "WHERE bay_id=" & bayID
>> to
>> " WHERE bay_id=" & bayID
>>
>> 2. The WHERE clause itself cannot work. "WHERE bay_id=105700117,
>> 105700117" is illegal syntax. The problem is, I see nothing in the
>> code you provided to cause bayID to contain "105700117, 105700117"
>>
>>
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Tue Jul 24 10:36:03 CDT 2007

true, but bayID is always assigned the value of bay_id. I can't tell you why
it lists twice in the response.write--there is only 1 call to that variable,
and as you can see, it isn't assigned explicitly by addProduct (which always
returns the correct values, btw). It has to be something w/ how the field is
defined in sql, since the same thing works fine w/ sql7 and IIS4--what it was
originally written for.

I'm not going to spend any more time on this, esp. since I'm likely going to
change the code anyway. I just thought if there was an obvious error in the
code that someone more experienced w/ this could see, great. But obviously
there isn't.

Thanks...

ba

"Bob Barrows [MVP]" wrote:

> bayID is a variable in vbscript. Based on the result of that response.write,
> this variable seems to contain "105700108, 105700108", not "105700108" which
> is probably what it should contain.
> bay_id is the identity field in your database.
>
> Frankly, I don't see how this update statement could be working. If you have
> an "on error resume next" statement anywhere in the code on that page,
> comment it out so real errors are not masked.
>
> BA wrote:
> > The field bayID is an identity seed that is automatically generated
> > and incremented for each instance. I had already tried it w/ a space
> > before the WHERE clause, but it still returns the same error on
> > execute. I tried it again and same deal:
> > ____________________________________________________________________
> > UPDATE bayServ SET bay_name='test 24',bay_picture='Illustration Not
> > Available',bay_price=24.99,bay_cat='Software',bay_subcat='Database
> > SW',bay_briefDesc='test24 ',bay_fullDesc='test24 full description
> > ',bay_status=1 WHERE bay_id=105700108, 105700108
> >
> > test 24 was updated in the database
> > ____________________________________________________________________
> >
> > From what I recall, if the update is unsuccessful, it should never
> > return that last line, and when I run it w/ execute, it doesn't. Now
> > you see why I'm perplexed. I think I need to go back and look at
> > everything again, esp. since I will likely be modifying the code to
> > account for sql injection anyway. Since this page is already secured
> > via windows encrypted login it isn't so much an issue, but for the
> > public pages it will be.
> >
> > Thanks...
> >
> > ba
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> BA wrote:
> >>> I think you misunderstood me. What I was saying was that I included
> >>> the post data string so that someone who works with this all the
> >>> time (I'm a network engineer and only got involved in this because
> >>> I wrote
> >>> the original code 7 years ago) could perhaps look at it and tell
> >>> what
> >>> the problem is. There isn't much diff between this and what you get
> >>> from response.write.
> >>
> >> I beg to differ. There definitely is a difference. One look at the
> >> sql statement and a couple problems screamed out at me, one of which
> >> I definitely would not have seen from just looking at the code.
> >>
> >>> I certainly wasn't intending for anyone to try
> >>> to recreate the entire scenario.
> >>>
> >>> I did try it with response.write, and it returns much the same info:
> >>> ________________________________________________________________
> >>> UPDATE bayServ SET bay_name='a new test',bay_picture='Illustration
> >>> Not Available',bay_price=0 ,bay_cat='Software',bay_subcat='Database
> >>> SW',bay_briefDesc='Not Listed ',bay_fullDesc='Product Description
> >>> Not Available ',bay_status=1WHERE bay_id=105700117, 105700117
> >>>
> >>> a new test was updated in the database
> >>> ________________________________________________________________
> >>> i.e., it indicates a successful update. I think I'll need to go back
> >>> and look at how all the variables are declared and how the fields
> >>> are defined in sql, along w/ some articles on the changes in this
> >>> area
> >>> over the last few years. I just haven't worked w/ this stuff much in
> >>> the intervening years and have focused more on my area.
> >>>
> >> You're saying you ran the code and it works now? With that sql
> >> statement? I don't think so. Try it yourself in Query Analyzer.
> >>
> >> The problems I see are:
> >>
> >> 1. You need a space between "bay_status=1" and "WHERE ... "
> >>
> >> That's easily fixed by changing this:
> >> "WHERE bay_id=" & bayID
> >> to
> >> " WHERE bay_id=" & bayID
> >>
> >> 2. The WHERE clause itself cannot work. "WHERE bay_id=105700117,
> >> 105700117" is illegal syntax. The problem is, I see nothing in the
> >> code you provided to cause bayID to contain "105700117, 105700117"
> >>
> >>
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob
Tue Jul 24 10:52:52 CDT 2007

BA wrote:
> true, but bayID is always assigned the value of bay_id.

Huh? At what point? I don't see the bayID value doubled up in the POST data
you showed ... Show us how you are assigning a value to the bayID variable.

< I can't tell
> you why it lists twice in the response.write--there is only 1 call to
> that variable, and as you can see, it isn't assigned explicitly by
> addProduct (which always returns the correct values, btw).

Unless addProduct and updateProduct can both be true in the same submission,
then that's not relevant.
> It has to
> be something w/ how the field is defined in sql, since the same thing
> works fine w/ sql7 and IIS4--what it was originally written for.

No it doesn't. At this point there is no connection to the database
involved. This is strictly vbscript code generating string and Writing it to
Response.

Also, AFAIK there are no compatability issues between SQL 7 and SQL 2000
that could cause the symptoms you are seeing.

>
> I'm not going to spend any more time on this, esp. since I'm likely
> going to change the code anyway. I just thought if there was an
> obvious error in the code that someone more experienced w/ this could
> see, great. But obviously there isn't.
>

Well, that's your choice, but I foresee problems down the road if you don't
get to the bottom of this
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Tue Jul 24 20:26:02 CDT 2007

bayID is assigned the value of bay_id in another file called updProds.asp,
and it does pass the value correctly. I didn't have time to look at it
earlier, but I did look at updProds.asp when I got back and found something
else: somewhere along the way, someone decided they wanted to be able to
change the value of bay_id (via updProds.asp), but they apparently didn't
know it was a seed field. it turns out that was the problem. once I set it
back to a constant and ran an update, it worked fine.

I'll still probably change a lot of it to eliminate the possibility of sql
injection, but at least now it is somewhat back to where it was when I
originally wrote it years ago.

thanks...

ba


"Bob Barrows [MVP]" wrote:

> BA wrote:
> > true, but bayID is always assigned the value of bay_id.
>
> Huh? At what point? I don't see the bayID value doubled up in the POST data
> you showed ... Show us how you are assigning a value to the bayID variable.
>
> < I can't tell
> > you why it lists twice in the response.write--there is only 1 call to
> > that variable, and as you can see, it isn't assigned explicitly by
> > addProduct (which always returns the correct values, btw).
>
> Unless addProduct and updateProduct can both be true in the same submission,
> then that's not relevant.
> > It has to
> > be something w/ how the field is defined in sql, since the same thing
> > works fine w/ sql7 and IIS4--what it was originally written for.
>
> No it doesn't. At this point there is no connection to the database
> involved. This is strictly vbscript code generating string and Writing it to
> Response.
>
> Also, AFAIK there are no compatability issues between SQL 7 and SQL 2000
> that could cause the symptoms you are seeing.
>
> >
> > I'm not going to spend any more time on this, esp. since I'm likely
> > going to change the code anyway. I just thought if there was an
> > obvious error in the code that someone more experienced w/ this could
> > see, great. But obviously there isn't.
> >
>
> Well, that's your choice, but I foresee problems down the road if you don't
> get to the bottom of this
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>