How do format a SQL Insert Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.

--
--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


Duck said:
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,
Click to expand...
middle_name,First_name,birth_day,secondary_id,comm_id,Marital_status,gender,
title,suffix,BCFP_Type,Denomination,occupation)" & _
 
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,ge
nder,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 & "'"
Click to expand...

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
 
Back
Top