Solved

quote problem

Posted on 2004-09-30
19
252 Views
Last Modified: 2010-08-05
Hi Experts,

I have Insert that works fine until I get single qoute within text.
The question is how do I convert / replace this single quote so it will be inserted.

Thank you.
0
Comment
Question by:fpoyavo
19 Comments
 
LVL 1

Author Comment

by:fpoyavo
ID: 12193664
I have already tried Replace("'", "''"); but it does not help.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12193674
I also tried to replace it by double quote but when I select the value I do see double but not single.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12193822
r u inserting into sql server
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12193874
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12193893
praneetha,

I saw this link before. The problem is that I am not using stored procedure :). It is OLEDB within C#.

Thanks.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12193926
can u post that code where u r trying to  insert
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12193981
I am saing it works fine until there is a single quote in text.

Thanks.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12194009
Here is code :

for select which blows up first I believe :

mysql = "select count(*) from mytbl where mycolumn = '" + mystring + "'";

Thanks.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12194049
mysql = "select count(*) from mytbl where mycolumn = 'hello'  "

can u hard code the values and check....

and when u have ' may be do \'

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:fpoyavo
ID: 12194085
here is insert :

mysql2 = "Insert into mytbl(col1, col2)" +
" values ('" + mystr1 + "', '" +  mystr2 + "')";
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12194100
praneetha,

It is not a matter of guessing...Either you know or not. Believe me I have tried all these things and only then came here.

Thank you anyway.
0
 
LVL 9

Expert Comment

by:s_sansanwal
ID: 12194295
Hi there,

Try

mysql2 = "Insert into mytbl(col1, col2)" +
      " values ('" + mystr1.Replace("'","''") + "', '" +  mystr2.Replace("'","''") + "')";


REPLACE does not change the variable but returns the replaced value.

Cheers,
S Sansanwal
   
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12194349
TextBox1.Text=TextBox1.Text.Replace("'","''");

                  // add a new user
                  SqlCommand myCommand=new SqlCommand("insert into testins(id) values('"+this.TextBox1.Text+"')",myConnection);
                  myCommand.CommandType=CommandType.Text;

ok that worked for me

replace "'" with 2 single quotes
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12194802
S Sansanwal

Please read from the start. I did it already.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12194847
is it mysql...bcz mine works in MS SQL SErver
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 12194859
praneetha,

You did not read the question and two post comments. They are saying I tried it already.

Another question is : when I do insert with replaced to double what do I have in Database ? I know - double .
                                when I do          with                   two single                                     ?  I dont know. Do you ?

                                when I do select what do I get for double : I know - double
                                                                              for two single : I don't. Do you ?

Thanks.
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12194868
yea two single quotes insrted single quote in the sql server...

0
 
LVL 11

Accepted Solution

by:
sachiek earned 500 total points
ID: 12196797
Well friend,
  Here is what I tried. It is working fine.

  A Dummy table.

 
CREATE TABLE [dbo].[TestTable1] (
      [ID] [int] NULL ,
      [DummyCol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


INSERT INTO TestTable1
                      (ID, DummyCol)
VALUES     (1, '''12345''')

It is inserting fine.

So the value inside the table now is

1, '12345'

Acutally that single quote is also there in the table now.

So if you want to have this from your application,

You can write as

mysql2 = @"Insert into mytbl(col1, col2)" +
" values ('''" + mystr1 + "''' , '''"  +  mystr2 + "''')";

  You have to have 3 single quote and one double quote. Watchout.
  Also I have added a "@" symbol. This will help C# to compile it easiler considering this statment.

Below is the documentation i found for you to show why @ symbol is good to use.

**************************************************
String literals are of type string and can be written in two forms, quoted and @-quoted. Quoted string literals are enclosed in double quotation marks ("):

"good morning"  // a string literal

and can contain any character literal, including escape sequences:

string a = "\\\u0066\n";  // backslash, letter f, new line

Note   The escape code \udddd (where dddd is a four-digit number) represents the Unicode character U+dddd. Eight-digit Unicode escape codes are also recognized: \udddd\udddd.

@-quoted string literals start with @ and are enclosed in double quotation marks. For example:

@"good morning"  // a string literal

The advantage of @-quoting is that escape sequences are not processed, which makes it easy to write, for example, a fully qualified file name:

@"c:\Docs\Source\a.txt"  // rather than "c:\\Docs\\Source\\a.txt"

To include a double quotation mark in an @-quoted string, double it:

@"""Ahoy!"" cried the captain." // "Ahoy!" cried the captain.

***************************************************

Hope you got it right this time.

Cheers!
Sachi
0
 
LVL 18

Expert Comment

by:armoghan
ID: 12197637
This error always comes up in QA
I would suggest always use the DBCommand

OleDbCommand cmd;

  // Create the SelectCommand.

  cmd = new OleDbCommand("SELECT * FROM Customers " +
                       "WHERE Country = @Country AND City = @City", conn);

  cmd.Parameters.Add("@Country", OleDbType.VarChar, 15);
  cmd.Parameters.Add("@City", OleDbType.VarChar, 15);

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now