Not sure how to run the UPDATE statement. 1

[tradle]

Hey Guys -

Hope all is well since my last post. My latest issue is simply bourne out of inexperience with VBScript. This script replaces values contained in Postscript files with entries from two different recordsets. What I want to do now is update a separate table when each file is updated with a "1". I've defined the SQL connection and SQL statement, but now don't know what to do next. I don't believe that using a recordset is correct in this instance, as I'm not storing values for future use. I simply want to enter a value as the script iterates through the file structure.

Any thoughts would be greatly appreciated.

Regards,
Tim

Function ReplaceTagValues()

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, source, fc, dbII
dbII = "Advent_Reports"
set con2 = CreateObject("ADODB.Connection")
strCon2 = "driver={SQL SERVER};server=OHCLESQL4005;uid=user;pwd=pwd;database=" & dbII & ""
con2.Open strCon2
   
sql2 = "select FileAsOfDate as Date, Period as Period" & _ 
   	" from Advent_Reports.dbo.BowneTags "      
set rs2 = CreateObject("ADODB.Recordset")
	rs2.Open sql2, con2

Set fso = CreateObject("Scripting.FileSystemObject")
set source = fso.GetFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step1\")
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, coalesce(MAIL_TO1, '') as mail1, coalesce(MAIL_TO2, '') as mail2, coalesce(MAIL_TO3, '') as mail3, coalesce(MAIL_TO4, '') as mail4, coalesce(MAIL_TO5, '') as mail5, coalesce(MAIL_TO6,'') as mail6, ACCOUNT_ID as portid, Stream as stream" & _
        		" from Advent_Reports.dbo.BowneTags "

		set rs = CreateObject("ADODB.Recordset")

			rs.Open sql, con

[b]Dim dbIII

dbIII = "NCC_DW"

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

'Create Command
		sql3 = "update Account_Status" & _
			  "set Metadata = 1" & _
				"where Acct_Nbr = "&(rs("Portid"))& _
					"and Period = "&(rs2("Period"))& " [/b]
        
				For Each f In fc
				  If rs.EOF Then Exit For
				  Set tsIni2 = f.OpenAsTextStream(ForReading)
				  file2create = "f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step2\" & f.Name
				  Set newInifile2 = fso.CreateTextFile(file2Create, True)
				  sLine = tsIni2.ReadAll
				  sLine = Replace(sLine, "@stream1@", rs("stream"))
				  sLine = Replace(sLine, "@stream2@", rs("Pkg"))
				  sLine = Replace(sLine, "@stream3@", rs("Seq"))
				  sLine = Replace(sLine, "@stream4@", rs("mail1"))
				  sLine = Replace(sLine, "@stream5@", rs("mail2"))
				  sLine = Replace(sLine, "@stream6@", rs("mail3"))
				  sLine = Replace(sLine, "@stream7@", rs("mail4"))
				  sLine = Replace(sLine, "@stream8@", rs("mail5"))
				  sLine = Replace(sLine, "@stream9@", rs("mail6"))
				  sLine = Replace(sLine, "@stream10@", rs("portid"))
				  newIniFile2.Write sLine
				  newIniFile2.Close
				  tsIni2.Close
				  rs.MoveNext
				Next
			rs.Close 
		Set rs = Nothing

	fso.CopyFile "f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step2\*.ps", "f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress", true

	fso.DeleteFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step1")
	fso.DeleteFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step2")

	rs2.Close
Set rs2 = Nothing

End Function

Call ReplaceTagValues()

 

[bboffin]

Try:

.
.
' this statement will execute the Update statement and
' return in NumAffected the number of records that were
' updated
con.Execute sql3, NumAffected
.
.

 

[tradle]

Thanks, bboffin -

2 questions:

1. Does it matter where the con.Execute statemenet needs to be placed?
2. What form of output is NumAffected?

Thanks,
Tim

 

[tradle]

One more question...

I'm getting a syntax error on the sql3 statement. What am I doing wrong?

sql3 = "update NCC_DW.dbo.Account_Status" & _
	  " set NCC_DW.dbo.Account_Status.Metadata = 1" & _
          "where NCC_DW.dbo.Account_Status.Acct_Nbr =" & (rs("portid")) & _
          "and NCC_DW.dbo.Account_Status.Period =" & (rs2("Period")) & ""

 

[PHV]

Insert a space before the where and the and words.

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

 

[tradle]

Thanks, PHV -

I now get further, but am getting a different error. I don't believe that my syntax is correct to call the sql3 statement specifically for the recordset entry that correlates to "portid". Let me further explain after you look at the code...

For Each f In fc
 If rs.EOF Then Exit For
  Set tsIni2 = f.OpenAsTextStream(ForReading)
  file2create = "f:\advent\wrc\ps\"&(rs2("Date")) 
    &"\In_Progress\Step2\" & f.Name
  Set newInifile2 = fso.CreateTextFile(file2Create, True)
  sLine = tsIni2.ReadAll
  sLine = Replace(sLine, "@stream1@", rs("stream"))
  sLine = Replace(sLine, "@stream2@", rs("Pkg"))
  sLine = Replace(sLine, "@stream3@", rs("Seq"))
  sLine = Replace(sLine, "@stream4@", rs("mail1"))
  sLine = Replace(sLine, "@stream5@", rs("mail2"))
  sLine = Replace(sLine, "@stream6@", rs("mail3"))
  sLine = Replace(sLine, "@stream7@", rs("mail4"))
  sLine = Replace(sLine, "@stream8@", rs("mail5"))
  sLine = Replace(sLine, "@stream9@", rs("mail6"))
  sLine = Replace(sLine, "@stream10@", rs("portid"))
  newIniFile2.Write sLine
   [b]con3.Execute sql3[/b]
  newIniFile2.Close
  tsIni2.Close
 rs.MoveNext
Next

What I'm ultimately trying to accomplish is that a value of 1 is inserted into the Metadata column as set by the sql3 statement. I want this to happen as the script iterates through each file. That way, I have an identifier to key off of in some of my other logic.

As I have it now, an error is returned that references a "portid" value, but says that it is an invalid column name. This leads me to believe that the syntax that I'm using isn't correct, or that I haven't properly defined the sql3 statement.

Thanks so much for helping!

Tim

 

[PHV]

Move the building of the sql3 statement just before the con3.execute sql3 line, as it references rs("portid").

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

 

[tradle]

I moved the sql3 definition, but am still getting the same error. Is this syntax correct to reference a value within a column, and not the column itself?

" where NCC_DW.dbo.Account_Status.Acct_Nbr =" & (rs("portid")) & _

 

[PHV]

Provided that Acct_Nbr and Period are numeric, try this:
[tt]sql3 = _
"UPDATE NCC_DW.dbo.Account_Status" & _
" SET NCC_DW.dbo.Account_Status.Metadata=1" & _
" WHERE NCC_DW.dbo.Account_Status.Acct_Nbr=" & rs("portid") & _
" AND NCC_DW.dbo.Account_Status.Period=" & rs2("Period")[/tt]
Let me know if either is text or date.

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

 

[tradle]

Period is numeric, but portid is text.

 

[PHV]

Provided that Acct_Nbr is text and Period is numeric, try this:
[tt]sql3 = _
"UPDATE NCC_DW.dbo.Account_Status" & _
" SET NCC_DW.dbo.Account_Status.Metadata=1" & _
" WHERE NCC_DW.dbo.Account_Status.Acct_Nbr='" & rs("portid") & "'" & _
" AND NCC_DW.dbo.Account_Status.Period=" & rs2("Period")[/tt]

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

 

[tradle]

Thanks PHV!!!!!!!!!!!!!