SQLite User Forum

SQLite Insufficient parameters supplied to the command
Login
Chronological

SQLite Insufficient parameters supplied to the command

(1) By Leon (leonloberman) on 2023-10-01 10:47:17 [source]

Hi - I'm an amateur coder so please be gentle with me!

I have a vb.net application (built in VS2019 Community edition) that is using Stub.System.Data.SQLite.Core.NetStandard v1.0.117

I'm using a SQLiteCommand to Insert Values into a table, using Parameters.

All runs fine but when I upgrade using NuGet to v1.0.118, I get the above error message.

I can't see anything wrong with the Insert command - it looks no different between the two.

I've tried searching for solutions, including case sensitivity, but cannot find any solution.

I can put the code on here but before cluttering the forum, does anyone have any suggestions.

MTIA

Leon Loberman

(2) By Stephan Beal (stephan) on 2023-10-01 11:11:01 in reply to 1 [link] [source]

I can't see anything wrong with the Insert command - it looks no different between the two.

And we can't see any of it so can only speculate.

Posting the failing code, rather than a description of the code, will drastically increase your odds of getting a useful response.

(3) By Leon (leonloberman) on 2023-10-01 16:11:27 in reply to 2 [link] [source] 

Ok - fair enough - I just wanted to make sure I wasn't missing something obvious that happened between 117 and 118.

Here's the code I'm using:
Create the table:
BS_SQL1 = "CREATE TABLE [Aircraft] (" &
                      "[AircraftID] integer PRIMARY KEY, " &
                      "[FirstCreated] datetime, " &
                      "[LastModified] datetime, " &
                      "[ModeS] varchar(6) Not NULL UNIQUE, " &
                      "[ModeSCountry] varchar(24), " &
                      "[Registration] varchar(20), " &
                      "[ICAOTypeCode] varchar(10), " &
                      "[SerialNo] varchar(30), " &
                      "[OperatorFlagCode] varchar(20), " &
                      "[Manufacturer] varchar(60), " &
                      "[Type] varchar(40), " &
                      "[FirstRegDate] varchar(10), " &
                      "[CurrentRegDate] varchar(10), " &
                      "[Country] varchar(24), " &
                      "[PreviousID] varchar(10), " &
                      "[DeRegDate] varchar(10), " &
                      "[Status] varchar(10), " &
                      "[PopularName] varchar(20), " &
                      "[GenericName] varchar(20), " &
                      "[AircraftClass] varchar(20), " &
                      "[Engines] varchar(40), " &
                      "[OwnershipStatus] varchar(10), " &
                      "[RegisteredOwners] varchar(100), " &
                      "[MTOW] varchar(10), " &
                      "[TotalHours] varchar(20), " &
                      "[YearBuilt] varchar(4), " &
                      "[CofACategory] varchar(30), " &
                      "[CofAExpiry] varchar(10), " &
                      "[UserNotes] varchar(300), " &
                      "[Interested] boolean Not NULL DEFAULT (0), " &
                      "[UserTag] varchar(5), " &
                      "[InfoURL] varchar(150), " &
                      "[PictureURL1] varchar(150), " &
                      "[PictureURL2] varchar(150), " &
                      "[PictureURL3] varchar(150), " &
                      "[UserBool1] boolean Not NULL DEFAULT (0), " &
                      "[UserBool2] boolean Not NULL DEFAULT (0), " &
                      "[UserBool3] boolean Not NULL DEFAULT (0), " &
                      "[UserBool4] boolean Not NULL DEFAULT (0), " &
                      "[UserBool5] boolean Not NULL DEFAULT (0), " &
                      "[UserString1] varchar(20), " &
                      "[UserString2] varchar(20), " &
                      "[UserString3] varchar(20), " &
                      "[UserString4] varchar(20), " &
                      "[UserString5] varchar(20), " &
                      "[UserInt1] integer DEFAULT (0), " &
                      "[UserInt2] integer DEFAULT (0), " &
                      "[UserInt3] integer DEFAULT (0), " &
                      "[UserInt4] integer DEFAULT (0), " &
                      "[UserInt5] integer DEFAULT (0));"

            daBSCommand = New SQLiteCommand(BS_SQL1, BS_Con_mem)
            daBSCommand.ExecuteNonQuery()
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

Select data from Access database to use:
Dim strSql As String = "SELECT * FROM ALLHEX"
        Dim dtb As New DataTable
        Using con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBname & "")
            con.Open()
            Using dad As New OleDbDataAdapter(strSql, con)
                dad.Fill(dtb)
            End Using
            con.Close()
        End Using



Populate the table:
daBSCommand = New SQLiteCommand("INSERT into Aircraft (AircraftID, ModeS, ModeSCountry, Registration, SerialNo, ICAOTypecode, UserInt1, UserTag, UserString1, Interested, FirstCreated, LastModified, RegisteredOwners, OperatorFlagCode)" &
                                   " VALUES (:AircraftID, :ModeS, :ModeSCountry, :Registration, :SerialNo, :ICAOTypecode, :UserInt1, :UserTag, :UserString1, :Interested, :FirstCreated, :LastModified, :RegisteredOwners, :OperatorFlagCode);", BS_Con_mem)
        Dim dr1 As DataRow
        Try

            ' Add the parameters for the InsertCommand.
            daBSCommand.Parameters.Add(":AircraftID", DbType.String, 6, "AircraftID")
            daBSCommand.Parameters.Add(":ModeS", DbType.String, 6, "ModeS")
            daBSCommand.Parameters.Add(":ModeSCountry", DbType.String, 24, "ModeSCountry")
            daBSCommand.Parameters.Add(":Registration", DbType.String, 20, "Registration")
            daBSCommand.Parameters.Add(":SerialNo", DbType.String, 30, "Serial")
            daBSCommand.Parameters.Add(":ICAOTypeCode", DbType.String, 10, "ICAOTypecode")
            daBSCommand.Parameters.Add(":UserInt1", DbType.Int32, 0, "UserInt1")
            daBSCommand.Parameters.Add(":UserTag", DbType.String, 5, "UserTag")
            daBSCommand.Parameters.Add(":UserString1", DbType.String, 20, "UserString1")
            daBSCommand.Parameters.Add(":Interested", DbType.Boolean, 0, "Interested")
            daBSCommand.Parameters.Add(":FirstCreated", DbType.DateTime, 0, "FirstCreated")
            daBSCommand.Parameters.Add(":LastModified", DbType.DateTime, 0, "LastModified")
            daBSCommand.Parameters.Add(":RegisteredOwners", DbType.String, 100, "RegisteredOwners")
            daBSCommand.Parameters.Add(":OperatorFlagCode", DbType.String, 20, "OperatorFlagCode")

            Using t As SQLiteTransaction = BS_Con_mem.BeginTransaction()

                For Each dr1 In dtb.Rows

                    daBSCommand.Parameters(":AircraftID").Value = (dr1("AircraftID"))
                    daBSCommand.Parameters(":ModeS").Value = (dr1("ModeS"))
                    daBSCommand.Parameters(":ModeSCountry").Value = (dr1("ModeSCountry"))
                    daBSCommand.Parameters(":Registration").Value = (dr1("Registration"))
                    daBSCommand.Parameters(":SerialNo").Value = (dr1("SerialNo"))
                    daBSCommand.Parameters(":ICAOTypeCode").Value = (dr1("ICAOTypeCode"))
                    daBSCommand.Parameters(":UserInt1").Value = (dr1("UserInt1"))
                    daBSCommand.Parameters(":UserTag").Value = (dr1("UserTag"))
                    daBSCommand.Parameters(":UserString1").Value = (dr1("UserString1"))
                    daBSCommand.Parameters(":Interested").Value = (dr1("Interested"))
                    daBSCommand.Parameters(":FirstCreated").Value = Now
                    daBSCommand.Parameters(":LastModified").Value = Now
                    daBSCommand.Parameters(":RegisteredOwners").Value = (dr1("RegisteredOwners"))
                    daBSCommand.Parameters(":OperatorFlagCode").Value = (dr1("OperatorFlagCode"))

                    'dr1count = dr1count + 1

                    da1.InsertCommand = daBSCommand
                    da1.InsertCommand.ExecuteNonQuery()

                Next

                t.Commit()


            End Using
            dtb.Dispose()
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

(4.2) By Aask (AAsk1902) on 2023-10-01 20:25:01 edited from 4.1 in reply to 3 [link] [source]

Using con As New OleDbConnection("Provider=Using con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBname & "").4.0;Data Source=" & DBname & "")

Microsoft.Jet.OLEDB is deprecated use Microsoft.ACE.OLEDB instead.

Select data from Access database to use:

A simpler way might be to save the data in Access as CSV format and to use the SQLite .import command.

' Add the parameters for the InsertCommand.

You are creating the parameters & then adding their respective values.

You can simplify your code using AddWithValue which requires the parameterName as the first argument and parameterValue as the second argument.

  • you would not need to create the parameter before adding its value
  • you would eliminate the likelihood of errors with parameterSize, the third argument of the Add method you are using.

If you ALWAYS supply the parameters in the same order as the columns in your destination table, consider using AddRange which takes a variant array of parameter values.

(5) By Leon (leonloberman) on 2023-10-01 21:16:18 in reply to 4.2 [link] [source]

Thank you AAsk1902

I've switched to using AddWithValue. (AddRange is going to take me a while to get my head around).

Under 118 it fails with the same error - it runs fine under 117 :-(

Re the JET engine - unfortunately I don't have control over the machines that the software runs on (background is that we are a small group of aviation enthusiasts using a local MS Access based application), so when I try and upgrade to use ACE I invariably get a dll not registered problem (some users are on Access Runtime and are not very computer savvy).

I haven't looked at saving the Access content as a CSV - perhaps the next step.

However I'd really like to understand what's changing between 117 and 118...

(6.2) By Aask (AAsk1902) on 2023-10-02 11:42:01 edited from 6.1 in reply to 5 [link] [source]

I try and upgrade to use ACE I invariably get a dll not registered problem

Unlike JET which comes as 32-bit, ACE comes as 32- OR 64-bit; that is, you can install EITHER the 32- OR the 64-bit version. The two versions cannot co-exit on the same computer without kludging).

For a 32-bit VS2019 project you will need the 32-bit ACE.

I haven't looked at saving the Access content as a CSV - perhaps the next step.

This might be a better solution ... certainly IF you need to port the data from ACCESS to SQLite ONCE only.

However I'd really like to understand what's changing between 117 and 118...

I have no insight on the difference (and can't investigate further without your MDB file and I use C# not VB) except that 1.0.117 is built upon SQLite 3.40.0 and 1.0.118 is built upon SQLite 3.42.0.

Using t As SQLiteTransaction = BS_Con_mem.BeginTransaction()

           For Each dr1 In dtb.Rows

You are inserting records in a loop and inside a transaction. In the event of an error, the errors will rollback (although your catch block does not specify this). Therefore, it is unclear whether your error arises with the first or nth insert. You might consider running your code in VS2019 debug mode and outside a transaction to determine which of the inserts is failing ... just in case it is a data (null) issue.

(7) By Leon (leonloberman) on 2023-10-02 13:56:43 in reply to 6.2 [link] [source]

Thanks for responding - that's a good suggestion about the transaction - I'll try that next.

The data is rebuilt into the Access data table and loaded to the SQLite table each time the application runs, so it's probably not so efficient to use the CSV approach.

I'm wondering if some validation has been tightened up in v118 / SQLite 3.42.0 to cause the issue to appear?

Thanks again for taking the time to provide some alternative approaches and options to try and isolate where the problem might actually lie.

(8) By Leon (leonloberman) on 2023-10-02 21:53:24 in reply to 7 [link] [source]

Update - tried running outside a transaction - still fails.

Also tried adding an AddValue for each column in the table, not just the ones coming from the MS Access db - still fails.

Finally, went back and re-checked for case sensitive errors and found one!! ICAOTypecode in the Insert statement should have been ICAOTypeCode.

Amended and lo and behold everything ran just fine on v118 :-)

Clearly v3.42.0 of SQLite is tighter on case sensitivity than v3.40.0

Thanks again for your help and sorry to have wasted your time, although this may prove an important pointer to anyone else having similar issues.

(9.1) By Stephan Beal (stephan) on 2023-10-02 22:36:13 edited from 9.0 in reply to 8 [link] [source]

Clearly v3.42.0 of SQLite is tighter on case sensitivity than v3.40.0

sqlite is not case-sensitive with regards to its own keywords, table names, column names, etc. Whether or not it treats your data as case-sensitive is entirely up to you:

SeLEcT 1 WHEre 'abc' = 'ABC'; -- no results
sElEct 1 wheRE 'abc' = 'ABC' cOlLAtE nOcAsE; -- one result

If you're hitting case-sensitivity problems, they're either in the wrapper you're using or the collations you're using (or not using), not in sqlite.

Edit: bound parameter names are case-sensitive, but the client has 100% control over those so that does not pose challenge beyond being consistent.

(10.1) By Aask (AAsk1902) on 2023-10-02 22:37:48 edited from 10.0 in reply to 8 [link] [source]

Clearly v3.42.0 of SQLite is tighter on case sensitivity than v3.40.0

The SQLiteCommand object is in System.Data.SQLite.DLL not in SQLite3.DLL. Looks like parameter names are case-sensitive albeit column names are never case-sensitive.

... and sorry to have wasted your time, although this may prove an important pointer to anyone else having similar issues.

Not a waste of time from my point of view; this type of error is easily introduced during the maintenance cycle.

However, in your case, your code runs fine in v1.117.0 but not in v1.118.0; looks like an instance of backward compatibility failure in v1.118.0 on top of other reported issues in that version.