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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.