Painful Error....

Hi...
i m inserting a string type into data base. i got the strange type of a error. i've control Text1 on the form, i enter some value into the text box like
"Hi it's me ROAMER"
My insert query states
Insert InTo demo(Notes) VALUES ('" & Text1.Text & "')
it gives me the Error. when i ommit the ' char from it's me ROAMER then the insert query inserts the data into data base. Problem is with ' char.
can any one plz help me.

use the CSQL function

here is the definition:

VB Code:

Public Function CSQL(SQLCMD As String)
     If IsEmpty(SQLCMD) Or IsNull(SQLCMD) Then CSQL = "''" Else CSQL = "'" & Replace(SQLCMD, "'", "''") & "'"
End Function

now with your insert statment go:
INSERT INTO Demo(notes) VALUES(" & CSQL(Text1.Text) & ")"

note: the csql will add the beginning ' and the end ' while replacing all other ' to '' making them a single quote inserted to the database.

VB Code:

Public Function AddSlashes(strText As String) As String
 strText = Replace$(strText, Chr(34), "\" & Chr(34))
 strText = Replace$(strText, Chr(39), "\" & Chr(39))
 
 AddSlashes = strText
End Function
 
Public Function StripSlashes(Text As String) As String
 Text = Replace$(Text, "\" & Chr(34), Chr(34))
 Text = Replace$(Text, "\" & Chr(39), Chr(39))
 
 StripSlashes = Text
End Function

Use the first function whenever you add data to your DB. It puts in front of all quotes \. And when you get data from DB use StripSlashes, it removes \ in fron of the quotes.

However, if you something like Crystal Report Writer to list data, will will have the slashes instead of apostrophies.