Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset syntax issue 1

Status
Not open for further replies.
tradle

tradle

Programmer
Jan 7, 2004
94
US
I'm getting a syntax issue - could anyone out there check this out? I borrowed from a different thread, so some of the syntax may make no sense.

Code: 
Function ReplaceTagValues()

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, source, fc
Set fso = CreateObject("Scripting.FileSystemObject")
set source = fso.GetFolder("f:\temp")
set fc = source.Files

Dim db

db = "Advent_Reports"

'Create Connection
set con = CreateObject("ADODB.Connection")
strCon = "driver={SQL SERVER};server=OHCLESQL4005;uid=user;pwd=pwd;database=" & db & ""
con.Open strCon

'Create Command
set doit = CreateObject("ADODB.Command")
set doit.ActiveConnection = con

	sql = "select PKG_SEQUENCE as Pkg, STMT_SEQUENCE as Seq, MAIL_TO1 as mail1, MAIL_TO2 as mail2, MAIL_TO3 as mail3, MAIL_TO4 as mail4, MAIL_TO5 as mail5, MAIL_TO6 as mail6, ACCOUNT_ID as portid" & _
        	"from BowneTags"
        
	set rs = CreateObject("ADODB.Recordset")

		rs.Open sql, con
		While Not rs.EOF

			for each file in fc
			Set tsIni2 = FSO.OpenTextFile(file)
			file2create = "f:\temp\step2" & file.Name & "done"
			Set newInifile2 = fso.CreateTextFile(file2Create, True)
	
				Do While Not tsIni2.AtEndOfStream
				    sLine = tags.ReadLine
				    newIniFile2.WriteLine Replace(sLine, "$1", "Pkg")
				    newIniFile2.WriteLine Replace(sLine, "$2", "Seq")
				    newIniFile2.WriteLine Replace(sLine, "$3", "mail1")
				    newIniFile2.WriteLine Replace(sLine, "$4", "mail2")
				    newIniFile2.WriteLine Replace(sLine, "$5", "mail4")
				    newIniFile2.WriteLine Replace(sLine, "$6", "mail5")
				    newIniFile2.WriteLine Replace(sLine, "$7", "mail6")
				    newIniFile2.WriteLine Replace(sLine, "$8", "portid")
				Loop
			Next

		Wend
		rs.MoveNext
		rs.Close
	Set rs = Nothing

Function End

Thanks!
 
Sort by date Sort by votes
Replace
Function End
by this:
End Function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks PHV -

Nothing actually happens - no error, no files produce in f:\temp\step2.

The line that I'm altering is:

"<rdf:Description rdf:about='' xmlns:ncb='//ohclesql1026/ncb/metadata/' ncb:CompTime='COMP_TIME|' ncb:pkgSeq='PKG_SEQUENCE|$1' ncb:StmtSeq='STMT_SEQUENCE|$2' ncb:Mail1='MAIL_TO1|$3' ncb:Mail2='MAIL_TO2|$4' ncb:Mail3='MAIL_TO3|$5' ncb:Mail4='MAIL_TO4|$6' ncb:Mail5='MAIL_TO5|$7' ncb:Mail6='MAIL_TO6|$8' ncb:ZipCode='ZIP_CODE|' ncb:InsMap='INSERT_MAP|YNNNNNNN' ncb:InsId='INSERT_ID|NCIM1Q04 ' ncb:AcctId='ACCOUNT_ID|$9' ncb:paperCde='PAPER_CODE|10001453' ncb:EnvCde='ENV_CODE|10001538' ncb:SpecHand='SPECIAL_HANDLING|N'/>"

for $1-$9 - do I need to put the variables outside of the ' perhaps, and if so, do I need to surround them with some other kind of identifier?
 
Upvote 0 Downvote
PHV -

I added an echo statement and now I get an error -

I have a syntax issue at line 28, char 3 - which I believe is the rs.Open statement.

Can you tell what the issue might be?

Thanks!
 
Upvote 0 Downvote
If a only thing you have changed is the adding of an echo statement, then obviously the syntax error is on that line.
Anyway, in your previously posted code, I guess that the tags.ReadLine should be tsIni2.ReadLine.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
PHV,

Thanks for the catch.

It now fails at line 23 - the sql query.

Code: 
sql = "select PKG_SEQUENCE as Pkg, STMT_SEQUENCE as Seq, MAIL_TO1 as mail1, MAIL_TO2 as mail2, MAIL_TO3 as mail3, MAIL_TO4 as mail4, MAIL_TO5 as mail5, MAIL_TO6 as mail6, ACCOUNT_ID as portid" & _
            "from BowneTags"

Any ideas? Is my connection string correct? I can't find any resources whatsoever in the MS Scripting documentation to be able to validate what I have in the script.

Thanks for your help and patience!
 
Upvote 0 Downvote
Your doit object seems unnecessary.
Your sql string lacks a space before the FROM clause
Anyway you browse your recordset without grabbing the field's values.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
PHV -

Files do produce, but every line in every report is duplicated for the total number of items in the run group. What in the world might cause that behavior?

Thanks,
Tim
 
Upvote 0 Downvote
This is the most recent code:

Code: 
Function ReplaceTagValues()

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, source, fc
Set fso = CreateObject("Scripting.FileSystemObject")
set source = fso.GetFolder("f:\temp")
set fc = source.Files

Dim db

db = "Advent_Reports"

'Create Connection
set con = CreateObject("ADODB.Connection")
strCon = "driver={SQL SERVER};server=OHCLESQL4005;uid=user;pwd=pwd;database=" & db & ""
con.Open strCon

'Create Command
	sql = "select PKG_SEQUENCE as Pkg, STMT_SEQUENCE as Seq, MAIL_TO1 as mail1, MAIL_TO2 as mail2, MAIL_TO3 as mail3, MAIL_TO4 as mail4, MAIL_TO5 as mail5, MAIL_TO6 as mail6, ACCOUNT_ID as portid" & _
        	" from Advent_Reports.dbo.BowneTags "
        
	set rs = CreateObject("ADODB.Recordset")

		rs.Open sql, con
		While Not rs.EOF

			for each file in fc
			Set tsIni2 = FSO.OpenTextFile(file)
			file2create = "f:\temp\step2" & file.Name & "done"
			Set newInifile2 = fso.CreateTextFile(file2Create, True)
	
				Do While Not tsIni2.AtEndOfStream
				    sLine = tsIni2.ReadLine
				    newIniFile2.WriteLine Replace(sLine, "$1", "Pkg")
				    newIniFile2.WriteLine Replace(sLine, "$2", "Seq")
				    newIniFile2.WriteLine Replace(sLine, "$3", "mail1")
				    newIniFile2.WriteLine Replace(sLine, "$4", "mail2")
				    newIniFile2.WriteLine Replace(sLine, "$5", "mail2")
				    newIniFile2.WriteLine Replace(sLine, "$6", "mail4")
				    newIniFile2.WriteLine Replace(sLine, "$7", "mail5")
				    newIniFile2.WriteLine Replace(sLine, "$8", "mail6")
				    newIniFile2.WriteLine Replace(sLine, "$9", "portid")
				Loop
			Next

		Wend
		rs.MoveNext
		rs.Close
	Set rs = Nothing

End Function

Wscript.echo ReplaceTagValues
 
Upvote 0 Downvote
Can you explain what you're trying to do as I can't read into your mind.
Anyway, in your posted script the ADO stuff is needless.
BTW, what is "every report" and "run group" ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
I'm so sorry - this is what I'm trying to do:

1. Query the Advent_Reports.dbo.BowneTags table for the values of PKG_SEQUENCE, STMT_SEQUENCE, and the other 7 fields listed in the query, and place the results in a recordset.

2. Open all files in the f:\temp directory and find each one of 9 variables, called $1-$9, and populate each one as specified in the query.

So, essentially, for the first record, the following line (housed in the first file of the directory):

Code: 
"<rdf:Description rdf:about='' xmlns:ncb='//ohclesql1026/ncb/metadata/' ncb:CompTime='COMP_TIME|' ncb:PkgSeq='PKG_SEQUENCE|$1' ncb:StmtSeq='STMT_SEQUENCE|$2' ncb:Mail1='MAIL_TO1|$3' ncb:Mail2='MAIL_TO2|$4' ncb:Mail3='MAIL_TO3|$5' ncb:Mail4='MAIL_TO4|$6' ncb:Mail5='MAIL_TO5|$7' ncb:Mail6='MAIL_TO6|$8' ncb:ZipCode='ZIP_CODE|' ncb:InsMap='INSERT_MAP|YNNNNNNN' ncb:InsId='INSERT_ID|NCIM1Q04                                                                         ' ncb:AcctId='ACCOUNT_ID|$9' ncb:PaperCde='PAPER_CODE|10001453' ncb:EnvCde='ENV_CODE|10001538' ncb:SpecHand='SPECIAL_HANDLING|N'/>"

would change to:

Code: 
"<rdf:Description rdf:about='' xmlns:ncb='//ohclesql1026/ncb/metadata/' ncb:CompTime='COMP_TIME|' ncb:PkgSeq='PKG_SEQUENCE|0000001' ncb:StmtSeq='STMT_SEQUENCE|0000001' ncb:Mail1='MAIL_TO1|Some Company Name' ncb:Mail2='MAIL_TO2|123 Some Street' ncb:Mail3='MAIL_TO3|Suite 1000' ncb:Mail4='MAIL_TO4|Cleveland,' ncb:Mail5='MAIL_TO5|Ohio' ncb:Mail6='MAIL_TO6|44115' ncb:ZipCode='ZIP_CODE|' ncb:InsMap='INSERT_MAP|YNNNNNNN' ncb:InsId='INSERT_ID|NCIM1Q04                                                                         ' ncb:AcctId='ACCOUNT_ID|cn000001' ncb:PaperCde='PAPER_CODE|10001453' ncb:EnvCde='ENV_CODE|10001538' ncb:SpecHand='SPECIAL_HANDLING|N'/>"

Then, save the file and iterate through the rest of the files in the directory for the appropriate values in the recordset.
 
Upvote 0 Downvote
I appear to be stuck in an infinite loop - do I need to move my Next command?
 
Upvote 0 Downvote
Provided you're only interested in the first row of your query, try something like this:
If Not rs.EOF
For Each f In fc
Set tsIni2 = f.OpenAsTextStream(ForReading)
file2create = "f:\temp\step2" & file.Name & "done"
Set newInifile2 = fso.CreateTextFile(file2Create, True)
Do While Not tsIni2.AtEndOfStream
sLine = tsIni2.ReadLine
sLine = Replace(sLine, "$1", rs("Pkg"))
sLine = Replace(sLine, "$2", rs("Seq"))
sLine = Replace(sLine, "$3", rs("mail1"))
sLine = Replace(sLine, "$4", rs("mail2"))
sLine = Replace(sLine, "$5", rs("mail3"))
sLine = Replace(sLine, "$6", rs("mail4"))
sLine = Replace(sLine, "$7", rs("mail5"))
sLine = Replace(sLine, "$8", rs("mail6"))
sLine = Replace(sLine, "$9", rs("portid"))
newIniFile2.WriteLine sLine
Loop
newIniFile2.Close
tsIni2.Close
Next
End If
rs.Close

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
PHV,

I get an "invalid use of null:'Replace'" for the line

newIniFile2.WriteLine sLine

I'm a bit confused about this - I guess I don't understand what the issue is.

To answer your last question, I'm interested in the single row of values from the query that corresponds to each file. In other words, I'm interested in a new row with each new file (such that, for instance, the second file would contain "PKG_SEQUENCE|0000002" in the string in which we're replacing values.


Thank you so much for all of your help! I hope you're able to stay on this with me for just a bit more.
 
Upvote 0 Downvote
So, you're sure your query returns exactly one row for each file ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Yes, there is but one distinct entry for each file in the table - I just validated that.
 
Upvote 0 Downvote
So, try something like this:
rs.Open sql, con
For Each f In fc
If rs.EOF Then Exit For
Set tsIni2 = f.OpenAsTextStream(ForReading)
file2create = "f:\temp\step2" & file.Name & "done"
Set newInifile2 = fso.CreateTextFile(file2Create, True)
sLine = tsIni2.ReadAll
sLine = Replace(sLine, "$1", rs("Pkg"))
sLine = Replace(sLine, "$2", rs("Seq"))
sLine = Replace(sLine, "$3", rs("mail1"))
sLine = Replace(sLine, "$4", rs("mail2"))
sLine = Replace(sLine, "$5", rs("mail3"))
sLine = Replace(sLine, "$6", rs("mail4"))
sLine = Replace(sLine, "$7", rs("mail5"))
sLine = Replace(sLine, "$8", rs("mail6"))
sLine = Replace(sLine, "$9", rs("portid"))
newIniFile2.Write sLine
newIniFile2.Close
tsIni2.Close
rs.MoveNext
Next
rs.Close

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
OHHH -

I got the invalid use of null Replace error once again, and now I think I see why. The MAIL_TO6 column does contain null values, as does MAIL_TO5 and MAIL_TO4 at various places in the table. Is there an extra command that can be thrown into the loop to simply erase the $ variable for any null value that is encountered?

Sorry about that - I should have been more clear about the content of the table.

Thanks, PHV - you're doing an unbelieveable job for me yet again.

I truly appreciate your efforts, both from yesterday and today.
 
Upvote 0 Downvote
Does SQL server permits the use of NZ or Coalesce function ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bdwilcox
  • Locked
  • Question
HTA fails with Syntax Error when working VBScript inserted
Replies
2
Views
712
bdwilcox
bdwilcox
JayEngr
  • Locked
  • Question
If/Else issue in script 1
Replies
3
Views
432
JayEngr
JayEngr
shilpa2002
  • Locked
  • Question
memory issue while generating word document
Replies
2
Views
263
combo
combo
Holly_Mae
  • Locked
  • Question
Issue with Powerpoint VBA/VBScript - Presentations opening behind windows explorer/unactivated
Replies
0
Views
234
Holly_Mae
Holly_Mae
Jake617
  • Locked
  • Question
SendKeys Issue
Replies
2
Views
616
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top