How to get single quotes in SQL? [Resolved]

This is probably a simple question, but I've got a brain block. I have a textbox field, txtMessage.text, that is read into a variable strMsg. This is used in an SQL Insert statement below (previously I just read txtMessage.text in this spot).
It saves just fine if there are no single quotes, i.e. apostrophes, in the text, but if there are, I get a syntax error. The user can't enter contractions because of this. How can I get this to allow single quotes? Thanks.

VB Code:

strSQL += CInt(txtInterviewerNum.Text) & ", "
        If blnIsCAS Then
            strSQL += "'" & strMsg & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        Else
            strSQL += "#" & CDate(txtSvcDate.Text) & "#, '"
            strSQL += strMsg & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        End If

If your text contains a single quote, then you must insert another one. For example.

If you are inserting (Don't try this)

Then you must make it (Don''t try this) That is NOT a double quote, it is two single quotes.

hope that helps.

That doesn't seem to be working. I ran Replace(txtMessage.Text, "'", "''", , , CompareMethod.Text), but still get the error: Syntax error in string in query expression ''Test'', True, False)'.
Without Replace I get a missing operator error.

Is this just so that if someone types in a name such as O'Connor and you submit that as say a like type expression for sql select,it fails?



here is a typical example:

Name = Name.Replace("'", "''")
Dim OledbStr As String
DbNam = DbConn.CurrCon
Dim Cn1 As New OledbConnection(DbNam)

OledbStr = "Select * from LandlordList where Name like '%" & Name & "%'"

Can you debug the thing and past the acutal SQL statement that is getting sent? Could be something else causing the problem.

Quote:

---

Originally posted by RichardAtherton
Is this just so that if someone types in a name such as O'Connor and you submit that as say a like type expression for sql select,it fails?

If you say yes and I will post the code you need

---

Well, they can type most anything except double quotes. Sentences like: I can't, or shouldn't, or won't, see Dr. O'Connor again.
I may have solved the problem, will have to test more. I was trying to use Replace as a stand alone method on txtMessage.text, then setting strMsg equal to it. Setting strMsg w/the Replace function may have solved it. But by all means post your code, it can only help, thanks.

edited last post to include code example

Thanks all. The code below seems to work. Adding the extra quote solved the first problem, using the Replace return value fixed the second.

VB Code:

Private Function SaveData(ByVal blnIsCAS As Boolean, ByVal blnIsSent As Boolean, ByVal blnIsNotSent As Boolean) As Boolean
        Dim strSQL, strMsg, strErrMsg As String
        Dim cmd As OleDbCommand
        Dim cn As OleDbConnection
 
        strMsg = Replace(txtMessage.Text, "'", "''", , , CompareMethod.Text)
        SaveData = True 'initialize & strPath
 
        'Build SQL string based on whether project is CAS.
        'Including service date on CAS raises cast error when no date present.
        strSQL = "INSERT INTO PurpleSlips "
        If blnIsCAS Then
            strSQL += "([Database], [ChooseProject], [From], [SurveyNumber], [Month], [PatientNumber], [Area/Page], [PatientName], [ComputerNumber], [PatientPhone], [InterviewerNumber], [Message], [Sent], [NotReallySent]) "
        Else
            strSQL += "([Database], [ChooseProject], [From], [SurveyNumber], [Month], [PatientNumber], [Area/Page], [PatientName], [ComputerNumber], [PatientPhone], [InterviewerNumber], [ServiceDate], [Message], [Sent], [NotReallySent]) "
        End If
        strSQL += "VALUES ('"
        strSQL += mstrDBName & "', '" & CStr(cboProject.SelectedItem) & "', '" & txtFrom.Text & "', "
        strSQL += CInt(txtSurNum.Text) & ", '"
        strSQL += CStr(cboMonth.SelectedItem) & "', "
        strSQL += CInt(txtPatNum.Text) & ", '"
        strSQL += txtModName.Text & "', '" & txtPatName.Text & "', "
        strSQL += CInt(txtCompNum.Text) & ", '"
        strSQL += txtPatPhone.Text & "', "
        strSQL += CInt(txtInterviewerNum.Text) & ", "
        If blnIsCAS Then
            strSQL += "'" & strMsg & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        Else
            strSQL += "#" & CDate(txtSvcDate.Text) & "#, '"
            strSQL += strMsg & "', " & blnIsSent & ", " & blnIsNotSent & ")"
        End If
 
        cn = New OleDbConnection(mstrCn)
        cmd = New OleDbCommand(strSQL, cn)
        strErrMsg = "Please try Send again."
 
        Try
            cn.Open()
            cmd.ExecuteNonQuery()   'save data
 
        Catch ex As OleDbException  'error opening connection
            MsgBox(strErrMsg & vbCrLf & ex.message, MsgBoxStyle.Information, "Error Opening Connection")
            SaveData = False
        Catch ex As InvalidOperationException   'connection already open or error executing command
            MsgBox(strErrMsg & vbCrLf & ex.Message, MsgBoxStyle.Information, "Connection Or Save Error")
            SaveData = False
        Catch ex As Exception
            MsgBox(strErrMsg & vbCrLf & ex.message, MsgBoxStyle.Information, "Error Saving Data")
            SaveData = False
 
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            If SaveData = True Then
                mblnDataChanged = False
            End If
        End Try
    End Function