How do format a SQL Insert Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Duck - 19 Dec 2005 18:21 GMT

The following Insert statement gives me an error message when the last name
is Like "O'Brien". How should I change the formating to remove this error?

sql = "insert into data_BCFP (BCFP_ID,last_name,
middle_name,First_name,birth_day,secondary_id,comm_id,Marital_status,gender,title,suffix,BCFP_Type,Denomination,occupation)" & _
"select" & "'" & M_bcfp_id & "'," & "'" & M_last_name & "'," & "'"
& M_Middle_name & "'," & "'" & M_first_name & "'," & "'" & M_Birth_day & "',"
& "'" & M_secondary_id & "'," & "'" & M_Comm_id & "'," & "'" &
M_Marital_status & "'," & "'" & M_gender & "'," & "'" & M_Title & "','" &
M_Suffix & "'," & "'" & M_BCFP_TYPE & "'," & "'" & M_Denomination & "'," &
"'" & M_Ocupation & "'"

DoCmd.RunSQL sql

Reply to this Message

Roger Carlson - 19 Dec 2005 18:25 GMT

Replace every apostrophe with TWO quotes, ie. ' -> ""

(Actually, this probably only has to be done around the Lastname field, but
it doesn't hurt to do it everywhere.

Signature

--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

> The following Insert statement gives me an error message when the last name
> is Like "O'Brien". How should I change the formating to remove this error?
>
> sql = "insert into data_BCFP (BCFP_ID,last_name,

middle_name,First_name,birth_day,secondary_id,comm_id,Marital_status,gender,
title,suffix,BCFP_Type,Denomination,occupation)" & _

> "select" & "'" & M_bcfp_id & "'," & "'" & M_last_name & "'," & "'"
> & M_Middle_name & "'," & "'" & M_first_name & "'," & "'" & M_Birth_day & "',"
[quoted text clipped - 4 lines]
>
> DoCmd.RunSQL sql

Reply to this Message

Duck - 19 Dec 2005 18:42 GMT

thanks, I tried it before I posted with a sign " instead of a ""

> Replace every apostrophe with TWO quotes, ie. ' -> ""
>
[quoted text clipped - 19 lines]
> >
> > DoCmd.RunSQL sql

Reply to this Message

Douglas J. Steele - 19 Dec 2005 22:29 GMT

See whether my May, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The following Insert statement gives me an error message when the last
> name
[quoted text clipped - 13 lines]
>
> DoCmd.RunSQL sql

Reply to this Message

Tim Ferguson - 20 Dec 2005 19:01 GMT

> The following Insert statement gives me an error message when the last
> name is Like "O'Brien". How should I change the formating to remove
[quoted text clipped - 10 lines]
> "','" & M_Suffix & "'," & "'" & M_BCFP_TYPE & "'," & "'" &
> M_Denomination & "'," & "'" & M_Ocupation & "'"

I can't understand why people throw legibility and structured code out of
the window as soon as there is a SQL command in the area....

adoSQL = "INSERT INTO data_bcfp (etc, etc, etc) " & _
"SELECT " & _
QuoteText(m_bcfp_id) & ", " & _
QuoteText(m_last_name) & ", " & _
QuoteText(m_middle_name) & ", " & _
etc etc

Public Function QuoteText(SomeText As Variant) As String
' use a variant in case it's passed a control.Value property

' this is single quote character for ADO/ SQL Server
' use double quote 34 if you are using DAO/Jet
Const c_wSQ As Integer = 39

Dim strTemp As String

If IsNull(SomeText) Then
strTemp = String$(2, c_wSQ) ' could return "NULL" instead

Else
' add quote chars at either end, and double up any in
' in the middle
strTemp = Chr(c_wSQ) & _
Replace(SomeText, String$(1, c_wSQ), String$(2, c_wSQ)) & _
Chr$(c_wSQ)

End If

QuoteText = strTemp

End Function

Hope that helps

Tim F

Reply to this Message