-
Jun 18th, 2018, 06:14 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Can't get data using SQLite
I have several tables in my SQLite database and am using inner join command. Do I have to create datatables for each table in database? Here's my code:
VB.NET Code:
private void LoadData() { connectionString.DataSource = "database.sqlite"; connectionString.ForeignKeys = true; connectionString.JournalMode = SQLiteJournalModeEnum.Wal; System.Console.WriteLine(connectionString.ToString()); SQLiteConnection sqlite_conn = new SQLiteConnection(connectionString.ToString()); sqliteConnection = new SQLiteConnection(connectionString.ToString()); selectQueryString = @"SELECT S.id, P.txt_press, S.int_repertory, T.txt_taken_from, S.txt_revision_date1, S.txt_revision_date2, S.txt_revision_date3, R.txt_region, S.txt_research_date1, S.txt_research_date2, S.txt_research_date3, So.txt_source_person, S.int_time, C.txt_compiler, S.txt_compile_date, N.txt_notation_person, S.txt_song_name, S.txt_lyrics, S.image1 FROM tbl_sheet S INNER JOIN tbl_press P ON P.id = S.int_press_id INNER JOIN tbl_taken_from T ON T.id = S.int_taken_from_id INNER JOIN tbl_region R ON R.id = S.int_region_id INNER JOIN tbl_source_person So ON So.id = S.int_source_person_id INNER JOIN tbl_compiler C ON C.id = S.int_compiler_id INNER JOIN tbl_notation_person N ON N.id = S.int_notation_person_id; "; sqliteConnection.Open(); sqliteDataAdapter = new SQLiteDataAdapter(selectQueryString, sqliteConnection.ConnectionString); sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter); dataTable = new DataTable(); sqliteDataAdapter.Fill(dataTable); sqliteConnection.Close(); sqliteDataAdapter.Dispose(); /* bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; dataGridView1.DataSource = bindingSource; // if you want to hide Identity column dataGridView1.Columns[0].Visible = false; */ }
EDIT: I have textboxes on my form and when I debug program they don't display anything. How can I fix this?
I'm not a man of too many faces
The mask I wear is one
-
Jun 18th, 2018, 06:31 AM
#2
Re: Can't get data using SQLite
This line:
csharp Code:
sqliteDataAdapter.Fill(dataTable);
is where you're executing your query and populating a DataTable. There are only three possible outcomes when that line is executed:
1. The call fails and an exception is thrown.
2. The call succeeds and returns zero, indicating that there were no records matching the query.
3. The call succeeds and returns a non-zero value, indicating that there were that many records matching your query and they were retrieved into the DataTable.
Which is it in your case? If it's #1 then you need to look at the exception to see what went wrong. If it's option #3 then you're getting your data so, if you don't see it, the issue is how you're trying to display it. If it's option #2 then either the data you expect is not present in the database or there's something wrong with your query. With complex queries like yours, the way to test that last possibility is start with the most basic query you can and then build it up step by step and see whether you stop getting data at a particular step, which means that that's where the issue is in your query.
-
Jun 18th, 2018, 07:01 AM
#3
Thread Starter
Addicted Member
Re: Can't get data using SQLite
Hello thank you for your reply. I tried the sql using SQLite Manager (Firefox addon) and couldn't get any rows again. I can't think of something to convert the sql to a basic query.
EDIT: I added to sql when trying it.
Last edited by nikel; Jun 18th, 2018 at 07:04 AM.
I'm not a man of too many faces
The mask I wear is one
-
Jun 18th, 2018, 07:06 AM
#4
Re: Can't get data using SQLite
Originally Posted by nikel
I can't think of something to convert the sql to a basic query.
Um, remove all the joins? Start by querying just one table. Do you get the results you expect? If so, add one join. Do you get the results you expect? Etc. At the point you don't get the expected results, you know that the last table added is at least part of the issue. You can then go back to basics and start with just that table. Etc.
-
Jun 18th, 2018, 07:20 AM
#5
Thread Starter
Addicted Member
Re: Can't get data using SQLite
Whoaw you're great. I removed inner joins beginning from the end and noticed that "tbl_taken_from" is the problem. The table was empty and I added 1 record to it. Then it worked well. Why is this happening?
I'm not a man of too many faces
The mask I wear is one
-
Jun 18th, 2018, 07:23 AM
#6
Re: [RESOLVED] Can't get data using SQLite
Because that's what INNER JOIN means.... only return data when rows exist in BOTH tables. So related data has to exist in all of your tables in order for your query to return anything. If even one table is empty, or doesn't have related data, then you run the risk of nothing being returned.
Perhaps what you really need is a LEFT JOIN which will return rows as long as data exists in the left table.
-tg
-
Jun 18th, 2018, 07:26 AM
#7
Re: [RESOLVED] Can't get data using SQLite
If you're still not sure after tg's explanation, perhaps you need to show us your database schema and explain what you're trying to achieve.
-
Jun 18th, 2018, 12:22 PM
#8
Thread Starter
Addicted Member
Re: [RESOLVED] Can't get data using SQLite
It OK jc. I hope I'll fix that.
I'm not a man of too many faces
The mask I wear is one
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|