In this article I will simply use the code from my first guest author Mathieu, to build up some small working solution, that uses parameters:
So, the two classes, provided by Mathieu are here as well GitHub, named AdoValueConventer.cls and SqlCommand.cls. These are used to make the following query from the code below:
|
1 |
"INSERT INTO Invoices ([InvoiceDate],[AccountType],[CompanyName]) " & "VALUES (? , ? , ?)" |
So, what is the idea of the code?
It creates5 entries to a database, consisting of 4 columns – ID, Invoice Date, AccountType and CompanyName. ID is autoincrementable Primary Key, so it is automatically generated. The sample data is generated through a loop:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Public Sub Main() Dim invoiceCollection As New Collection Dim i As Long Dim Account As Account For i = 1 To 5 Set Account = New Account Account.CompanyName = "Company " & i Account.TypeOfAccount = "Type " & i * 3 invoiceCollection.Add Account Next AddToDatabase invoiceCollection End Sub |
The Account class, is the one which is responsible to hold the data. The invoiceCollection consists of Account Objects:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Option Explicit Private m_sTypeOfAccount As String Private m_sCompanyName As String Public Property Get TypeOfAccount() As String TypeOfAccount = m_sTypeOfAccount End Property Public Property Get CompanyName() As String CompanyName = m_sCompanyName End Property Public Property Let CompanyName(ByVal sNewValue As String) m_sCompanyName = sNewValue End Property Public Property Let TypeOfAccount(ByVal sNewValue As String) m_sTypeOfAccount = sNewValue End Property Public Property Get InvoiceDate() As Date InvoiceDate = Now() End Property |
Once the data is generated, we pass the invoiceCollection to a database, using the two classes (referred in GitHub):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Public Sub AddToDatabase(invoiceCollection As Collection) Dim conString As String Dim con As New ADODB.connection Dim rs As New ADODB.Recordset Dim sql As String conString = "Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Initial Catalog=InvoiceAutomation;Trusted_Connection=yes;timeout=30;" con.Open conString con.CommandTimeout = 60 Dim Account As Account Dim i As Long For i = 1 To invoiceCollection.Count Set Account = invoiceCollection(i) sql = "INSERT INTO Invoices ([InvoiceDate],[AccountType],[CompanyName]) " & "VALUES (? , ? , ?)" With New SqlCommand Set rs = .Execute(con, sql, Account.InvoiceDate, Account.TypeOfAccount, Account.CompanyName) End With Next i con.Close End Sub |
And this is how it sends 5 sql transactions to our LocalDb.