message box to appear to confirm records have been inserted in oracle 10g table

Guys, I have a script that uploads data from excel into oracle this is working perfectly.

What I would like to happen is a message box to pop up confirming that the records have been successfully inserted into the table.

I believe I have a solution to that but don't know how to get the value returned to me as a message box on successful. Of course if it isn't successful the message box should state that.

the section that shows "countSQL" is the sql that I want to run to validate if the records have been entered for that day.

Public Sub Insert_Oracle()

Dim cn As ADODB.Connection
Dim baseSQL, exSQL As String
Dim countSQL, exSQL1 As String
Dim source As String
Dim user As String
Dim password As String
Dim row, i As Long
   
    source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value

    Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
       
    baseSQL = "INSERT INTO IFSAPP.INTERFACE_PLANNING_TAB (OBJECT_ID, WO_NO, SITE, CRAFT, CRAFT_RATE, QTY_HOURS, AMOUNT, DAY, OBJID) " & _
              "VALUES ( $col1, $col2, '$col3', '$col4', $col5, $col6, $col7, to_date('$col8','dd/mm/yyyy'), SYSDATE) "
               
    countSQL = "select count(*) as count from ifsapp.interface_planning_tab where trunc(objid) = trunc(sysdate)"
   
   
    cn.BeginTrans
   
    row = 2

    Do While Cells(row, 1) <> ""
   
        exSQL = baseSQL
       
        For i = 1 To 9
            exSQL = Replace(exSQL, "$col" & i, Cells(row, i))
        Next i
       
        cn.Execute exSQL
   
        row = row + 1
   
    Loop
   
    If cn.Errors.Count = 0 Then
   
       cn.CommitTrans
   
    Else
        cn.RollbackTrans
    End If
   
       cn.Close
           
End Sub

Regards
DarrenJacksonAsked:
Who is Participating?
 
omgangIT ManagerCommented:
Didn't consider that you may be running the routine more than once a day.

Dim rs As ADODB.Recordset
Dim intRecCount As Integer

Set rs = New ADODB.Recordset

rs.Open countSQL, cn
intRecCount = rs(0)    '<-- could also be intRecCount = rs("count")


OM Gang
0
 
omgangIT ManagerCommented:
You could create a recordset object and assign the query countSQL to it and then reference the recordset column 'count' to get the value.  Since you're only looking for a count of processed records, and you already have the count in your routine, you could do something like this.


    cn.BeginTrans
     
     row = 2

     Do While Cells(row, 1) <> ""
     
         exSQL = baseSQL
         
         For i = 1 To 9
             exSQL = Replace(exSQL, "$col" & i, Cells(row, i))
         Next i
         
         cn.Execute exSQL
     
         row = row + 1
     
     Loop
     
     If cn.Errors.Count = 0 Then
     
        cn.CommitTrans
       
        MsgBox row - 1 & " records entered", , "Success"
     
     Else
         cn.RollbackTrans
         MsgBox "No records entered", , "Error"


     End If


OM Gang
0
 
DarrenJacksonAuthor Commented:
Thankyou for this.

This works great but..... it only tells me what has successfully been uploaded it doesn't check to see if there are already records in the oracle table for that day.
0
 
DarrenJacksonAuthor Commented:
Nice one thank you works great
0
 
omgangIT ManagerCommented:
Glad it did.  I didn't actually test it first so was working from memory!
You are welcome.

OM Gang
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.