Should be easy, but I'm missing it

[netSurfer]

Can you post the entire line of code you use to do this? I'm not sure exactly what you are using (recordset etc) although I think I might know what's wrong

[WadeD]

sSQL = "INSERT INTO [MainRep] ([Service Description]) " _
& "VALUES (" & """" & "CAN'T" & """" & ");"
cnn.Execute sSQL

Thanks,
Wade

[MartinLiss]

Change it to 'CAN''T'

That's two single quotes between the N and the T.

------------------
Marty
Why is it called lipstick if you can still move your lips?

[WadeD]

What if CAN'T is in the middle of a long string of comments from a user?

Thanks,
Wade


[This message has been edited by WadeD (edited 01-26-2000).]

[MartinLiss]

Use the VB6 Replace function, or write a routine that parses the string replacing all single quotes with pairs of single quotes.

------------------
Marty
Why is it called lipstick if you can still move your lips?

[WadeD]

I'm writing back a number of records in a loop already. If I have to check each field that could have an apostrophe, it'll really start to drag. Do you know why VB is choking on the quotation marks when I can do it correctly directly from Access using the same SQL?

Thanks,
Wade

[jpark]

Code

Public Function RemQMark(strTest As String) As String
'Test for illegal SQL characters (' or ") in company name
Dim intI As Integer, intL As Integer, intE As Integer
Dim strMsg As String

intL = Len(strTest)
intI = 0: RemQMark = ""

Do While intI <= intL
intI = intI + 1
If Mid(strTest, intI, 1) = """" Then
strTest = Left(strTest, intI) & """" & Right(strTest, intL - intI)
ElseIf Mid(strTest, intI, 1) = "'" Then
strTest = Left(strTest, intI) & "'" & Right(strTest, intL - intI)

End If
intE = Len(strTest)
If intE > intL Then
intL = intL + 1
intI = intI + 1
End If
Loop

RemQMark = strTest

End Function
============================

Usage

sSQL = "INSERT INTO [MainRep] ([Service Description]) VALUES ('" & RemQMark(YourVariable) & "')"
cnn.Execute sSQL

Joon

[netSurfer]

try this:

strString = "Can't"

sSQL = "INSERT INTO [MainRep] ([Service Description]) " _
& "VALUES ('" & strString & "');"

cnn.Execute sSQL

That's usually what I do when I'm messing with things like this.. which is most of the time.

NOTE: that's ( SINGLE ' followed by DOUBLE " then DOUBLE ", SINGLE ' and )

[This message has been edited by netSurfer (edited 01-26-2000).]

[WadeD]

I appreciate all of the responses, but I don't want to add overhead by checking the string on each textbox that could have an apostrophe.
Netsurfer,

strString = "Can't print on all computers"
sSQL = "INSERT INTO [MainRep] ([Service Description]) " _
& "VALUES (" & """" & strString & """" & ");"
txtTestSQL = sSQL

If I copy the text from txtTestSQL to Access, the SQL works. I know in DAO, you could pass quotations. RDO or ADO should allow the same, shouldn't they?

Thanks again,
Wade

[netSurfer]

Did you try my last example? I use it and know that it will post ' inside strings. I use DAO though but I don't know if it makes a difference.

[WadeD]

I tried it, but it gives me a not enough parameters error. The example 2 above uses what you mentioned. I added """" around each side of the string to make it pass quotations (so it passes "can't print on all computers"). Any other ideas?

Thanks again,
Wade

[netSurfer]

I'm sorry , I just can't see anything wrong. It's probably something simple that we'll kick ourselves for but I just can't see it. Good luck

[WadeD]

Thanks for trying. I'll let you know if I find it.

Wade

[chrisjk]

If "can't" is causing so many problems, why not just send "cannot"??? :)

[WadeD]

Chris,

I appreciate the reply, but can't was only an example of text with an apostrophe that a user might enter.

Aaron gave me an example of Replace which works.

Thanks,
Wade

[David Laplante]

Hi WadeD,

Hope you are still looking at this topic because I tried exacly what you want to know several times... and found out a way to work around it...

instead of using a SQL statement, Just use a record set with the AddNew method...

The thing is that in SQL, string values are quoted between apostophes as so: 'YOURSTRING'
now if you put another apostrophe in that string value, it's like you are saying that the string end right there.... (Still folowing me? :))

Using recordsets is not really slower than SQL statements (Haven't actualy timed it but never had problems)

so you can try something like this:

Dim DB As Database
Dim rst As RecordSet

Set DB = OpenDatabase(PATH_OF_YOUR_DATABASE)
Set rst = DB.OpenRecordSet("SELECT * FROM MainRep")

rst.AddNew
rst![Service Description] = "Can't"
rst.update

DB.Close
rst.Close
Set DB = Nothing
Set rst = Nothing


Now using that in a loop won't be slower than using a SQL statement in a loop..

Hope it helps!

[WadeD]

I'm sending an INSERT SQL statement to add a record:
'Error --> Too Few Parameters:
INSERT INTO [MainRep] ([Service Description]) VALUES ("CAN'T");
I used the quotations around CAN'T because of the apostrophe, and this works great in Access (I copied and pasted to make sure), but I get an error in VB. Where am I going wrong??

Thanks in advance,
Wade

[This message has been edited by WadeD (edited 01-26-2000).]

[netSurfer]

It thinks you're trying to insert into 2 fields 1 variable. What is can't?

[WadeD]

The first name shown is the table name, not a field. This works directly from Access, but not in vb.

I was using a variable but it was choking on the word can't so I put can't in there while troubleshooting.

Thanks,
Wade

[This message has been edited by WadeD (edited 01-26-2000).]

[WadeD]

David,

I know you can use AddNew with DAO, which I've done before, but I've migrated to ADO which I don't believe supports the AddNew method. When I've tried it previously, I get an error message. Thanks for the reply.

Wade

[Joacim Andersson]

An ADO Recordset do have the AddNew method.

------------------
Joacim Andersson
joacim@programmer.net
joacim@yellowblazer.com
www.YellowBlazer.com (http://www.YellowBlazer.com)

Uhhh guys.. if your saying what I think your saying, your haveing a hard time with the Quotes and the Apostrophe in the word "can't" ???

Why not use a CHR ?

Example:
"can" + chr(39) + "t"

Or am I missing the point here ?


------------------
John T. Mieske
Star Trek LCARS programmer

Knight Vision Enterprises
kvision@gate.net


[This message has been edited by Knight_Vision (edited 01-28-2000).]