VB

[ritesh joshi]

how to insert a single quote ' (apostrophe) in a database field of varchar. Using VB's Execute Insert Statement.

RSJ

[Andrew_Fryer]

Use triple quotes, i.e '''

Andrew

[dave seddon]

If you want to insert a record into a database with one of the fields containing an apostrophe ('), simply place a 2nd ' after the original one and it will store the text with a single '

EG.

SomeText = "Dave''s code"
INSERT INTO TableName (FieldName) VALUES (SomeText)

would result in Dave's code being stored in the field.

[Kishore A R]

Hai Nitish,

I don't heard of VB's execute insert statement. But I can show you how to insert a apstrophe to a varchar2 field by using ADO. The following code inserts a line into a table TEST.


private Sub Form_Click()
Dim x as ADODB.Connection ' ado db connection variable
Dim strSql as string ' string to hold SQL command

set x = new ADODB.Connection ' make new connection

' open connection.
' here sms is dsn name, kishoe is user name
' and ksh is password
x.Open "sms", "kishore", "ksh"

' make the sql command
strSql = "insert into TEST values " _
& "(chr(39) || 'God loves fun' || chr(39))"

' execute it
x.Execute strSql

' close connection
x.Close
End Sub




All the best.

Kishore.

[theghost]

g_str_qot = chr$(34)


str_SQL = ""
str_SQL = str_SQL & "Insert into table" & vbCrLf
str_SQL = str_SQL & " (columnname," & vbCrLf
str_SQL = str_SQL & " columnname" & vbCrLf
str_SQL = str_SQL & " Values" & vbCrLf
str_SQL = str_SQL & "(" & g_str_Qot & datafield & g_str_Qot & "," & vbCrLf
str_SQL = str_SQL & g_str_Qot & datafield & g_str_Qot & ")" & vbCrLf
g_dba_DB.Execute str_SQL

This code should work fine for apostrophes.

Please rate the post.

Jason

[Prashant Sharma]

Two consecutive single quotes in an sql are replaced by one if they occure inside a pair of single quotes (a string). In such case they are not treated as closing single quote. If you wish to inserts a single quote, this is how you can do it...

"Insert into xyz values('Out side ''Inside'' Out again')"
This will insert the following...
Out Side 'Inside' Out again

In the same way '''' will create a string containing a single quote.

I hope it helps.

With Regards,
Prashant

PS: Kindly rate my posts, no matter you like it or not (those zeros are also welcome), as it will help me evaluate and update my self.