MS SQL syntax

[Martin]

I could really use some help with this. I have the most ordinary UPDATE statements, but SQL server is giving me "incorrect syntax".

for example...
UPDATE mapdata set parentID = '<something>' where nodeID = <something>"

is returning " Incorrect syntax near '='., ".

interesting thing is, an update statment without any WHERE clauses works fine.


I had a similar problem earlier... "delete * FROM table" wasn't working, but when I changed it to "DELETE FROM table" it works.

Anybody got a clue? Google isn't helping me.

 

[91TTZ]

Yes, I'll take Potent Potables for $500, Alex.


(hey, if I can't answer your question, at least I could give you a free bump...)

 

[Gunslinger08]

What's the data type of nodeID? Sounds like it might be a char/varchar/string, which needs ' ' around it.

delete * from is incorrect syntax.. that's why it didn't work. You can't selectively delete columns that way.

 

[Deeko]

the delete statement works that way cuz you're taking out the whole row...as for the update statement, that looks about right to me...

 

[labgeek]

I'm assuming the missing quote is a typo...

Also watch ' vs "

 

[NikPreviousAcct]

Here is the correct syntax.

 

[torpid]

Need quotes around nodeId's value assuming it is a char field. Delete from table deletes all rows unless you put in a where clause; I assume you know this.

 

[Martin]

no, its not the var types. The program was working fine with an ACCESS/ODBC connection. then we got a new sql server and needed to move everything there, so we just made an ODBC connection point to SQL server instead of Access. This caused some problems, but this UPDATE thing is the only thing left :

its just not making any sense....

 

[EmperorIQ]

I'm assuming youre using a language like php or asp to connect and execute your queries, try priting the query, and see what happens.

for example in php you may have

<?php

$ID1 = 0;
$ID2 = 1;

$update = "UPDATE set ID = $ID1 WHERE another_id = $ID2";

?>

a problem may occur if either one of your variables $ID1 or $ID2 is empty.

 

[torpid]

OK, so:

1) You in fact DO have a mapdata table where the parentID is a char but not the nodeID?
2) You in fact DID paste the SQL or just typed some random syntax expecting us to troubleshoot? You realize you don't have a space after the end quote for the parentID right?
3) Why are you using ODBC instead of OLEDB (or, if it's .net, a native SQL driver)?

 

[GeekDrew]

EmperorIQ's suggestion is something that is probably the case; also, check to see if you need to have ` marks around field names.... such as

UPDATE `mapdata` SET `parentID` = '<something>' WHERE `nodeID` = <something>

<edit>

No clue if this applies to you. I'm only familiar with MySQL and PHP.

 

[GeekDrew]

Originally posted by: torpid
[snip]You realize you don't have a space after the end quote for the parentID right?

Not OP, but... huh?

 

[Xede]

Listen to EmeperorIQ and GeekDrew. It's either:

1) You're using a blank value for NodeID
2) You're using a non-numeric value for NodeID
3) You're using a numeric value for NodeID, but NodeID is actually a character field, so you need single quotes around the value.

Output the actual query to see what you're feeding it.

 

[Suspicious-Teach8788]

Easiest way to check. SSH in or use phpMyAdmin and type in the SQL query. Check teh errors.

Finally, if you're doing this in PHP or any language, just make sure you print/echo your variables before hand. DEBUG!

 

[tfinch2]

Originally posted by: Nik
Here is the correct syntax.

FTFW!

 

[Martin]

ok, so it turns out the problem was that the nodID field lost its Autoincrement property when it was moved from access to MSSQL.

bah.