Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
My Project Details: Visual Studio WinForms C# SQLite; a simple form to retrieve mileage records from the autos selected.

I have a combobox populated with the following from query:

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

The actual names and values are: car_ID car_Make car_Model car_year so the combo box has the following records:

1 FORD F-150 2018
2 DODGE RAM 2021
3 CHEVROLET EQUINOX 2017

Based on the selection of one of these three in the combobox, the following query is run:
C#
query = "SELECT STRFTIME(mile_Date) AS MileDate, begin_Miles, end_Miles FROM Mileage WHERE (car_ID='"+ autoidcomboBx.SelectedItem +"')";

No error occurs, but it doesn't pull the records I want because it still sees a string and not four values separated by spaces where I want the first value to compare to car_ID.

Below is the full code for reference:
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();
            }
        }
    }
}


What I have tried:

Hours of online searches. I thought about concat_ws, but I can't seem to make that work.
Posted
Updated 14-Feb-22 11:41am
v2
Comments
Richard MacCutchan 14-Feb-22 14:16pm    
I already explained why this will not work. You cannot concatenate all the fields and expect SQLite to figure out that you want it to ignore part of it.

You have four columns in your database, the first is car_ID which contains a number only. So when you want to use that in a WHERE clause you must use only that value. Passing a string like "3 CHEVROLET EQUINOX 2017" will never match. If you want to include the other fields than you will need to use three more expressions separated by AND as described at SQL AND, OR, NOT Operators[^].
 
Share this answer
 
Comments
Maciej Los 14-Feb-22 14:52pm    
Good point!
A combobox[^] control has got 2 most important properties:
- DisplayMember[^]
- ValueMember[^]

You can use them in your MileageTrack_Load method:

C#
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";
    query = "SELECT car_ID, car_Make || ' ' || car_Model  || ' ' || car_Year AS carInfo 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();

    DataTable dt = new DataTable();
    dt.Load(sqlite_datareader);
    autoidcomboBx.ValueMember = "car_ID";
    autoidcomboBx.DisplayMember = "car_Info";
    autoidcomboBx.DataSource = dt;
}


After that, use SelectedItem[^] or SelectedValue[^] to get car_ID for further usage.

Good luck!
 
Share this answer
 
Comments
Blue Ocean 14-Feb-22 15:19pm    
Thanks Maciej, I really appreciate the extra direction!
Maciej Los 14-Feb-22 15:20pm    
You're very welcome.
Hey All, I am posting the solution of what worked for my code for anyone in the future that may need help. Im sure others could do a cleaner job, but this worked for me.

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 AS carInfo FROM Car";
            sqlite_cmd = new SQLiteCommand(query,sqlite_conn);
            sqlite_conn.Open();
            sqlite_cmd.Connection = sqlite_conn;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(sqlite_datareader);
            autoidcomboBx.ValueMember = "car_ID";
            autoidcomboBx.DisplayMember = "carInfo";
            autoidcomboBx.DataSource = dt;
        }

        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.SelectedValue + "')";
                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();
            }
        }
    }
}


Super thanks to @losmacWatch especially and also @Richard-MacCutchanWatch !
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900