Adding Multiple Records

[jimmimac]

Could somebody tell me how to add multiple records using ADO.

I would like to add y number of rows to my database which can contain x number of fields (Columns) using ADO.

I am creating the SQL database through code, and can successfully add one row of data. I would like the code to loop per row and for further info, the data in each row is populated by a random function.

Any help would be appreciated!

My code so far for that area.
=============================
Dim strConexion As ADODB.Connection
Dim strRecSet As New ADODB.Recordset
Dim strRecString As String
Dim intLoopVal As Integer
Dim Measures As Integer
Dim Cols As Integer
Dim feeldName As String
Dim RndNum As Variant
Dim strCon As String
Dim Rows As Integer

strCon = "Provider=SQLOLEDB.1;" & "Integrated Security=SSPI;" & "Persist Security Info=False;" & "Initial Catalog=" & sDatabaseName & ";" & "Data Source=" & frmCreateDatabase.cmbServerList.Text

'strCon = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=james;Data Source=SLAB-001"

Set strConexion = New ADODB.Connection
strConexion.ConnectionString = strCon
'Open Connection
strConexion.Open

For intTables = 1 To CInt(frmEnterTableDetails.txtNumTables)

'Set strRecSet = New ADODB.Recordset
strRecString = "SELECT * FROM " & frmEnterTableDetails.txtTableName & CStr(intTables)
'Open Record Set
strRecSet.Open strRecString, strCon, adOpenKeyset, adLockPessimistic, adCmdText

Cols = CInt(frmEnterTableDetails.txtColumns)
Measures = CInt(frmEnterTableDetails.txtMeasures)

For Rows = 1 To CInt(frmPopulate.txtRows)

'If strRecSet.Supports(adAddNew) Then
' MsgBox "Supported AddNew!"
'End If
strRecSet.AddNew
For intLoopVal = 0 To Cols - 1
feeldName = frmEnterTableDetails.txtDimNames + CStr(intLoopVal + 1)
strRecSet(intLoopVal) = feeldName
Next
intLoopVal = 0
For intLoopVal = Cols To (Cols + Measures) - 1
RndNum = GetrandomNumber()
strRecSet(intLoopVal) = RndNum
Next
strRecSet.Update

Next

'Close Record Set
strRecSet.Close

Next
'Close Connection
strConexion.Close

[dcaillouet]

I you want to add more than one record you need to have your .AddNew method called inside the loop for each record you want to add. Since these records are being inserted and not updated, you don't have to use pessimistic locking to handle contention with other users. If you use batch locking, you could insert the records with a single .UpdateBatch after the loop is completed.