DTS ActiveX Script Update within recordset
Posted on 2004-10-11
I'm running a dts activex script that inserts each line of a query into a csv file and then emails the file.... i need to make the process update my database as it writes each line... i've been trying to use the following code - which aint working - can anyone suggest a way to do this?
CURRENT CODE (this is just the bit that deals with this part of the process)
Set rs = cn.Execute("SELECT a,b,c FROM customers WHERE a,b,c")
if rs.bof or rs.eof then noOrders = true
if not noOrders then
strLine="" 'Initialize the variable for storing the filednames
'Retrieve the values from the database and write into the database
Do while Not rs.EOF
for each x in rs.Fields
strLine= strLine & chr(34) & x.value & chr(34) & chr(44)
if x.name = "b" then
'set the email flag in the orders database
set rsConnU3 = CreateObject("ADODB.Connection")
set rs_ordersU3 = CreateObject("ADODB.Recordset")
rsConnU3.Open = "Provider=SQLOLEDB.1;Data Source=x; Initial Catalog=y;uid=xxx;password=xxx"
rsSQLCmdTextU3 = "UPDATE customers SET d= CONVERT(CHAR(8), GETDATE(), 112) WHERE (b= wN)"
rs_ordersU3.Open rsSQLCmdTextU3, rsConnU3