escaping sql strings?

[Camel]

don't you love it when you type a page and a half, and then hit back and lose the entire message? well i'm not typing the whole thing out this time -_-

long story short: i'm trying to put a string in a column of a table in an mdb file with ms jet 4.0 driver in vb. it doesnt like my escaped strings. for example:
'camels string' will work, but 'camel\'s string' wont.
"camel's string" will work, but "camel's \"string\"" wont.
[camel's "string"] will work, but [camel's "string" \[see also: boredom\]]] wont. (note, there should be two ]s there but i put three so it would show up as two)

i need a way to put *any* string in the database, any suggestions (aside from ditching the idea completely)?

[Grok]

Do we have to say it?  

SHOW CODE!

[iago]

Also, if you're gotten to the point where you actually hate it when you press back and lose your post, then don't press back while making a post..

[Camel]

Do we have to say it?  

SHOW CODE!

arf

Code Select Expand

Public Function MkSQLStr(strIn As String)
   MkSQLStr = strIn
   If InStr(MkSQLStr, "'") = 0 Then
       MkSQLStr = "'" & MkSQLStr & "'"
       Exit Function
   End If
   If InStr(MkSQLStr, """") = 0 Then
       MkSQLStr = """" & MkSQLStr & """"
       Exit Function
   End If
   
   MkSQLStr = Replace(MkSQLStr, "'", """")
   MkSQLStr = "'" & MkSQLStr & "'"
End Function

right now that code attempts to use single quotes and then double quotes. if there are both in the string, it turns the single quotes into double quotes and uses single quotes to indicate the string

[Grok]

I'm not sure what you're wanting to do, but here is what the function DOES:

Code Select Expand


? mksqlstr("select * from t1")
'select * from t1'

? mksqlstr("""select * from t1""")
'"select * from t1"'

? mksqlstr("'select * from t1'")
"'select * from t1'"

? mksqlstr("""select * from t1 where col1 like '[vL]%'""")
'"select * from t1 where col1 like "[vL]%""'

And what's wrong with this??

? mksqlstr("camel's string")
"camel's string"



Why are you under the impression you need escape characters to put single quotes in a VB string?

[Camel]

Why are you under the impression you need escape characters to put single quotes in a VB string?

that's not what i'm using it for

Code Select Expand

ExecSQL "INSERT INTO buffer (priority, added, [text]) VALUES (10, now(), " & MkSQLStr(Left(strText, m)) & ");"

[Grok]

You seem determined to not give enough code to let someone help you.  Your question (first post) and last post (ExecSQL ...) appear to have no relation, and you've not attempted to explain the relationship or the problem, relative to it.

I've tested your MkSQLStr and shown exactly how IT functions, and asked why you are trying to use \' escape sequences.  Your reply didn't even attempt to answer.

I'm still quite willing to help out if you'd put a little effort into this.

[Adron]

It seems to me that he's trying to figure out how to escape various quotes inside a sql statement. I think it ought to be:

INSERT INTO buffer (priority, added, [text]) VALUES (10, now(), 'this is a "nice" string that''ll be added');

I.e. double quotes go as they are, single quotes duplicated, if you're using single quotes to delimit the string. That should add the string "this is a "nice" string that'll be added" in the text field.

[Camel]

thanks adron