StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » Single quotes in a string cause errors

Single quotes in a string cause errors

Andria Jensen — Mon, 31 Jul 2006 13:21:46 GMT

I have some string fields on a few of my forms that have apostrophes (single quotes) in them in the database. Things like New Year's Day cause errors when I go to save the BO using the Save method. How do I get around this?

RE: Single quotes in a string cause errors

StrataFrame Team — Mon, 31 Jul 2006 13:21:46 GMT

Hehe, that's not a problem. If you have any problems on the save, just post that debug output along with the post, and it will help us solve the problem much faster.

If you ever need to send values to the server (especially string values, since they can contain single quotes), don't concatenate a bunch of strings together into the whole command; parameterize the command instead. Parameterizing commands also helps SQL Server to cache the execution path of the command for future executions since the command text itself doesn't change, just the values of the parameters.

So, instead of this:

Me.FillDataTable("SELECT * FROM MyTable WHERE MyField = '" & SomeValue & "'")

Do this:

Dim loCommand As New SqlCommand()
loCommand.CommandText = "SELECT * FROM MyTable WHERE MyField = @MyValue"
loCommand.Parameters.Add("@MyValue", SqlDbType.NVarChar)
loCommand.Parameters("@MyValue").Value = MyValue
Me.FillDataTable(loCommand)

You'll notice that you can pass a DbCommand object to the data source for execution (which is your SqlCommand object). Have fun :)

RE: Single quotes in a string cause errors

Andria Jensen — Mon, 31 Jul 2006 13:16:20 GMT

Sorry about this one...I think it's my mistake in a SQL statement I didn't realize it was hitting.

RE: Single quotes in a string cause errors

StrataFrame Team — Mon, 31 Jul 2006 13:10:54 GMT

When you run your application, in your SetDataSources() method of the AppMain.vb file, after the call to ConnectionManager.SetConnections(), add this line:

MicroFour.StrataFrame.DataLayer.DataSources("").SetDebugOn("C:\SFDebug.html", True)

When you run your app, and try to save, it will record the debug results to that output file. Then, if you can attatch the file to a forum post, it will tell me the syntax of the command that it's trying to execute and should help me figure out what's going on. Thanks.

RE: Single quotes in a string cause errors

Andria Jensen — Mon, 31 Jul 2006 13:06:51 GMT

I get "An error occurred while saving data to the server" when I try to save.

I also sometimes get:
Line 1: Incorrect syntax near 's'.
Unclosed quotation mark before the character string ''.

RE: Single quotes in a string cause errors

StrataFrame Team — Mon, 31 Jul 2006 12:56:50 GMT

What is the error message you're receiving? All of the saves are parameterized, so having apostrophes within your values shouldn't matter. We use apostrophes and special characters in our field values all the time. If you can post the exception and any inner exceptions (or just the output from the red error window), that should be enough.