(using SQLite) DataGridView showing only three rows from my DB

Zrnko Kávy

Hey there.. after days of trying to solve this problem by trying some variations of my code, I'm desperate.

I'm trying to fill my DataGridView with SQLite database - it works.. but.. DatGridView shows me only a few rows, for whatever reason.

additional info:

SQLite queries work perfectly (tried in "DB Browser in SQLite")

also tried UNION/UNION ALL and cmd.ExecuteReader/cmd.ExecuteNonQuery + cmd.ExecuteReader

code:

private void button1_Click(object sender, EventArgs e)
{
SQLiteConnection myconnection = new SQLiteConnection("Data Source=C:\\Users\\jprochazka\\Desktop\\HG DB SQLite\\hg_db07 - serverside\\hg_db06\\databaze_heinz_glas.db;Version=3");
myconnection.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = myconnection;
cmd.CommandText = "SELECT id, nazev_tabulky, nazev_zarizeni, typ_zarizeni, vyrobce, umisteni, evidencni_cislo FROM Spotrebice UNION " +
"SELECT id, nazev_tabulky, nazev_zarizeni, typ_zarizeni, vyrobce, umisteni, evidencni_cislo FROM Stroje UNION " +
"SELECT id, nazev_tabulky, prurez, typ_kabelu, vyrobce, umisteni, evidencni_cislo FROM Prodluzovaci_Privody UNION " +
"SELECT id, nazev_tabulky, nazev, oznaceni, vnejsi_vlivy, null, ev_c FROM Prostory UNION " +
"SELECT id, nazev_tabulky, oznaceni, typ_zarizeni, vyrobce, umisteni, vyrobni_cislo FROM Rozvadece;";
using (SQLiteDataReader sdr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(sdr);
sdr.Close();
myconnection.Close();
dataGridView1.DataSource = dt;
}
}

Thanks for every advice.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
Is there any event is firing after row added to datagridview? if any event is firing then you have to check there. Did you try using SQLiteDataAdapter to bind datagrdiview
Zrnko Kávy5y ago
No events after loading. Pure DataGridView without any further changes.

SQLiteDataAdapter works, thank you very much!
Zrnko Kávy5y ago
Number of columns are the same.
Data types of each column are the same.

The SQLite query works. There's propably a problem while loading into DataGridView
Sachin Singh5y ago
you need to join , unoin won't work here.
Basic rules for combining two or more queries using UNION :

1.) number of columns and order of columns of all queries must be same.

2.) the data types of the columns on involving table in each query must be same or compatible.
In your case all table have different number of columns and Names, so you need to join them, not union them.
use proper joining key , i have just given an example

string query=@"
select *
from Spotrebice sp
join Stroje st on sp.Id=st.Spotrebice_Id
join  Prodluzovaci_Privody prp
on prp.Id= st.Prodluzovaci_Privody
join Prostory pro
on pro.Id=prp.Prostory_Id
join Rozvadece roz
on roz.Id= pro.Rozvadece_Id
";



cmd.Connection = myconnection;
cmd.CommandText = query;
using (SQLiteDataReader sdr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(sdr);
sdr.Close();
myconnection.Close();
dataGridView1.DataSource = dt;
}
}