3.6.4.1. Insert/Update Records on a Database After Export
This is an example on how to insert/update ChronoScan data in a database using ADO DB connection.
We need to export the data to SqlServer.
Sub UpdateDatabase()
' Create a database connection,
'Set MyDB = ChronoApp.CreateAdoDBConnection("MY DSN", "", "")
MyDB = ChronoApp.GetChronoScanDBConnection("MY CHRONOSCAN CONFIG", "", "") 'connection configured in ChronoScan
If MyDB Is Nothing Then
MsgBox("Error connecting to database")
Exit Sub
End If
Set Batch = ChronoApp.GetCurrentBatch
Dim NumDocs
NumDocs = Batch.GetDocCount
'navigating records
For i = 0 To NumDocs - 1
If (Batch.IsValidated(i)) Then ' only validated documents
Set document = Batch.GetDocument(i)
MsgBox("Barcode field: " + document.get_field_value("Barcode"))
SQLString = "Select * from [Invoices] where [Barcode] = '" & document.get_field_value("Barcode") & "'"
rsInvoices = MyDB.Execute(SQLString)
If rsInvoices.EOF Then
' record doesn't exist, create a new one
MyDb.Execute("INSERT INTO [Invoices] ([invoice no], [barcode]) VALUES ('" & document.get_field_value("Invoice no") & "','" & document.get_field_value("Barcode") & "')")
Else
'record exist, update it
MyDb.Execute("UPDATE [Invoices] SET [invoice no]='" & document.get_field_value("Invoice no") & "' WHERE [barcode] = '" & document.get_field_value("Barcode") & "'")
End If
End If
Next
MyDb.Close()
End Sub
UpdateDatabase()
Results in SqlServer
