Android Question Use a SQLite table name stored in a variable

[Fab117]

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

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

 

[DonManfred]

Cursor1 = Main.SQL1.ExecQuery("SELECT Nature FROM RefSousCategorie")

Learn the basics...

Try this

Cursor1 = Main.SQL1.ExecQuery("SELECT Nature FROM "&RefSousCategorie)

 

[Fab117]

Perfect.

Thank you.

Fab

 

[Fab117]

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

 

[sorex]

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

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.

 

[Fab117]

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

 

[Fab117]

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

Fab

 

[sorex]

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.

 

[Mahares]

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

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.

Main.SQL1.ExecNonQuery2("INSERT INTO ContenuActuel VALUES (?,?,?,?,?,?,?,?,?,?)", _
Array As Object(DateDeCongelation, NouvelleCategorie,NouvelleSousCategorie, 3, Source, DateDExpiration, Null, Null, Null, Null))

Bonne chance

 

[edgar_ortiz]

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.

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

 

[Fab117]

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