Problem importing data in SQLite C# WinForms

Question

Make a meteorological application that reads data from files. I have a problem importing the data.

I tried importing it, but I came across an error:

System.Data.SQLite.SQLiteException: 'SQL logic error 11 values for 12 columns'

private SQLiteConnection Connection;

SQLiteConnection.CreateFile("meteodb.sqlite");

Connection = new SQLiteConnection("Data Source=meteodb.sqlite;Version=3;");

Connection.Open();

string createTable = ("CREATE TABLE meteodata (Station INT(5) NOT NULL, Dat datetime NOT NULL, Temp FLOAT(7, 1), Hum FLOAT(7, 1), Press FLOAT(7, 1), windSpeed FLOAT(7, 1), windDirection FLOAT(7, 1), Rain FLOAT(7, 1), rainIntensity FLOAT(7, 1), notDraw FLOAT(7, 1), notDraw1 FLOAT(7, 1), sunRad FLOAT(7, 1), PRIMARY KEY(Station, Dat));");

SQLiteCommand createHydDnev = new SQLiteCommand(createTable, Connection);
createHydDnev.ExecuteNonQuery();

SQLiteCommand sqlComm;
sqlComm = new SQLiteCommand("begin", Connection);
sqlComm.ExecuteNonQuery();           

string insertData = ("INSERT INTO meteodata (Station, Dat, Temp, Hum, Press, windSpeed, windDirection, Rain, rainIntensity, notDraw, notDraw1, sunRad) " +
                    "values (" + item.id + ",'" + item.date + "'," + item.temperature + ",'" + item.hum + ",'" + item.presure + "," + item.windSpeed + "," + item.windDirect + "," + item.rain + "," + item.rainIntensity + "," + item.notDraw + "," +  item.notDraw1 + "," + item.sunRadiation + ");");

SQLiteCommand fillData = new SQLiteCommand(insertData, Connection);
fillData.ExecuteNonQuery();

I expect to fill the table, but I receive the above error.

Please use parameters instead of concatenating strings: https://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp — mm8, Jun 18 '19 at 12:12
The problem occurs most probably from the insert statement 'item.date'. I think if you use parameters as @mm8 suggested you can insert correctly into your table — Serhat Oz, Jun 18 '19 at 12:14
there is a typo ... use parameters to avoid them(typos) and sql injections — Selvin, Jun 18 '19 at 12:17

score 0 · Accepted Answer · answered Jun 18 '19 at 12:20

Please use parameters instead of concatenating strings:

const string InsertData = "INSERT INTO meteodata (Station, Dat, Temp, Hum, Press, windSpeed, windDirection, Rain, rainIntensity, notDraw, notDraw1, sunRad) " +
    "values (@Station, @Dat, @Temp, @Hum, @Press, @windSpeed, @windDirection, @Rain, @rainIntensity, @notDraw, @notDraw1, @sunRad)";

SQLiteCommand fillData = new SQLiteCommand(insertData, Connection);
fillData.Parameters.AddWithValue("@Station", item.id);
fillData.Parameters.AddWithValue("@Dat", item.date);
fillData.Parameters.AddWithValue("@Temp", item.temperature);
fillData.Parameters.AddWithValue("@Hum", item.hum);
fillData.Parameters.AddWithValue("@Press", item.presure);
fillData.Parameters.AddWithValue("@windSpeed", item.windSpeed);
fillData.Parameters.AddWithValue("@windDirection", item.windDirect);
fillData.Parameters.AddWithValue("@Rain", item.rain);
fillData.Parameters.AddWithValue("@rainIntensity", item.rainIntensity);
fillData.Parameters.AddWithValue("@notDraw", item.notDraw);
fillData.Parameters.AddWithValue("@notDraw1", item.notDraw1);
fillData.Parameters.AddWithValue("@sunRad", item.sunRadiation);
fillData.ExecuteNonQuery();

System.Data.SQLite.SQLiteException: 'constraint failed UNIQUE constraint failed: meteodata.Station, meteodata.Dat' With this code I have this error ? — Pizhev Racing, Jun 18 '19 at 12:39
Then your original issue has been solved beause the `INSERT` statement is executed. Apparently you have a `UNIQUE` constraint set up in your database that prevents your from adding duplictate values in the `Stat` and `Dat` columns. — mm8, Jun 18 '19 at 12:41
I try with INSERT or IGNORE querry and then program work properly I think... :P — Pizhev Racing, Jun 18 '19 at 12:44

score 0 · Answer 2 · answered Jun 18 '19 at 13:06

0

Here is the problem in your code:

...",'" + item.hum + ",'"... - note the misplaced single quotes, around item.hum.

The other parameters-based answer is indeed better.

answered Jun 18 '19 at 13:06

Anton

3,587
2
12
27

Problem importing data in SQLite C# WinForms

2 Answers2