Insert Single Quote into tables

[Maritza]

I search the Forum and Knowledge base for this topic, but was unsuccessful.

Here is the problem,

I'm inserting the value of a textbox into a table. I'm doing that with the following code:

Set cmdInsertCode = New ADODB.Command
cmdInsertCode.ActiveConnection = cnx1
cmdInsertCode.CommandText = "insert into tblExhibitACodes (ContractCodeID, PostID," & _
"PostContractCode, Location, Function)" & _
"values ('" & PostContractCodeID & "','" & intPostID & _
"','" & txtPostContractCode & " ','" & txtLocation & "','" & txtFunction & "')"
cmdInsertCode.Execute

I also tried assigning the value of the textbox to a variable string
with str = """ & txtLocation & """ and inserting the string that didn't work.


I tried str = " & ch(39) & " & txtLocation & " & ch(39) & " - didn't work

Does anyone knows how to insert text that might contain a single quote into a table using the insert into command?

Thanks,

Maritza

 

[MikeAngelastro]

Create a constant called QUOTES and make equal to """". That is four double-quotes. Then if you want a string to be treated as a string within a string - the value in a text box - as you would in an SQL statement, you can do the following:

 

[MikeAngelastro]

Create a constant called QUOTES and make equal to """". That is four double-quotes. Then if you want a string to be treated as a string within a string - the value in a text box - as you would in an SQL statement, you can do the following:

 

[MikeAngelastro]

Create a constant called QUOTES and make equal to """". That is four double-quotes. Then if you want a string to be treated as a string within a string - the value in a text box - as you would in an SQL statement, you can do the following:

SQL = "SELECT * FROM TableName WHERE Field = " & QUOTES & me.txtName & QUOTES

This will be seen by the DBengine as "SELECT * FROM TableName WHERE Field = "Dog"".

You would have to do the same thing if you wanted to actually store what's in a text box in a table. Suround the data with the QUOTES

 

[MikeAngelastro]

Create a constant called QUOTES and make equal to """". That is four double-quotes. Then if you want a string to be treated as a string within a string - the value in a text box - as you would in an SQL statement, you can do the following:

SQL = "SELECT * FROM TableName WHERE Field = " & QUOTES & me.txtName & QUOTES

This will be seen by the DBengine as "SELECT * FROM TableName WHERE Field = "Dog"".

You would have to do the same thing if you wanted to actually store what's in a text box in a table. Surround the data with the QUOTES constant.

The advantage to this approach is that your code is much more readable.

I also created a function:

Public Function SQLstring(StringIn as String) as String

SQLstring = QUOTES & StringIn & QUOTES

End Function

This way my code says:

SQL = "SELECT * FROM TableName WHERE Field = " & SQLstring( me.txtName)

which is even more readable.

Mike

 

[Mile-O]

To Mike, how many constants are you expecting called QUOTES?

 

[MikeAngelastro]

To Mile-O-Phile.

Just one.

When I responded to this I did not realize I had already responded until I posted the last reply - if that is what you are referring to.

Mike

 

[Mile-O]

You can delete unnecessary posts, good housekeeping ethics and all that. Click edit, check delete, click delete...

 

[Maritza]

Inserting Single string

Thanks Mike. I'm going to try it as soon as we return to work.(We are off due to Isabe)

Maritza