Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Exclamation Unanswered: Help Me Please!!!!!!!!! I'm Having Dts Troubles!!!!!!!

    Can anybody help me with this?

    I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

    Dim rs1, strSQL

    strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
    set rs1 = objConn.Execute(strSQL)

    Do While NOT rs1.EOF

    strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
    strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
    strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
    strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
    objConn.Execute(strSQL)

    rs1.MoveNext()
    Loop

    Function Main()
    Main = DTSTaskExecResult_Success
    End Function

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Help Me Please!!!!!!!!! I'm Having Dts Troubles!!!!!!!

    Some things I noted:

    1. Move the Function Main() statement to the top
    2. Dim objConn
    3. Instantiate objConn and rs1
    4. Set the connection string for objConn
    5. Open objConn

    I did not see anything glaringly wrong with the SQL, but I did not focus on that.

    The rewritten code would look like:

    Code:
    Function Main()
    
        Dim rs1, strSQL, objConn
    
        set objConn = CreateObject("ADODB.Connection")
        set rs1 = CreateObject("ADODB.RecordSet")
    
        objConn.ConnectionString = {insert your connection string here}
        objConn.Open
    
        strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
        set rs1 = objConn.Execute(strSQL)
    
        Do While NOT rs1.EOF
    
            strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
    strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
    strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
    strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler   where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
            objConn.Execute(strSQL)
    
            rs1.MoveNext
    
        Loop
    
        Main = DTSTaskExecResult_Success
    
    End Function
    HTH,

    Hugh Scott

    Originally posted by Bigced_21
    Can anybody help me with this?

    I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

    Dim rs1, strSQL

    strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
    set rs1 = objConn.Execute(strSQL)

    Do While NOT rs1.EOF

    strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
    strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
    strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
    strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
    objConn.Execute(strSQL)

    rs1.MoveNext()
    Loop

    Function Main()
    Main = DTSTaskExecResult_Success
    End Function
    Last edited by hmscott; 01-30-03 at 19:39.

Posting Permissions

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