Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Sydney Australia
    Posts
    2

    Question Unanswered: VBScript Capabilities

    Should ADO type updates of tables be possible in a DTS package ActiveX Task using VBScript ?

    The following code just won't run.

    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************
    option explicit
    Function Main()

    Dim Cnxn '
    dim strCnxn '
    Dim rsNotes 'rsCustomers
    dim rsNotes_Dest
    dim strSQLNotes 'strSQLCustomers
    Dim rsProducts '
    dim strSQLProducts '
    dim rec_field
    dim line_fields
    dim Account
    dim Candate
    dim srcProduct
    dim desProduct
    dim srcAccount_Number
    dim desAccount_Number
    dim srcRec_No
    dim desRec_No
    dim srcLine_No
    dim desLine_No
    dim srcTran_Code
    dim desTran_Code
    dim srcComment_Date
    dim desComment_Date
    dim srcComment
    dim desComment
    dim srcImport_Date
    dim desImport_Date
    dim strsql


    ' open connection
    Set Cnxn = CreateObject("ADODB.Connection")
    strCnxn ="Provider=SQLOLEDB;Password= 'ansett21';Persist Security Info=True;User ID= 'e12007';Initial Catalog= 'Cletus' ;Data Source= 'kogntdb28' ;Connect Timeout=0"
    Cnxn.Open strCnxn

    ' create and open Recordset using Recordset using recordset - open
    Set rsNotes = CreateObject("ADODB.Recordset")
    rsNotes.ActiveConnection = Cnxn
    'rsNotes.CursorLocation = adUseClient
    ' rsNotes.CursorType = adOpenKeyset
    'rsNotes.LockType = adLockOptimistic
    rsNotes.Source = "SELECT * FROM TCS_Notes_Transitory"
    rsNotes.Open

    ' create and open Recordsetto write to using Recordset using recordset - open
    Set rsNotes_Dest = CreateObject("ADODB.Recordset")
    strsql ="SELECT * FROM TCS_Notes"
    ' rsNotes_Dest.ActiveConnection = Cnxn
    ' 'rsNotes_Dest.CursorLocation = adUseClient
    ' 'rsNotes_Dest.CursorType = rdOpenForwardOnly
    ' rsNotes_Dest.LockType = adLockOptimistic
    ' rsNotes_Dest.Source = strsql
    ' rsNotes_Dest.Open
    rsNotes_Dest.Open "SELECT * FROM TCS_Notes", Cnxn, rdOpenForwardOnly, adLockOptimistic, adCmdText
    rec_field = 0
    line_fields = 0


    While Not rsNotes.EOF
    srcProduct = rsNotes("Product")
    srcAccount_Number = rsNotes("Account_Number")
    srcTran_Code = rsNotes("Tran_Code")
    srcComment = rsNotes("Comment")
    srcImport_Date = rsNotes("Import_Date")
    srcComment_Date = rsNotes("Comment_Date")

    msgbox srcAccount_Number

    rec_field = rec_field + 1
    line_fields = 0


    if src_Account_Number = rsNotes("Account_Number") And srcComment_Date = rsNotes("Comment_Date") And Not rsNotes.EOF then
    line_fields = line_fields + 1
    end if
    rsNotes_Dest.AddNew
    rsNotes_Dest("Product") = srcProduct
    rsNotes_Dest("Account_Number") = srcAccount_Number
    rsNotes_Dest("Tran_Code") = srcTran_Code
    rsNotes_Dest("Comment") = srcComment
    rsNotes_Dest("Import_Date") = srcImport_Date
    rsNotes_Dest("Comment_Date") = srcComment_Date
    rsNotes_Dest("Rec_No") = rec_Field
    rsNotes_Dest("Line_No") = line_fields
    rsNotes_Dest.Update

    Wend


    ' clean up
    If rsNotes.State = adStateOpen then
    rsNotes.Close
    End If
    If rsNotes_Dest.State = adStateOpen then
    rsNotes_Dest.Close
    End If

    If Cnxn.State = adStateOpen then
    Cnxn.Close
    End If
    Set rsNotes = Nothing
    Set Rs_Notes_Dest = NOthing
    Set Cnxn = Nothing
    set rec_field = Nothing
    set line_fields = Nothing
    Main = DTSTaskExecResult_Success

    End Function


    The error messages reckon that the
    "rsNotes_Dest.Open "SELECT * FROM TCS_Notes", Cnxn, rdOpenForwardOnly, adLockOptimistic, adCmdText
    "
    line has "conflicting or our of range values".

    Does anyone have any ideas on this ?

    Regards
    Warwcik

  2. #2
    Join Date
    Sep 2002
    Location
    Rhode Island, USA
    Posts
    1
    There appears to be a typo in the command string? Did you spot it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •