DarrenJackson
asked on
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/yyy y'), 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
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;
baseSQL = "INSERT INTO IFSAPP.INTERFACE_PLANNING_
"VALUES ( $col1, $col2, '$col3', '$col4', $col5, $col6, $col7, to_date('$col8','dd/mm/yyy
countSQL = "select count(*) as count from ifsapp.interface_planning_
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice one thank you works great
Glad it did. I didn't actually test it first so was working from memory!
You are welcome.
OM Gang
You are welcome.
OM Gang
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