PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Getting All Values Using Where Clause in SQLite-VBForums
Results 1 to 9 of 9
  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Getting All Values Using Where Clause in SQLite

    Hi, I have a database and I want to get all records (regarding given argument) using the command ExecuteNonQuery. Is this possible?
    I'm not a man of too many faces
    The mask I wear is one

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,181

    Re: Getting All Values Using Where Clause in SQLite

    The point of ExecuteNonQuery is to execute a SQL statement that does not produce a result set. If your intent is to retrieve the result set of a query then it should be obvious that ExecuteNonQuery is not appropriate. If you want to retrieve data then you have three(ish) choices:

    1. Call ExecuteScalar on a command object to retrieve a single value.
    2. Call ExecuteReader on a command object to create a data reader and then use that to read the result set one record at a time.
    2a. Once you have a data reader, you can pass it to the Load method of a DataTable to populate the table with all the data.
    3. Call Fill on a data adapter object to populate a DataTable with the entire result set.

    The difference between 2a and 3 is that a data adapter can be used to save changes made to the DataTable back to the database, while you'd only use 2a if you wanted to retrieve the data without editing it.

    I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for code examples of common ADO.NET scenarios. The examples are for SQL Server but, as it says in that thread, the patterns are the same for other data sources and you basically just have to change the provider.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Getting All Values Using Where Clause in SQLite

    I tried your suggestion before, let me try again. I'll post here what happened.
    I'm not a man of too many faces
    The mask I wear is one

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Question Re: Getting All Values Using Where Clause in SQLite

    I'm following your tutorial and changing several things. I tried the sql above for insert command using SQLite Manager. I got error: near "VALUES": syntax error

    SQL Code:
    1. INSERT INTO "tbl_sheet"
    2. SELECT (
    3.     P.txt_press,
    4.     CA.txt_category,
    5.     S.txt_repertory_no,
    6.     T.txt_taken_from,
    7.     S.txt_revision_date1,
    8.     S.txt_revision_date2,
    9.     S.txt_revision_date3,
    10.     R.txt_region,
    11.     PR.txt_prepared_by,
    12.     S.txt_research_date1,
    13.     S.txt_research_date2,
    14.     S.txt_research_date3,
    15.     SP.txt_source_by,
    16.     M.txt_measure,
    17.     S.txt_time,
    18.     C.txt_compiled_by,
    19.     S.txt_compilation_date,
    20.     N.txt_notation_by,
    21.     S.txt_mp3_paths,
    22.     A.txt_artist,
    23.     S.txt_song_name,
    24.     S.txt_lyrics,
    25.     S.image1, S.image2, S.image3, S.image4, S.image5, S.image6, S.image7,
    26.     S.image8, S.image9, S.image10, S.image11, S.image12, S.image13, S.image14
    27. )
    28. VALUES (
    29.     CA.txt_category,
    30.     "3",
    31.     T.txt_taken_from,
    32.     "1980",
    33.     NULL,
    34.     NULL,
    35.     R.txt_region,
    36.     PR.txt_prepared_by,
    37.     NULL,
    38.     NULL,
    39.     NULL,
    40.     SP.txt_source_by,
    41.     M.txt_measure,
    42.     "120",
    43.     C.txt_compiled_by,
    44.     "1980",
    45.     N.txt_notation_by,
    46.     "C:\test.mp3?C:\test2.mp3",
    47.     A.txt_artist,
    48.     "Test Song",
    49.     "Line1\nLine2\nLine3",
    50.     NULL, NULL, NULL, NULL, NULL, NULL, NULL,
    51.     NULL, NULL, NULL, NULL, NULL, NULL, NULL
    52. )
    53. FROM "tbl_sheet"  
    54. LEFT JOIN tbl_category CA
    55.     ON CA.id = S.int_category_id
    56. LEFT JOIN tbl_press P
    57.     ON P.id = S.int_press_id
    58. LEFT JOIN tbl_taken_from T
    59.     ON T.id = S.int_taken_from_id
    60. LEFT JOIN tbl_prepared_by PR
    61.     ON PR.id = S.int_prepared_by_id
    62. LEFT JOIN tbl_region R
    63.     ON R.id = S.int_region_id
    64. LEFT JOIN tbl_source_by SP
    65.     ON SP.id = S.int_source_by_id
    66. LEFT JOIN tbl_measure M
    67.     ON M.id = S.int_measure_id
    68. LEFT JOIN tbl_compiled_by C
    69.     ON C.id = S.int_compiled_by_id
    70. LEFT JOIN tbl_notation_by N
    71.     ON N.id = S.int_notation_by_id
    72. LEFT JOIN tbl_artist A
    73.     ON A.id = S.int_artist_id
    74. WHERE (
    75.         P.txt_press = "TRT Mzik Dairesi Yayınları"
    76.     AND
    77.         CA.txt_category = "Genel"
    78.     AND
    79.         T.txt_taken_from = "Selim Kasap"
    80.     AND
    81.         R.txt_region = "Şarkışla / Sivas"
    82.     AND
    83.         PR.txt_prepared_by = "<Bilinmiyor>"
    84.     AND
    85.         SP.txt_source_by = "şık Ali İzzet zkan"
    86.     AND
    87.         M.txt_measure = "şık Ali İzzet zkan"
    88.     AND
    89.         C.txt_compiled_by = "Nida Tfeki"
    90.     AND
    91.         N.txt_notation_by = "Nida Tfeki"
    92.     AND
    93.         A.txt_artist = "Zara"
    94. )

    Can anyone help me?
    I'm not a man of too many faces
    The mask I wear is one

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,938

    Re: Getting All Values Using Where Clause in SQLite

    An Insert statement can be in one of these styles:
    Code:
    INSERT INTO tablename
       (field1, field2, field3)
    VALUES
       (value1, value2, value3)
    Code:
    INSERT INTO tablename
       (field1, field2, field3)
    SELECT fieldA, fieldB, value3
    FROM ....
    (from Select onwards, it is a normal Select statement - it just needs to have the correct amount/type of fields/values to match the fields you are inserting into)

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Getting All Values Using Where Clause in SQLite

    So my structure is correct?
    I'm not a man of too many faces
    The mask I wear is one

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,938

    Re: Getting All Values Using Where Clause in SQLite

    No it isn't, your structure is like this:
    Code:
    INSERT INTO tablename
      < no fields listed here! > 
    SELECT (field1, field2, field3)
    VALUES
       (value1, value2, value3)
    FROM ...
    ...which is not like either of the ones I showed.

    Assuming you want to insert from a Select statement, you need to use the second example I showed in post #5 (so the parts I highlighted in the code block in this post are where you went wrong).

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,181

    Re: Getting All Values Using Where Clause in SQLite

    If you use a SELECT in your INSERT statement then you don't use VALUES. Either the values come from the query or you provide them, not both.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Getting All Values Using Where Clause in SQLite

    Hi geek, thanks for helping. Should I remove join's and where clause and create relations in C# class?
    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
  •  



Featured


Click Here to Expand Forum to Full Width