System.Data.SQLite

I would like to read from a XP/visual basic 2008 a db3 database using:

BaseMPCommand.CommandText = "SELECT tblPrograms.idProgram, tblPrograms.strTitle, tblPrograms.strDescription FROM tblPrograms WHERE idProgram=" + iteration1.ToString

table: tblPrograms
cell: strDescription, type:text, data value for this cell: il jouit egalement des faveurs de la "reine" du bordel

my program return an exception:

SQLite error
near "Reine": syntax error

If I delete the two double quote into the cell: il jouit egalement des faveurs de la reine du bordel the program does'nt crash any more! Bug, misunderstanding, missing parameters, what could be the solution? (data is produced by an external package, and I can't modify the cell before)

Many thanks for your help

 You will want to use "parameterized" queries, which avoid problems such as this:

http://sqlite.phxsoftware.com/forums/t/83.aspx

Sunshine, thanks a lot for the answer, but I am a newbie and I don't understand how the "parameterized" querie could fix this data type problem. Could you explain a little bit why with or without these double quotes in a  cell (data type text) fix this exception?

thanks

a little step forward!

after accessing the database thru the SELECT, I update the cell using

BaseMPCommand.CommandText = "UPDATE tblPrograms SET strDescription=" + Guillemet + Description + Guillemet + " WHERE idProgram= " + iteration1.ToString

Description = il jouit egalement des faveurs de la "reine" du bordel,    Guillemet is double quote So the question:

How to place a double quote in a text field, using a parameter and so don't interpret these double quotes?????

ernie74:

I am a newbie and I don't understand how the "parameterized" querie could fix this data type problem. Could you explain a little bit why with or without these double quotes in a  cell (data type text) fix this exception?

Sorry, I read your original post too hastily.  Are you certain that the SELECT statement was the cause of the SQL error, or was the problem caused by a composed INSERT or UPDATE string containing the quoted "reine"?

ernie74:

after accessing the database thru the SELECT, I update the cell using

BaseMPCommand.CommandText = "UPDATE tblPrograms SET strDescription=" + Guillemet + Description + Guillemet + " WHERE idProgram= " + iteration1.ToString

Description = il jouit egalement des faveurs de la "reine" du bordel,    Guillemet is double quote So the question:

How to place a double quote in a text field, using a parameter and so don't interpret these double quotes?????

This is the sort of issue for which parameterized queries are very helpful.With parameterized queries, your CommandText would look like:

  BaseMPCommand.CommandText = "UPDATE tblPrograms SET strDescription=@desc WHERE idProgram=@id";

 You then provide values for the paramaterized arguments like this:

  BaseMPCommand.CommandText.Parameters["@desc"].Value = Description;
  BaseMPCommand.CommandText.Parameters["@id"].Value = iteration1;

With these parameterized values set up, any strange characters or quoting problems in 'Description' will be handled correctly and automatically by SQLite so that you do not have to worry about forming a properly quoted SQL string.

Great! Shure the faulty statment is the update. I will test your suggestion as soon as I will be back home . Any way many thanks an happy new year.

Many thanks for your help, the problem is fixed.

BaseMPCommand.Parameters.Add(New SQLiteParameter("@Description"))

BaseMPCommand.Parameters("@Description").Value = Description

BaseMPCommand.CommandText = "UPDATE tblPrograms SET strDescription = @Description WHERE idProgram = @ProgramReference"