SQL database and backticks

[idkpmiller]

I am getting the error below, this I belive is because of the backtick in the filename. how are people dealing with SQL when the string you want to store has a backtick?

Thanks

error:

Code:

2008-05-25 22:53:50.312    VERBOSE    [1]    ReturnToMainMenu()@2
2008-05-25 22:53:50.328    VERBOSE    [1]    System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression ''/fullscreen "\\Htpc\mp3\C\Carl Perkins\05_Honey_Don't.mp3"', '1', '25/05/2008 10:53:50 p.m.')'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at GameZone.CommonUtilities.SQLInsUpd(String SQLQuery) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\CommonUtilities.vb:line 100
   at GameZone.GameZone.GameZoneTask.AddTopTen(String media, String FileName, String DisplayFileName, String selectedEmulator, String emulatorexe, String emulatorarg) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\GameZoneTask.vb:line 1080
   at GameZone.ProcUtils.Launch(String media, String FileName, String DisplayFileName, String selectedEmulator, String emulatorpath, String emulatorarg, String emulatorExe) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\ProcUtils.vb:line 119
   at GameZone.GameZone.GameZoneTask.PreLaunchCheck(String media, String FileName, String DisplayFileName, String selectedEmulator, String emulatorpath, String emulatorarg, String emulatorExe) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\GameZoneTask.vb:line 2981
   at GameZone.GameZone.GameZoneTask.Controls_OnKeyDown(KeyEventArgs e) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\GameZoneTask.vb:line 2001
   at GameZone.GameZone.GameZoneTask.OnKeyDown(KeyEventArgs e) in H:\GB-PVR\project\GameZone 2.3\GameZone 2.3\GameZone 2.3\GameZoneTask.vb:line 1350
   at GBPVRX2.MenuTask.x9006c1ed9ccb79de.OnKeyDown(KeyEventArgs e)
   at GBPVRX2.x0061b801bdf12d35.xdae9991ab918b397(Object xdf2e3583f942db7b, KeyEventArgs xc4f45905cb1fc7ba)
   at System.Windows.Forms.Control.OnKeyDown(KeyEventArgs e)
   at System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
   at System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
   at System.Windows.Forms.Control.WmKeyChar(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at GBPVRX2.x0061b801bdf12d35.WndProc(Message& m)
2008-05-25 22:53:51.671    VERBOSE    [1]    ShowMouse ref=0
2008-05-25 22:53:53.062    VERBOSE    [1]    Preparing for shutdown
2008-05-25 22:53:53.125    VERBOSE    [1]    Exiting...

[pBS]

isn't there a conversion equiv? like " or something like that? i think that's vb..
umm,sometimes doubling it up works..''

that's all i got....

[Ommina]

pBS has got it. Before cooking the filename into the expression, change the ' to '' (two single quotes, not one double).

Just append .Replace("'", "''") to the filename string as you add it.

[idkpmiller]

pBS has got it. Before cooking the filename into the expression, change the ' to '' (two single quotes, not one double).

Just append .Replace("'", "''") to the filename string as you add it.

Unfortunately I have tried that, Although not convinced its working, so with the news that it may be the correct way forward. I will try again when I get home tonight :-)

Cheers

[Ommina]

Hmm - the single quote will definitely mess with things. If it is still putting up a fuss, maybe post the SQL string you're passing to the command object and we'll take it apart from there. Can always turn it into parameters if it is going to stamp its feet and complain.

As an aside -- which SqLite OLEDB provider are you using? (Well, guess that assumes you're using Sqlite.)

[idkpmiller]

Here is the SQL query, I am using a access db with the Oledbconnector

Code:

Dim NumOfPlaysQuery As String = "SELECT TTGames.TTNumberofPlays FROM(TTGames)WHERE TTGames.TTFileName=" & Chr(34) & FileName & Chr(34) & " AND TTGames.TTMedia = " & Chr(34) & media & Chr(34) & " AND TTGames.TTEmulator =" & Chr(34) & selectedEmulator & Chr(34) & " AND TTGames.TTROMPath =" & Chr(34) & ROMPath & Chr(34) & " AND TTGames.TTDisplayName =" & Chr(34) & DisplayFileName & Chr(34)

I am just going to check why the backtick doesnt seem to be being replaced correctly as I said I was replacing a single backtick with a double the error seems to suggest this is not happening :-(

[idkpmiller]

problem solved!

Yay!

The resoluton was to change the single backtick to a double the additional problem I had was it wasnt the filename per se that was causing the problem it was the embedded filename with the emulatorargs that was causing the issue and I had not replaced the backtick before the filename was embedded in to the emulatorarg varible :o

Oh well nevermind...

Thanks guys for the assistance

Cheers