quotes in a query only kills INSERT, not UPDATE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

quotes in a query only kills INSERT, not UPDATE

Author

Topic

storemike
Starting Member

10 Posts

Posted - 2003-10-15 : 15:09:11

Here's one that's been puzzling me fo a while. Most of my code has been used with Oracle and MySQL, but breaks with MSSQL 7 and 2000. As an example, this bit works fine:

update JOBS set TITLE="Leg Breaker" where ID=23

but this doesn't:

insert into JOBS(TITLE) values("Leg Breaker")

however, this does:

insert into JOBS(TITLE) values('Leg Breaker')

Can anyone explain why the first statement works and the second one doesn't? The eror I get from the second query is:
Server: Msg 128, Level 15, State 1, Line 1
The name 'Leg Breaker' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-15 : 15:16:27

The UPDATE statement fails when I try to run it.

You need to use single quotes instead of double quotes.

Here is the code that I used:



SET QUOTED_IDENTIFIER ON

CREATE TABLE JOBS
(
ID INT IDENTITY(1, 1) NOT NULL,
TITLE VARCHAR(50) NOT NULL
)




insert into JOBS(TITLE) values('Leg Breaker')

update JOBS set TITLE="Leg Breaker" where ID=1

select * from jobs

DROP TABLE JOBS

You use double qoutes for things like this:

SELECT Column1 AS "Some Column"
FROM Table1

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-15 : 15:41:29

quote:
Originally posted by storemike

As an example, this bit works fine:

Works where? Not is SQL Server....

quotes tell sql server that you are going to reference an object name...

quotes and [] do this

Try this
USE Northwind
GO
SELECT * FROM "Order Details"
GO
SELECT * FROM [Order Details]
GO

do the same thing...

3,000+? fingers...can't...keep...up

but then your'e probably using more than 2

Brett

8-)

storemike
Starting Member

10 Posts

Posted - 2003-10-16 : 09:53:39

OK...I guess I'll just have to face the music and change all of my queries to use single quotes instead of double quotes. I just find it strange that updates and selects both work with double quotes (on Sql Server 2K and 7.0) and the SQL 2K server pukes on inserts only. (SQLServer 7 has no problems whatsoever) Kooky, eh?

Thanks,
Mike

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-16 : 12:31:45

Mike, UPDATEs do not work with double quotes, at least not on SQL 2k. Run the code that I posted and see. If you get errors, change my code so that you don't get errors so that we can see what the difference is.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-16 : 13:07:13

quote:
Originally posted by storemike

OK...I guess I'll just have to face the music

This just in...man faces music..

[Thus sprak zarathustra]
Open the Pod bay doors hal
[/Thus sprak zarathustra]

Are you telling me...and I don't care what version...that any of the following work?


SELECT * FROM Orders WHERE CustomerId = "VINET"
UPDATE Orders SET CustomerId = "VINET" WHERE CustomerId = 'VINET'
INSERT INTO Orders (OrderId,CustomerId) VALUES(1,"VINET")
DELETE FROM Orders WHERE CustomerId = "VINET"

Not a chance...only place I ever saw double quotes was in Access...

Brett

8-)

storemike
Starting Member

10 Posts

Posted - 2003-10-16 : 14:01:12

You're both right...in the query analyzer, anything with double quotes dies. What I'm seeing could be due to the fact that I'm using PHP as my language of choice. Maybe something the interpreter is modifying the queries. So, to make sure everything continues to work, I'll go on to change all of my sql statements to use single quotes.

Thanks for the help.

Mike

Lavos
Posting Yak Master

200 Posts

Posted - 2003-10-16 : 17:59:33

It's certainly possible that it's using an odbc driver or something equally silly that is converting it to a string, then properly deliminating it. Run SQL Profiler to see the exact statement passed to the server.

Double quotes in ANSI SQL are used to indicate objects, and should work for that purpose for any database that has a certain level of ANSI compliance. (This is why I prefer to use them instead of brackets in SQL Server, though to my eyes the brackets look soooo much better since I do a lot of code in C#. Bad enough I keep getting my commenting styles switched :) )

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources