Syntax Error with Insert Statement

[violentjay25]

I have a question. I am using Access as a frontend and the backend is a SQL server. I have used the code below in order to put more that 256 characthers into the txtMemo1 field. This was the only way I could get it to work. The problem occurs when on the form if you use a comma or apostraphe I receive a runtime error. I know this is because acces thinks its code. Its not a huge deal but is there anyway to elimiate this problem. Here is the code



Private Sub cmdUpdate_Click()

DoCmd.RunSQL "Insert into tbl_Updates (Date1, Memo1, ProjectName) " & _
"Values (#" & Me.txtDate1 & "#, '" & _
Me.txtMemo1 & "', '" & _
Me.cboProjectName & "')"

 

[RichO]

You could replace the apostrophes with the Chr$ equivalent of quotation marks.

DoCmd.RunSQL "Insert into tbl_Updates (Date1, Memo1, ProjectName) " & _
"Values (#" & Me.txtDate1 & "#, " & Chr$(34) & Me.txtMemo1 & Chr$(34) & ", '" & _
Me.cboProjectName & "')"

 

[Shadez]

To get round the problem all you need to find all the offending punctuation and replace it with two copies of the same chr.


ie,

replace(Me.txtMemo1 ,"'","''",,,vbtextcompare)

the server will now enter a single ' into the text field of the database

note the vbtextcompare at the end athough the function says this is optional ive noticed a bugs if its not included.

 

[Shadez]

You could replace the apostrophes with the Chr$ equivalent of quotation marks.

DoCmd.RunSQL "Insert into tbl_Updates (Date1, Memo1, ProjectName) " & _
"Values (#" & Me.txtDate1 & "#, " & Chr$(34) & Me.txtMemo1 & Chr$(34) & ", '" & _
Me.cboProjectName & "')"

That wont solve the problem, using a ' as a text qualifyer is just as valid as using a " did you know that """" = " a bit easier than chr$(34) . the problem is that txtmemo1 can contain invalid chr like ' and " which if left unresolved will create syntax errors in the sql field.

 

[RichO]

I don't follow. Anytime I've had syntax errors in SQL because of an apostrophe in the field, Chr$(34) would solve the problem. I'm aware of """" but I use Chr$(34) as a personal preference because I find it less cluttering when reading the code.

Does what you are saying pertain to memo date type fields only?

 

[Shadez]

It doesnt matter if the text is going into a memo or text field Changing the text qualifier from ' to " will not make any difference. They both do exactly the same thing. ie start and end a string.


Richo the examples below below will both give syntax errors.

Insert into atable (somestring) values ('" & "Mr Smith's hat" & "')"

or

Insert into atable (somestring) values ('" & chr(34) & "Mr Smith's hat" & chr(34) & ")"

the reason for the syntax error, is that there is a ' inside the text string, the system sees this as the end of the string and not the character ' hence causing a syntax error.

to solve the problem you replace single quotation mark with two quotation marks this tells the system that its the character ' not the end of a string.

ie

Insert into atable (somestring) values ('" & "Mr Smith''s hat" & "')"

or 

Insert into atable (somestring) values ('" & chr(34) & "Mr Smith''s hat" & chr(34) & ")"

it is exactly the same consept that changes """" into "


[edit]

ok ive just checked and you could get away with using the " instead of ' but what if someone types a " into the field?

 

[RichO]

Yes, the method I use has always worked for the problem with apostrophes. I've never run into actual quotation marks used in a text field but I understand that they will always cause an error.