I'm trying to figure out how do multiple insert in sqlite3 database according this answer
Is it possible to insert multiple rows at a time in an SQLite database?
It seems to work perfectly, but I have a problem: my table has 9 columns + the usual ID that is "primary key autoincrement not null"...a total of 10 columns.
If I use the statement indicated in above topic, it works only if I specify an ID.
If I omit the ID in the statement, it gives me error "SQLite error table Weights has 10 columns but 9 values were supplied"
If I do a single insert and I omit the ID of course it gives the next default value automatically.
How can I omit the ID for this multiple insert?
I don't know if this is relevant, but I'm programming on a compact framework 3.5 on Windows CE 6.
Thank you
'DATABASE WEIGHT COLUMNS NAME
Private Const DBWGTIdLotto = "IDLotto"
Private Const DBWGTProgressive = "ProgressiveNum"
Private Const DBWGTWeight = "Weight"
Private Const DBWGTTime = "theTime"
Private Const DBWGTStatusFlag = "StatusFlag"
Private Const DBWGTTot1 = "Tot1"
Private Const DBWGTTot2 = "Tot2"
Private Const DBWGTTot3 = "Tot3"
Private Const DBWGTPrice = "Price"
'CREATE THE DATABASE
Public Sub CreateDatabaseWeights()
Try
If Not File.Exists(Me.DatabasePesatePath) Then
'Crea la tabella
Dim sqlString As String = "CREATE TABLE " + DatabasePesateTableName + "(" _
+ "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " _
+ DBWGTIdLotto + " INTEGER NOT NULL, " _
+ DBWGTProgressive + " INTEGER NOT NULL, " _
+ DBWGTWeight + " INTEGER, " _
+ DBWGTTime + " TIMESTAMP, " _
+ DBWGTStatusFlag + " INTEGER, " _
+ DBWGTTot1 + " BOOLEAN, " _
+ DBWGTTot2 + " BOOLEAN, " _
+ DBWGTTot3 + " BOOLEAN, " _
+ DBWGTPrice + " REAL" _
+ ");"
Dim db As New SQLiteDatabase(Me.DatabasePesatePath)
db.ExecuteNonQuery(sqlString)
End If
Catch ex As Exception
AggiungiErrore("CLASS: RecordPeso => CreateDatabaseWeights: " + ex.Message)
End Try
End Sub
'SUB TO INSERT MULTIPLE RECORDS
Public Sub AggiungiPeso(ByVal lotto As Integer, ByVal progressive As Integer, ByVal weight As Integer, ByVal flag As Byte, ByVal tot1 As Boolean, ByVal tot2 As Boolean, ByVal tot3 As Boolean, ByVal price As Single)
Try
Dim test(100) As Dictionary(Of String, String)
For i As Integer = test.GetLowerBound(0) To test.GetUpperBound(0)
'Crea il dizionario con i valori
Dim data As New Dictionary(Of String, String)
data.Add("id", i.ToString) 'IF I REMOVE THIS, IT GIVES ERROR !!!!!!!!!!!!!!
data.Add(DBWGTIdLotto, lotto.ToString)
data.Add(DBWGTProgressive, progressive.ToString)
data.Add(DBWGTWeight, weight.ToString)
data.Add(DBWGTTime, Now.ToString)
data.Add(DBWGTStatusFlag, flag.ToString)
data.Add(DBWGTTot1, IIf(tot1, "1", "0"))
data.Add(DBWGTTot2, IIf(tot2, "1", "0"))
data.Add(DBWGTTot3, IIf(tot3, "1", "0"))
data.Add(DBWGTPrice, price.ToString)
test(i) = Data
Next
'Salva il nuovo peso
Dim db As New SQLiteDatabase(Me.DatabasePesatePath)
db.MultipleInsert(DatabasePesateTableName, test)
Catch ex As Exception
AggiungiErrore("CLASS: RecordPeso => AggiungiPeso: " + ex.Message)
End Try
End Sub
--------------- DATABASE CLASS --------------------
'Insert a row in the database
'<tableName> Nome della tabella
'<data()> Array di Dizionari con coppie di colonne/valori
'<return> Boolean per successo o fallimento
Public Function MultipleInsert(ByVal tableName As String, ByVal data() As Dictionary(Of String, String)) As Boolean
Dim vals As String = ""
Dim columns As String = ""
Dim returnCode As Boolean = True
Try
'Se abbiamo elementi
If data.Count >= 1 And data(0).Count >= 1 Then
Dim tmpQuery As String = String.Format("INSERT INTO {0} SELECT ", tableName)
'Creo la query per l'aggiornamento
For Each val As KeyValuePair(Of String, String) In data(0)
tmpQuery += String.Format("'{0}' AS '{1}', ", val.Value.ToString, val.Key.ToString)
Next
'Remove last useless comma
tmpQuery = tmpQuery.Substring(0, tmpQuery.Length - 2)
'Aggiorno tutti i valori degli altri dizionari
For i As Integer = data.GetLowerBound(0) + 1 To data.GetUpperBound(0)
'Contatore inizio riga
Dim j As Integer = 0
'Add value of other dictionaries
For Each Val As KeyValuePair(Of String, String) In data(i)
If j = 0 Then tmpQuery += " UNION SELECT"
tmpQuery += String.Format(" '{0}',", Val.Value.ToString)
j += 1
Next
tmpQuery = tmpQuery.Substring(0, tmpQuery.Length - 1)
j = 0
Next
'Aggiorna il DB
Me.ExecuteNonQuery(tmpQuery)
End If
Catch ex As Exception
AggiungiRigaSuFile(ErroriPath, "CLASS: SQLiteDatabase => MultipleInsert: " + ex.Message, True, True)
returnCode = False
End Try
Return returnCode
End Function
'Interact with database for purpose different from query
'<sqlString> SQL string for interacting in DB
'<return> Number of columns updated
Public Function ExecuteNonQuery(ByVal sqlString As String) As Integer
Dim rowsUpdated As Integer = 0
Try
'Definisco la connessione al database
Using cnn As New SQLiteConnection(dbConnection)
cnn.Open()
Dim cmd As New SQLiteCommand(cnn)
cmd.CommandText = sqlString
'Aggiorno i dati
rowsUpdated = cmd.ExecuteNonQuery
End Using
Catch ex As Exception
AggiungiRigaSuFile(ErroriPath, "CLASS: SQLiteDatabase => ExecuteNonQuery: " + ex.Message, True, True)
End Try
'Ritorna le righe elaborate
Return rowsUpdated
End Function
