Hi,
I use SQLite to drive a racing database since 2012 coded in VB.net. In that
time I have always used the sqlite-netFx40-binary-bundle-Win32-2010-x.x.xx.x
zip and then used the System.Data.SQLite.dll. I have used this process from
version 1.0.81.0 to 1.0.94.0. (Im not sure if there is anything inherently
incorrect with using that DLL so I am mentioning it)
I downloaded the System.Data.SQLite (1.0.95.0) yesterday. Upon implementation
of this DLL my program fails, with error ?Table doesn't have a Primary Key?
when I try finding a row on the table. (using this code Dim Findrow as datarow
= Inforows.Rows.Find(?Sample1?)
When I revert to 1.0.94.0 there is no issue and the program works as expected
and as it has been these last 3 years using the different versions outlined
above. Nothing else is changed other then the program reference of the
different version of the DLL. I can go back and forth; from 94 up to 95 fails
with the error. Going Back down, from 95 to 94 is OK. The table does have a
Primary Key. The data IS being filled into the table and has correct schema
types.
My intent is to be able to add temporary columns to a Data Table ( a blank
Alias column) within the program without changing the actual Database structure.
Below is the code used to fill the data table.
Dim TodaysHorses as New DataTable
Using oMainQueryR As New SQLite.SQLiteCommand
oMainQueryR.CommandText = ?SELECT HorseID, RaceID, Null as
'ISP', Comments FROM InfoRows WHERE RDate BETWEEN DATE(:StartDate) AND
DATE(:EndDate) ORDER BY DATETIME([Jump]) Asc, RaceID, TABno?
oMainQueryR.Parameters.AddWithValue(":StartDate",
CDate(StartDate).ToString("yyyy-MM-dd"))
oMainQueryR.Parameters.AddWithValue(":EndDate",
CDate(EndDate).ToString("yyyy-MM-dd"))
Using connection As New
SQLite.SQLiteConnection(R2W_conectionString)
Using oDataSQL As New SQLite.SQLiteDataAdapter
oMainQueryR.Connection = connection
oDataSQL.SelectCommand = oMainQueryR
connection.Open()
oDataSQL.FillSchema(TodaysHorses, SchemaType.Source)
oDataSQL.Fill(TodaysHorses)
connection.Close()
End Using
End Using
End Using
Here is the code that casts the error
Dim Findrow as datarow = Inforows.Rows.Find(?Sample1?)
The actual SQL Query created by the above code is
SELECT HorseID, RaceID, Null as 'ISP', Comments FROM InfoRows WHERE RDate
BETWEEN DATE(:StartDate) AND DATE(:EndDate) ORDER BY DATETIME([Jump]) Asc,
RaceID, TABno
The program fails even with this SQL query
SELECT HorseID, RaceID, Null as 'ISP', Comments FROM InfoRows
If the code is changed to use the following SQL Query the program works OK with
both versions of the dll.
SELECT HorseID, RaceID, Comments FROM InfoRows
The issue is in the Query itself, where the use of Null AS ?Alias_Name?
delivers a table without the Primary Key being designated. As I said before the
data is filled correctly even into the Primary Key column. When the Alias
column is removed the program works as normal. If the alias column is created
using an existing column that also works OK.
Example : Select HorseID, RaceID, Comments AS ?NewCol?, Comments . This SQL
string works.
Kind Regards
Michal
