Need help with sqliteexception error being thrown while populating datagridview?

Question

1.00/5 (1 vote)

See more:

, +

I have a very simple project in Visual Studio 2022 using Winforms C# SQLite, that includes a combobox that selects from sqlite database.

Based on the selection in the combobox, the datagridview is filled with data from another table.

When my LoadData() starts, after I make my selection from my combobox, it generates an error System.Data.SQLite.SQLiteException: "unknown error Insufficient parameters supplied to the command' and it is referencing a line that says:

adapter.Fill(ds, "Mileage");

C#

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace ArukahHouse
{
    public partial class MileageTrack : Form
    {
        
        SQLiteConnection sqlite_conn;
        SQLiteCommand sqlite_cmd;
        SQLiteDataReader sqlite_datareader;
        string query;
        SQLiteDataAdapter adapter;
        BindingSource bsource = new BindingSource();
        DataSet ds = null;
        

        public MileageTrack()
        {
            InitializeComponent();
        }

        private void MileageTrack_Load(object sender, EventArgs e)
        {
            /*sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
            sqlite_cmd = new SQLiteCommand();
            sqlite_conn.Open();
            sqlite_cmd.Connection = sqlite_conn;
            sqlite_cmd.CommandText = "SELECT * FROM Car";
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
                autoidcomboBx.Items.Add(sqlite_datareader["car_ID"]);
                autoidcomboBx.Items.Add(sqlite_datareader["car_Make"]);
            }
            sqlite_conn.Close(); */

            sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
            query = "SELECT * FROM Car";
            sqlite_cmd = new SQLiteCommand(query,sqlite_conn);
            sqlite_conn.Open();
            sqlite_cmd.Connection = sqlite_conn;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
                query=sqlite_datareader[0].ToString()+" "+ sqlite_datareader[1].ToString()+" "+ sqlite_datareader[2].ToString() + " " + sqlite_datareader[3].ToString();
                autoidcomboBx.Items.Add(query);
            }
            sqlite_datareader.Close();
        }
        private void autoidcomboBx_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadData(); 
        }

        private void LoadData()
        {
            sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
            query = "SELECT mile_Date, begin_Miles, end_Miles FROM Mileage WHERE car_ID=@car_ID";
            sqlite_cmd.Parameters.Add("@car_ID", System.Data.DbType.Int16).Value = autoidcomboBx;
            sqlite_cmd.ExecuteNonQuery();
            adapter = new SQLiteDataAdapter(query, sqlite_conn);
            sqlite_conn.Open();
            ds = new DataSet();
            SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
            adapter.Fill(ds, "Mileage");
            bsource.DataSource = ds.Tables["Mileage"];
            dataGridView1.DataSource = bsource;
            sqlite_conn.Close();
        }
    }
}

What I have tried:

I have tried every resource I could think of by searching the web, but I am pretty new to everything and I can't figure it out.

Posted 11-Feb-22 10:44am

Blue Ocean

Updated 14-Feb-22 5:26am

Add a Solution

Comments

PIEBALDconsult 11-Feb-22 17:01pm

Make sure the parameter value isn't null.

Blue Ocean 11-Feb-22 20:01pm

I think the parameter is a problem, yes. If I
//WHERE car_ID=@car_ID";
//sqlite_cmd.Parameters.Add("@car_ID", System.Data.DbType.Int16).Value = //autoidcomboBx.SelectedIndex;
and the if () solution below with the autoidcombobx.SelectIndex change from below, I can get data to populate the datagrid, but only if dont include the mile_Date in the query. If I include it, another error is thrown: System.FormatException
HResult=0x80131537
Message=String was not recognized as a valid DateTime.

4 solutions

Solution 3

C#

qlite_cmd.Parameters.Add("@car_ID", System.Data.DbType.Int16).Value = autoidcomboBx;

A reference to a ComboBox cannot be a valid identifier. You need to find the actual Car_ID value for the item selected by the user, and pass that to your query.

Posted 12-Feb-22 0:37am

Richard MacCutchan

Solution 5

For all who are interested in the final solution that spanned two different questions, go here:

https://www.codeproject.com/Questions/5325114/How-to-concatenate-a-multiple-field-combobox-selec

Posted 14-Feb-22 11:47am

Blue Ocean

Updated 14-Feb-22 19:47pm

Solution 4

OK, with everyone's guidance I seem to have worked most things out now. I still have one issue that evades me.

Here is my cleaned up code that seems to work great so far, with the one exception:

C#

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace ArukahHouse
{
    public partial class MileageTrack : Form
    {
        SQLiteConnection sqlite_conn;
        SQLiteCommand sqlite_cmd;
        SQLiteDataReader sqlite_datareader;
        string query;
        SQLiteDataAdapter adapter;
        BindingSource bsource = new BindingSource();
        DataSet ds = null;

        public MileageTrack()
        {
            InitializeComponent();
        }

        private void MileageTrack_Load(object sender, EventArgs e)
        {
            sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
            query = "SELECT car_ID, car_Make, car_Model, car_Year FROM Car";
            sqlite_cmd = new SQLiteCommand(query,sqlite_conn);
            sqlite_conn.Open();
            sqlite_cmd.Connection = sqlite_conn;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
                query = sqlite_datareader[0].ToString();// +" "+ sqlite_datareader[1].ToString()+" "+ sqlite_datareader[2].ToString() + " " + sqlite_datareader[3].ToString();
                autoidcomboBx.Items.Add(query);
            }
            sqlite_datareader.Close();
        }

        private void autoidcomboBx_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadData(); 
        }

        private void LoadData()
        {
            if (autoidcomboBx.SelectedIndex >= 0)
            {
                sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
                query = "SELECT STRFTIME(mile_Date) AS MileDate, begin_Miles, end_Miles FROM Mileage WHERE car_ID="+ autoidcomboBx.SelectedItem;
                adapter = new SQLiteDataAdapter(query, sqlite_conn);
                sqlite_conn.Open();
                ds = new DataSet();
                SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                adapter.Fill(ds, "Mileage");
                bsource.DataSource = ds.Tables["Mileage"];
                dataGridView1.DataSource = bsource;
                sqlite_conn.Close();
            }
        }
    }
}

The problem lies in the datareader strings being passed to the combobox. If I only use

C#

query = sqlite_datareader[0].ToString();

everything works excellent. The value passed to the combobox is the car_ID which is either:
1
2
3

If I use the following, I get an exception error and it highlights the value of the string index [1].

C#

query = sqlite_datareader[0].ToString()+" "+ sqlite_datareader[1].ToString()+" "+ sqlite_datareader[2].ToString() + " " + sqlite_datareader[3].ToString();

System.Data.SQLite.SQLiteException: 'SQL logic error<br />
near "CHEVROLET": syntax error'

Which is the value of sqlite_datareader[1].ToString that I select in the combobox. The combobox loads fine with these values:
1 FORD F-150 2018
2 DODGE RAM 2021
3 CHEVROLET EQUINOX 2017

the error occurs on selection LoadData().

Any thoughts? Many thanks!

Posted 14-Feb-22 5:26am

Blue Ocean

Comments

Richard MacCutchan 14-Feb-22 12:00pm

In this case your query will be:
Copy Code

SELECT STRFTIME(mile_Date) AS MileDate, begin_Miles, end_Miles FROM Mileage WHERE car_ID=3 CHEVROLET EQUINOX 2017;

which is not a valid statement because of the space between "3" and "CHEVROLET EQUINOX 2017". Strings with spaces need to be in quotes, so your query should be:
Copy Code

query = "SELECT STRFTIME(mile_Date) AS MileDate, begin_Miles, end_Miles FROM Mileage WHERE car_ID='"+ autoidcomboBx.SelectedItem + "'"; // add single quotes round selector key.

However this will most likely not work, as car_ID is the first column only, and not as concatenation of all columns.

Blue Ocean 14-Feb-22 13:07pm

I should have caught the quotes issue, you are right it doesnt return the records but it doesnt generate the error.

Richard Deeming 14-Feb-22 12:54pm

This is not a "solution" to your question. Either click the green "Improve question" link and update your question, or mark this one as "solved" and ask a new question.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M Imran Ansari · Accepted Answer · 2022-02-11T11:25:00

That's correct, you are providing Insufficient parameters, check the below line you are providing control name as @Car_ID parameter not the selected value:

sqlite_cmd.Parameters.Add("@car_ID", System.Data.DbType.Int16).Value = autoidcomboBx;

Change you code accordingly.

private void LoadData()
{
	if (autoidcomboBx.SelectedIndex > 0)
	{
		sqlite_conn = new SQLiteConnection("Data Source=Arukah.db;Foreign Keys=True;");
        query = "SELECT mile_Date, begin_Miles, end_Miles FROM Mileage WHERE car_ID=@car_ID";
        sqlite_cmd.Parameters.Add("@car_ID", System.Data.DbType.Int16).Value = autoidcomboBx.SelectedIndex;
		// ......
		// Other code
	}
}

Need help with sqliteexception error being thrown while populating datagridview?

4 solutions

Solution 1

Solution 3

Solution 5

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us