ERROR...

[A1Pat]

hello all,

I'm getting an error that I can seem to find its location anywhere, maybe you can help me.

My code:
sql = "SELECT ProductName FROM tblSupports"
set rsFind = objConn.Execute(sql)
Do While Not rsFind.EOF
sqlX = "SELECT ProductID FROM tblProducts WHERE ShortName = '" & rsFind("ProductName") & "'"
set rsMatch = objConn.Execute(sqlX)
do while not rsMatch.eof
sql = "UPDATE tblSupports SET ProductID = " & rsMatch("ProductID") & " WHERE ProductName = '" & rsFind("ProductName") & "'"
objConn.Execute sql
rsMatch.movenext
loop
rsFind.MoveNext
Loop

the ERROR:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.

Thanks!

 

[A1Pat]

By the way, I'm also interested in how to combine/shorten these SQLs together.

Thanks for any help!

 

[Revera]

you don't say what database you are using so if it is Access, this suggestion is straight forward enough, if it is SQL or Oracle or other you might have to do a bit of work to get Access to talk to your database before you can use this

On the combining question, you are looking to INNER JOIN SQL commands

These can get complicated if you are a novice, so a handy way is to use something like access to build them for you

Use the query builder in Access to produce the dataset you are looking for. Then copy and paste the SQL it generates in the SQL view into your code. you will need to do a little formatting in your ASP editor to get it in but the SQL you are looking for will be there.

 

[Revera]

for each of the 3 times you create the SQL to be executed, output it to the screen using a response.write before you execute it to see what the actual SQL statement the system is trying to execute is (Make sure buffering is turned off on the server during this testing to see the output)

We can't tell which one is failing from the error provided. seeing what statement is actually failing might throw some light on the error

And I now see you are using MS SQL so my previous comment might not be as easy to implement

 

[A1Pat]

Sorry, i'm utilizing SQL 2000 db. thanks!

 

[Lothario]

Try something like this...

sql = "SELECT a.ProductID, b.ProductName FROM tblProducts a INNER JOIN tblSupports b on a.ProductID=b.ProductID WHERE a.ShortName='"&rsFind("ProductName")&"' "

set rsFind = objConn.Execute(sql)

Do While Not rsFind.EOF
            sql1 = "UPDATE tblSupports SET ProductID = "&rsFind("ProductID")&" WHERE ProductName = '"&rsFind("ProductName")&"' "
        objConn.Execute sql1
    rsFind.MoveNext
Loop

-L

 

[Lothario]

I meant this

sql = "SELECT [b]a.ProductID as prodid, b.ProductName as prodname[/b] FROM tblProducts a INNER JOIN tblSupports b on a.ProductID=b.ProductID WHERE a.ShortName='"&rsFind("ProductName")&"' "

set rsFind = objConn.Execute(sql)

Do While Not rsFind.EOF
            [b]sql1 = "UPDATE tblSupports SET ProductID = "&rsFind("prodid")&" WHERE ProductName = '"&rsFind("prodname")&"' "[/b]
        objConn.Execute sql1
    rsFind.MoveNext
Loop

-L