inserting string with quotes

Hi Guys,
i want to insert a string for example 'abcd'edfg'gg'into a
table in sql server 2000.
but it is not working but giving the error
"not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are
not permitted."
will this require any sp_configue or db_option changes?
pls advice me.Use two single quotes instead of one like: 'abcd''edfg''gg'
Anith

Inserting row with single quote

I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.

What would be the best way to work around this, other than read the line and replace it with a space.

In a literal you need to double embedded quotes, so it'd be 'O''Riordan'.

The best solution is to use a parameterized query, then you don't have to worry about embedded quotes.

|||How to use parameterized query? the program i currently using are java.. can show me some examples on how to use parameterized query. ^-^|||

Parameterized query is very useful but you can also use string concatenation. I've written a sample on my blog on how to insert records with single quotes. Here's a sample TSQL script

PRINT 'This is the man''' + 's choice'

In order to do this, you use a two-single quote approach. Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.

|||

Take a look at these links

http://msdn2.microsoft.com/en-us/library/ms378878.aspx

http://msdn2.microsoft.com/en-us/library/ms378138.aspx

Hope this helps

|||Sorry.. after looking thru the website i still dun know how cause my sql statement is like that..

String query = "INSERT INTO addresses (" +
"company, address, name, hp, " +
"phone, fax, email, start, day, month, year, " +
"mrc, isp, des, sale, mark" +
") VALUES ('" +
fields.company.getText() + "', '" +
fields.address.getText() + "', '" +
fields.name.getText() + "', '" +
fields.hp.getText() + "', '" +
fields.phone.getText() + "', '" +
fields.fax.getText() + "', '" +
fields.email.getText() + "', '" +
fields.start.getText() + "', " +
Integer.valueOf(dd) + ", " +
Integer.valueOf(mm) + ", " +
Integer.valueOf(yy) + ", '" +
fields.mrc.getText() + "', '" +
fields.isp.getText() + "', '" +
fields.des.getText() + "', '" +
fields.sale.getText() + "', '" +
fields.mark.getText() + "')";

I may need to type S'pore in the fields.address.getText() but instead of ' i need to type " which is very troublesome... in this way how should i replace the " to ' ?

|||If you're not comfortable with this approach, create a function in your application that replaces the single quote to double quote. The sure-st way there is is to create a parameterized query

Inserting row with single quote

I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.

What would be the best way to work around this, other than read the line and replace it with a space.

In a literal you need to double embedded quotes, so it'd be 'O''Riordan'.

The best solution is to use a parameterized query, then you don't have to worry about embedded quotes.

|||How to use parameterized query? the program i currently using are java.. can show me some examples on how to use parameterized query. ^-^|||

Parameterized query is very useful but you can also use string concatenation. I've written a sample on my blog on how to insert records with single quotes. Here's a sample TSQL script

PRINT 'This is the man''' + 's choice'

In order to do this, you use a two-single quote approach. Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.

|||

Take a look at these links

http://msdn2.microsoft.com/en-us/library/ms378878.aspx

http://msdn2.microsoft.com/en-us/library/ms378138.aspx

Hope this helps

|||Sorry.. after looking thru the website i still dun know how cause my sql statement is like that..

String query = "INSERT INTO addresses (" +
"company, address, name, hp, " +
"phone, fax, email, start, day, month, year, " +
"mrc, isp, des, sale, mark" +
") VALUES ('" +
fields.company.getText() + "', '" +
fields.address.getText() + "', '" +
fields.name.getText() + "', '" +
fields.hp.getText() + "', '" +
fields.phone.getText() + "', '" +
fields.fax.getText() + "', '" +
fields.email.getText() + "', '" +
fields.start.getText() + "', " +
Integer.valueOf(dd) + ", " +
Integer.valueOf(mm) + ", " +
Integer.valueOf(yy) + ", '" +
fields.mrc.getText() + "', '" +
fields.isp.getText() + "', '" +
fields.des.getText() + "', '" +
fields.sale.getText() + "', '" +
fields.mark.getText() + "')";

I may need to type S'pore in the fields.address.getText() but instead of ' i need to type " which is very troublesome... in this way how should i replace the " to ' ?

|||If you're not comfortable with this approach, create a function in your application that replaces the single quote to double quote. The sure-st way there is is to create a parameterized query

Inserting or Updating a View

In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View?
For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
Woof has Columns WoofID(key) and WoofName(nvarchar).
SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
I make a view:
SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
dbo.zSonOfWoof.SonOfWoofName,
dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
dbo.zGrandSonOfWoof.zGrandSonOfWoofName
FROM dbo.zWoof INNER JOIN
dbo.zSonOfWoof ON dbo.zWoof.WoofID =
dbo.zSonOfWoof.WoofID INNER JOIN
dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
dbo.zGrandSonOfWoof.zSonOfWoofID
But I can not find a way to use a Stored procedure to insert a column to
GrandsonOfWoof, for example.
Any recommendation would be greatly appreciated.. (Do I use an "Indexed
View"?)
TIA,
Paul
(woof!)On Thu, 26 Jan 2006 10:32:09 -0500, Paul wrote:

>In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View
?
>For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
>Woof has Columns WoofID(key) and WoofName(nvarchar).
>SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
>GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
>GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
>I make a view:
>SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID
,
>dbo.zSonOfWoof.SonOfWoofName,
> dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
>dbo.zGrandSonOfWoof.zGrandSonOfWoofName
>FROM dbo.zWoof INNER JOIN
> dbo.zSonOfWoof ON dbo.zWoof.WoofID =
>dbo.zSonOfWoof.WoofID INNER JOIN
> dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
>dbo.zGrandSonOfWoof.zSonOfWoofID
>But I can not find a way to use a Stored procedure to insert a column to
>GrandsonOfWoof, for example.
>Any recommendation would be greatly appreciated.. (Do I use an "Indexed
>View"?)
>TIA,
>Paul
>(woof!)
>
Hi Paul (meow),
You can't insert rows in the tables through this view. Since the view
shows data from three tables, an INSERT might be equivalent to an INSERT
in all three base tables - and that is not supported.
If you can just insert into the base tables, do so.
If you really *have* to insert through this view, then check out INSTEAD
OF triggers. They are described in Books Online. If the help there is
not enough to get you going, then by all means come back here for more
help - but in that case, I'll have to ask you to use CREATE TABLE and
INSERT statements to explain your situation. See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP