Android Question Use a SQLite table name stored in a variable

Discussion in 'Android Questions' started by Fab117, Sep 6, 2015.

  1. Fab117

    Fab117 Member Licensed User

    Hello,
    I have a SQlite database with lots of tables.
    From an initial query, I stored the name of the future table I will need in a string variable (RefSousCategorie)
    Now I would like to use data's srored in this table under the field "Nature".
    I tried with
    Code:
    Cursor1 = Main.SQL1.ExecQuery("SELECT Nature FROM RefSousCategorie")
    But I have an error message, because it looks for the table named "RefSousCategorie" instead the name stored in this variable.

    Do someone know how to use correct code ?

    Thanks and have a nice Sunday.

    Fab
     
  2. DonManfred

    DonManfred Expert Licensed User

    Learn the basics...

    Try this
    Code:
    Cursor1 = Main.SQL1.ExecQuery("SELECT Nature FROM "&RefSousCategorie)
     
    Fab117 likes this.
  3. Fab117

    Fab117 Member Licensed User

    Perfect.

    Thank you.

    Fab
     
  4. Fab117

    Fab117 Member Licensed User

    Hi Don Manfred,
    Similar question.
    In table "ContenuActuel", I have 10 Fields

    I want to add one line in my database.
    Most of the data's to add are stored in string or int variables.
    Based on support from last Sunday, I tried:
    Main.SQL1.ExecNonQuery("INSERT INTO ContenuActuel VALUES (&DateDeCongelation, &NouvelleCategorie, &NouvelleSousCategorie, '3', &Source, &DateDExpiration, null, null, null, null)")

    NB: When I write:
    Main.SQL1.ExecNonQuery("INSERT INTO ContenuActuel VALUES ('tttt', 'zzzzzz', 'uuuuuuu', '3', 'ggggggggggg', 'ffffffffffffffff', null, null, null, null)")

    It is working.

    Thanks for your help

    Fab
     
  5. sorex

    sorex Expert Licensed User

    concatenating variables should be done by

    closing the string with a double quote
    add &
    add variable
    add &
    re-open the string with a double quote

    Code:
    Main.SQL1.ExecNonQuery("INSERT INTO ContenuActuel VALUES ('"&DateDeCongelation&"', '"&NouvelleCategorie&"', '"&NouvelleSousCategorie&"', '3', '"&Source&"','"&DateDExpiration&", null, null, null, null)")
    note the single quotes needed for date fields (at least in mssql/mysql)

    Edit: I adjusted the single quotes to your example so ignore the line above.
     
    Last edited: Sep 11, 2015
  6. Fab117

    Fab117 Member Licensed User

    Dear Sorex,

    Thank you for your help.

    Unfortunately, a copy/paste from your proposal doesn't work.

    Based on your explanation, I tried some changes, such as:
    Main.SQL1.ExecNonQuery("INSERT INTO ContenuActuel VALUES (" & DateDeCongelation & ", " & NouvelleCategorie & ", " & NouvelleSousCategorie & ", '3', " & Source & ", " & DateDExpiration & ", null, null, null, null)")
    without success.

    Fab
     
  7. Fab117

    Fab117 Member Licensed User

    Dear Sorex,
    I tested your updated code, but unfortunately, still not working

    Fab
     
  8. sorex

    sorex Expert Licensed User

    put the query string into a string variable and use a log() command with it.

    then you can copy and paste it in your sqlite editor to see what's wrong.
     
    edgar_ortiz likes this.
  9. Mahares

    Mahares Well Known Member Licensed User

    Most generally, it is much easier and more readable to use a parameterized query as shown below to avoid having to mess around with single quotes.
    Code:
    Main.SQL1.ExecNonQuery2("INSERT INTO ContenuActuel VALUES (?,?,?,?,?,?,?,?,?,?)", _
    Array As Object(DateDeCongelation, NouvelleCategorie,NouvelleSousCategorie, 3, Source, DateDExpiration, NullNullNullNull))
    Bonne chance
     
  10. edgar_ortiz

    edgar_ortiz Active Member Licensed User

    I think that a parametrized query is a nice option.

    But I prefer create a string with the command and then execute the query.

    Regards,

    Edgar
     
  11. Fab117

    Fab117 Member Licensed User

    Mahares,
    Your code is working perfectly, thank you.
    The others, thank you also for your help. I tried to create a string with the command without success.
    When I will have more time, I will try to investigate more on it.

    Have a nice Sunday.

    Fab
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice