Recordset syntax issue 1

[tradle]

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.

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!

 

[PHV]

So amend your SQL to not return Null values, but empty ones.

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

 

[tradle]

YOU'RE THE BEST, PHV!!!!!!!!!!!

I hope like crazy you know how much you are appreciated!

Regards,
Tim

 

[PHV]

Thanks for the the nice words (and the star).
BTW, don't forget mrmovie and tsuji in your other thread as now you know how to do it.

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