Problem Using Apostrophy ' in Insert Statement (Urgent) 
Author Message
 Problem Using Apostrophy ' in Insert Statement (Urgent)

Hello,
insert into TableA values('"& txtbox1.text &"','"&
txtbox2.text &"')

Where
textBox1.text = AB0001
textBox2.text = Name's

The above statement i am executing after getting the above
data from the form.

Now, because textbox2.text has the value of Name's
(contains the apostrophy) this insert statement generates
error because what going at the backend is as follows:

insert into TableA values('AB0001','Name's')

In order to avoid this error I can do the very same thing
by opening a Recordset, but what i need is, Is there any
way that i can save the above data into the database with
the help of Insert Statement.

Well it also creates problem when I need to fetch the
specific data from the database like "Select * From TableA
Where ItemName = '"& Name's &"'".

Kindly help me to sort out this problem
Thanks in advance
Jawahir



Mon, 06 Dec 2004 20:06:42 GMT  
 Problem Using Apostrophy ' in Insert Statement (Urgent)

You must replace single quotes in your string with double
quotes.

The following code will solve your problem...

''''''''''''''''''''''''''''''''''''''''''''''
dim strClean as string

strClean = txtbox1.Text
strClean = Replace(strItemTxt, "'", "''")

'''''''''''''''''''''''''''''''''''''''''''''''

In your INSERT statement use strClean instead of
txtbox1.text

If you're doing lots of inserts put the code into a
Function in a module and save some time typing.

Good Luck, Dwight

Quote:
>-----Original Message-----
>Hello,
>insert into TableA values('"& txtbox1.text &"','"&
>txtbox2.text &"')

>Where
>textBox1.text = AB0001
>textBox2.text = Name's

>The above statement i am executing after getting the
above
>data from the form.

>Now, because textbox2.text has the value of Name's
>(contains the apostrophy) this insert statement generates
>error because what going at the backend is as follows:

>insert into TableA values('AB0001','Name's')

>In order to avoid this error I can do the very same thing
>by opening a Recordset, but what i need is, Is there any
>way that i can save the above data into the database with
>the help of Insert Statement.

>Well it also creates problem when I need to fetch the
>specific data from the database like "Select * From
TableA
>Where ItemName = '"& Name's &"'".

>Kindly help me to sort out this problem
>Thanks in advance
>Jawahir

>.



Mon, 06 Dec 2004 21:03:14 GMT  
 Problem Using Apostrophy ' in Insert Statement (Urgent)


Quote:
> Hello,
> insert into TableA values('"& txtbox1.text &"','"&
> txtbox2.text &"')

> Where
> textBox1.text = AB0001
> textBox2.text = Name's

> The above statement i am executing after getting the above
> data from the form.

> Now, because textbox2.text has the value of Name's
> (contains the apostrophy) this insert statement generates
> error because what going at the backend is as follows:

> insert into TableA values('AB0001','Name's')

> In order to avoid this error I can do the very same thing
> by opening a Recordset, but what i need is, Is there any
> way that i can save the above data into the database with
> the help of Insert Statement.

> Well it also creates problem when I need to fetch the
> specific data from the database like "Select * From TableA
> Where ItemName = '"& Name's &"'".

As others have mentioned, the apostrophe is used to delimit strings in SQL,
so when it's used as data within a string, it must be 'escaped' by doubling
it.

Although this will solve the problem, that isn't necessarily the best
solution.  Don't dynamically build the SQL by inserting string text into a
SQL string.  Use ADO parameters to pass the data to the query and the string
will be handled properly for you.  It would be even better to build a stored
procedure to handle the INSERT SQL on the server side using ADO parameters
to just pass the data to be inserted.  This will not only fix the string
delimiter problem, but it will usually improve record insert performance
dramatically while simultaneously reducing the server load.

It takes some effort to learn this approach, but you'll find it to be
worthwhile to do so.



Tue, 07 Dec 2004 13:47:11 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Problem using ' apostrophy in insert statement

2. Using apostrophy(') in Insert Statement

3. Problem Inserting batch insert statement

4. Apostrophy question in SQL statement

5. How to use apostrophe's in an INSERT statement

6. How to use apostrophe's in an INSERT statement

7. What's wrong with this insert statement?!?!?!?!?

8. using variables in Insert Into statement

9. Excel 2000: Using INSERT statement in QueryTable query fails

10. How to Insert A String Variable into an SQL Statement using VBScript/ASP/Access db

11. retrieve primary key after insert statement (using ADO / SQL Server 2000)

12. Date to SQL from VB using Insert statement

 

 
Powered by phpBB® Forum Software