Results 1 to 8 of 8

Thread: [RESOLVED] Can't get data using SQLite

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Resolved [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:
    1. private void LoadData()
    2. {
    3.     connectionString.DataSource = "database.sqlite";
    4.     connectionString.ForeignKeys = true;
    5.     connectionString.JournalMode = SQLiteJournalModeEnum.Wal;
    6.  
    7.     System.Console.WriteLine(connectionString.ToString());
    8.  
    9.     SQLiteConnection sqlite_conn = new SQLiteConnection(connectionString.ToString());
    10.  
    11.     sqliteConnection = new SQLiteConnection(connectionString.ToString());
    12.     selectQueryString = @"SELECT
    13.                           S.id,
    14.                           P.txt_press,
    15.                           S.int_repertory,
    16.                           T.txt_taken_from,
    17.                           S.txt_revision_date1,
    18.                           S.txt_revision_date2,
    19.                           S.txt_revision_date3,
    20.                           R.txt_region,
    21.                           S.txt_research_date1,
    22.                           S.txt_research_date2,
    23.                           S.txt_research_date3,
    24.                           So.txt_source_person,
    25.                           S.int_time,
    26.                           C.txt_compiler,
    27.                           S.txt_compile_date,
    28.                           N.txt_notation_person,
    29.                           S.txt_song_name,
    30.                           S.txt_lyrics,
    31.                           S.image1
    32.                         FROM tbl_sheet S
    33.                         INNER JOIN tbl_press P
    34.                           ON P.id = S.int_press_id
    35.                         INNER JOIN tbl_taken_from T
    36.                           ON T.id = S.int_taken_from_id
    37.                         INNER JOIN tbl_region R
    38.                           ON R.id = S.int_region_id
    39.                         INNER JOIN tbl_source_person So
    40.                           ON So.id = S.int_source_person_id
    41.                         INNER JOIN tbl_compiler C
    42.                           ON C.id = S.int_compiler_id
    43.                         INNER JOIN tbl_notation_person N
    44.                           ON N.id = S.int_notation_person_id; ";
    45.  
    46.     sqliteConnection.Open();
    47.  
    48.     sqliteDataAdapter = new SQLiteDataAdapter(selectQueryString, sqliteConnection.ConnectionString);
    49.     sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter);
    50.  
    51.     dataTable = new DataTable();
    52.     sqliteDataAdapter.Fill(dataTable);
    53.  
    54.     sqliteConnection.Close();
    55.     sqliteDataAdapter.Dispose();
    56.  
    57.     /*
    58.     bindingSource = new BindingSource();
    59.     bindingSource.DataSource = dataTable;
    60.  
    61.    
    62.     dataGridView1.DataSource = bindingSource;
    63.  
    64.     // if you want to hide Identity column
    65.     dataGridView1.Columns[0].Visible = false;
    66.     */
    67. }

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can't get data using SQLite

    This line:
    csharp Code:
    1. 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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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
    Code:
    WHERE S.id = 1
    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can't get data using SQLite

    Quote Originally Posted by nikel View Post
    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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
  •  



Click Here to Expand Forum to Full Width