VB SQL Error?

Set rs = Server.CreateObject("ADODB.Recordset")
rsstatm = "SELECT * FROM F4105C"
rs.Open rsstatm, ConnSQL

rs.movefirst

do while not rs.eof


Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1statm = "SELECT * FROM F4105 WHERE COITM =" rS("COITM")" AND COMCU =
"rS("COMCU")" AND COLEDG = '02'" AND COLEDG = '02'"
rs1.Open rs1statm, ConnAS400

//======================================================================
// Why this SQL Statement is error? "SELECT * FROM F4105 WHERE COITM ="
rS("COITM")" ...?
// Where is the error? How to solve?
//======================================================================



rs.movenext
loop

rs.close
Set rs = Nothing

Viatcheslav
Wed Apr 28 03:52:26 CDT 2004

What is real SQL (value of rs1statm)?

May be query should be:

rs1statm = "SELECT * FROM F4105 WHERE COITM =" & rS("COITM") & " AND COMCU =
" & rS("COMCU") & " AND COLEDG = '02'"

Do not forget to close rs1 before rs.movenext

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Samuellai" <samuellai@ajikl.com.my> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:u5XxzfPLEHA.3428@TK2MSFTNGP09.phx.gbl...
> Set rs = Server.CreateObject("ADODB.Recordset")
> rsstatm = "SELECT * FROM F4105C"
> rs.Open rsstatm, ConnSQL
>
> rs.movefirst
>
> do while not rs.eof
>
>
> Set rs1 = Server.CreateObject("ADODB.Recordset")
> rs1statm = "SELECT * FROM F4105 WHERE COITM =" rS("COITM")" AND COMCU =
> "rS("COMCU")" AND COLEDG = '02'" AND COLEDG = '02'"
> rs1.Open rs1statm, ConnAS400
>
> //======================================================================
> // Why this SQL Statement is error? "SELECT * FROM F4105 WHERE COITM ="
> rS("COITM")" ...?
> // Where is the error? How to solve?
> //======================================================================
>
>
>
> rs.movenext
> loop
>
> rs.close
> Set rs = Nothing
>
>

Bob
Wed Apr 28 05:53:56 CDT 2004

Samuellai wrote:
> Set rs = Server.CreateObject("ADODB.Recordset")
> rsstatm = "SELECT * FROM F4105C"
> rs.Open rsstatm, ConnSQL
>
This following line is not needed. the recordset will already be pointed at
the first record immediately after it is opened:
************************
> rs.movefirst
************************
>
> do while not rs.eof
>
>
> Set rs1 = Server.CreateObject("ADODB.Recordset")
> rs1statm = "SELECT * FROM F4105 WHERE COITM =" rS("COITM")" AND
> COMCU = "rS("COMCU")" AND COLEDG = '02'" AND COLEDG = '02'"
> rs1.Open rs1statm, ConnAS400
>
>
>
> //======================================================================
> // Why this SQL Statement is error? "SELECT * FROM F4105 WHERE
> COITM =" rS("COITM")" ...? // Where is the error? How to solve?
> //======================================================================
>

Because your AS400 database knows nothing about rs(...). You need to
concatenate the values contained in the recordset fields, not the references
to the recordset fields.

Try this instead:
rs1statm = "SELECT * FROM F4105 WHERE COITM =" & _
rS("COITM") & "AND COMCU =" & rS("COMCU") & _
" AND COLEDG = '02'" AND COLEDG = '02'"

'The following line is for debugging purposes. Comment it out
'when finished debugging:
'****************************************************
response.write rs1statm & "<BR>"
'****************************************************

'Uncomment the following lines when finished debugging:
'****************************************************
'rs1.Open rs1statm, ConnAS400
' ... all other lines that use rs1
'****************************************************

Your goal when creating dynamic sql statements is to produce a statement
that will run as-is in your database. That is the reason for the
Response.Write: you need to see the resulting statement to see if it will
run in your database. When you run this code with the response.write line
uncommented, you will see a series of statements written to the browser
window. If you have created them correctly, you will be able to copy and
paste them (one-at-a-time, of course) from the browser window into a strSQL
session in your AS400 and run them without modification.

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"