February 2, 2005 at 2:27 pm
I have a stored procedure that uses an ASP.NET(C#) form to insert a record into a sql table (named TABLE).
I'm developing another ASP.NET(C#) form which will update a record in a sql table (named TABLE).
My "Update_Table" stored procedure won't save due to an "ADO error: line 17: Incorrect syntax near '('." Line 17 is the line right above "Values".
any idea what's wrong with this (I've also included the working stored procedure below)? and what is "ADO"
tks/Mark
CREATE PROCEDURE dbo.Update_Table
(
@Priority varchar (7),
@EditsIssues varchar (8000),
@Date_Signed datetime,
@Final varchar (500),
@FINAL_DOC image
)
AS
UPDATE TABLE
(
PRIORITY,
EDITSISSUES,
DATE_SIGNED,
FINAL,
FINAL_DOC
)
Values
(
@Priority,
@EditsIssues,
@Date_Signed,
@Final,
@FINAL_DOC
)
The stored procedure that this was based on (which works fine) is:
ALTER PROCEDURE dbo.Insert_Table
(
@Priority varchar (7),
@EditsIssues varchar (8000),
@Date_Signed datetime,
@Final varchar (500),
@FINAL_DOC image
)
AS
Insert TABLE
(
PRIORITY,
EDITSISSUES,
DATE_SIGNED,
FINAL,
FINAL_DOC
)
Values
(
@Priority,
@EditsIssues,
@Date_Signed,
@Final,
@FINAL_DOC
)
February 2, 2005 at 2:33 pm
You don't use a VALUES list with an Update, and instead use SET to set the column list:
UPDATE TABLE
SET
PRIORITY = @Priority,
EDITSISSUES = @EditsIssues,
DATE_SIGNED = @Date_Signed,
FINAL = @Final,
FINAL_DOC = @FINAL_DOC
WHERE ...
You also need a WHERE clause, otherwise EVERY row in the table gets updated with these values.
See BOL on the UPDATE section.
Also, "TABLE" is not an ideal name for a Table - you should avoid SQL reserved keywords in object names.
February 2, 2005 at 2:59 pm
Hey thanks for the fast response, makes sense but what would be a garden variety "where" statement.
I've got the record open in my form and (coming from the Notes/Domino world) I'd assume that the open record would be the record to be updated?
I initially thought of (and tried) to have a unique identifier column (in the sql table) but I just couldn't figure out how to autoincrement it (by 1) each time a new record was saved to the database. This could be my where statement "Where UNIQUE_ID = ..." but then of course there's the challenge of passing the existing unique id to the stored procedure.
I really appreciate you going through and correcting my code, I'd been Googling for the better part of the day and hadn't found these answers,
-MC
February 2, 2005 at 3:07 pm
For your UNIQUE_ID, read the BOL on defining a column as IDENTITY.
If you Select the UNIQUE_ID to the fonr end as part of the dataset, then it is a simple matter of passing it back to the Update stored proc as an addtional parameter.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy