How to Insert Character ( ' ) in Access - Xtreme Visual Basic Talk

11-28-2006, 12:39 AM

Kashif

Junior Contributor

Join Date: Aug 2002

Location: Earth

Posts: 344

How to Insert Character ( ' ) in Access

Hi All Members.

I'm Using Execute Method to Insert Values from Vb 6.0 Interface into Microsft Access Database.

I want to ask if User TYPE ( ' ) Character in a Chracter type Field. How do i Insert that Value in the Database Using the Execute Method of a Connection Type Object.

__________________
Regards,
Kashif
There are too many Question that looks Odd
But it helps more to Learn

11-28-2006, 12:48 AM

gb711825

Contributor

Join Date: Apr 2004

Location: Ireland

Posts: 563

Look for Chr(39) in your textbox

Or just refer to the textbox in your SQL statement. It should not matter what the contents of the textbox is.

Code:

strInsert = "INSERT INTO [i]Table[/i]([i]field[/i]) VALUES( '" & me.Textbox & "')"
cn.execute strInsert

__________________
Yeah Boyieeeeeeeeeee!

11-28-2006, 12:56 AM

jutsu

Junior Contributor

Join Date: Jul 2006

Posts: 343

You have to replace the (') with ('') or otherwise you'll get an error..

Code:

Dim a As String
    a = "'mama"
    a = Replace(a, "'", "''")

11-28-2006, 12:56 AM

Kashif

Junior Contributor

Join Date: Aug 2002

Location: Earth

Posts: 344

Here is my SQL Statment

Code:

mySQL = "INSERT INTO A_LVL2 VALUES (" & txtAcctCode & "," & "'" & txtDesc & "'" & "," & LVL1Code & ")"
con.Execute (mySQL)

__________________
Regards,
Kashif
There are too many Question that looks Odd
But it helps more to Learn

11-28-2006, 01:05 AM

Kashif

Junior Contributor

Join Date: Aug 2002

Location: Earth

Posts: 344

Quote:

Originally Posted by jutsu

You have to replace the (') with ('') or otherwise you'll get an error..

Is there no Way to Insert Single Quote in the Database. because this the Requirement.
Replacement is not the Solution.

__________________
Regards,
Kashif
There are too many Question that looks Odd
But it helps more to Learn

11-28-2006, 01:07 AM

NEOLLE

fully realized avatar

Super Moderator
* Expert *

Join Date: Jun 2004

Location: Davao Philippines

Posts: 2,295

Just apply jutsu's recommendation

Code:

mySQL = "INSERT INTO A_LVL2 VALUES (" & txtAcctCode & ",'" & Replace$(txtDesc,"'","''") & "'," & LVL1Code & ")"
con.Execute (mySQL)

Just a tip, its better to specify which the fields you will be inserting. This will cause an error if your database will be modified - like adding a new column, re-order the column assignment or a column is removed.

__________________
A Day Called Today The Avatar State Amazing Davao! Shared Pool