[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1091
  • Last Modified:

Passing variable from vbscript to ADO SQL statement

Hi Everyone,

I have a vbscript that has some ADO and SQL in it  set to query an Oracle database.  The first part of my script creates a value which I then want to pass to a SQL statement which in turn should be able to query the database using the value.

However I am unable to pass the value created to the SQL query.  The error message I receive is
"Either BOF or EOF is True, or the current record has been deleted" - but I know for sure the record exists.

Here's a snippet of my code:
------------------------------------------------------------------------------
set readfile = filesys.OpenTextFile("c:\readRTV\06_25_rtv.txt")
do while readfile.AtEndOfStream <> true
    value1 = readfile.ReadLine
    value2 = Mid(value1, 59,12)
loop
MsgBox "The value you want to pass is" & value2
Set Con = CreateObject("ADODB.Connection")
strCon = <<datasource opens successfully>>
Con.Open strCon

set rs=CreateObject("ADODB.recordset")
rs.CursorType = adOpenStatic
rs.Open "SELECT MTADCOD FROM F_PR_CPTK_MAP WHERE MAP_CPTK_MC = MTADCOD IN ('value2')", strCon
Set stream = CreateObject("ADODB.Stream")
stream.Open
vRecordset = rs.GetString
stream.WriteText vRecordset
stream.saveToFile "c:\result.txt"
stream.close
rs.close
Con.Close
------------------------------------------------------------------------------

Anybody know how I can successfully pass "value2" to the above SQL statement?
Thanks to anyone who can help!
0
PatternRecognitionAdmin
Asked:
PatternRecognitionAdmin
1 Solution
 
Arthur_WoodCommented:
what is this line:

rs.Open "SELECT MTADCOD FROM F_PR_CPTK_MAP WHERE MAP_CPTK_MC = MTADCOD IN ('value2')", strCon


trying to do?    this part (WHERE MAP_CPTK_MC = MTADCOD IN ('value2') ) is not correctly formatted.

You can either have :  WHERE MAP_CPTK_MC = MTADCOD

or :  WHERE MAP_CPTK_MC ('value2')",

but NOT both at the same time.  Also, you loop through ALL of the records in  your .txt filem, and only make use of the very LAST value2, is that what you want?

I would think the code should be something like:

     Set Con = CreateObject("ADODB.Connection")
     strCon = <<datasource opens successfully>>
     Con.Open strCon
set readfile = filesys.OpenTextFile("c:\readRTV\06_25_rtv.txt")
do while readfile.AtEndOfStream <> true
    value1 = readfile.ReadLine
    value2 = Mid(value1, 59,12)

     MsgBox "The value you want to pass is" & value2
     set rs=CreateObject("ADODB.recordset")
     rs.CursorType = adOpenStatic
     rs.Open "SELECT MTADCOD FROM F_PR_CPTK_MAP WHERE MAP_CPTK_MC = '" & value2 & "'", strCon
     Set stream = CreateObject("ADODB.Stream")
     stream.Open
     vRecordset = rs.GetString
     stream.WriteText vRecordset
     stream.saveToFile "c:\result.txt"
     stream.close
     rs.close
loop
Con.Close

this will then use EVERY value2 from the txt file, not just the last one.  Also is Value2 a NUMBER, or Text?  

AW
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now