using 2 sql commands in 1 string?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using SQL server express 2005 and sending the string as an SQL text
query from C# 2005.
Perhaps I am missing something, but when I use

UPDATE SomLast_num
SET CUST_NUM = CUST_NUM + 1
WHERE (CurrentRec = 1)

I get a respose of 1 as an integer. (I assume it means 1 record affected)

When I try to get the new record number by sending

UPDATE SomLast_num SET CUST_NUM = CUST_NUM + 1 WHERE CurrentRec = 1
SELECT CUST_NUM FROM SomLast_num WHERE CurrentRec = 1

I get an "unable to parse error"
Is there a way to send 2 SQL statements and use the last response as the
return value?
I tried separating them with ; and putting a go between them, but I still
get an unable to parse error.
 
Lewis said:
I am using SQL server express 2005 and sending the string as an SQL text
query from C# 2005.
Perhaps I am missing something, but when I use

UPDATE SomLast_num
SET CUST_NUM = CUST_NUM + 1
WHERE (CurrentRec = 1)

I get a respose of 1 as an integer. (I assume it means 1 record affected)

When I try to get the new record number by sending

UPDATE SomLast_num SET CUST_NUM = CUST_NUM + 1 WHERE CurrentRec = 1
SELECT CUST_NUM FROM SomLast_num WHERE CurrentRec = 1

I get an "unable to parse error"
Is there a way to send 2 SQL statements and use the last response as the
return value?
I tried separating them with ; and putting a go between them, but I still
get an unable to parse error.
Click to expand...


Yes. But there's a killer feature of SQL 2005 that makes this unnecessary:
the OUTPUT clause. You can update the table and return the updated data in
a single statement.

UPDATE SomLast_num
SET CUST_NUM = CUST_NUM + 1
OUTPUT INSERTED.CUST_NUM
WHERE (CurrentRec = 1)


This will return a result set exactly like

SELECT CUST_NUM FROM SomLast_num WHERE CurrentRec = 1

.. This also helps insulate you from concurrency problems that you would
have with your approach.

David
 
That looks cool and the whole Idea was to reduce the concurrency issue.
But now it says I'm missing an INTO statement. Any idea where it goes?
 
That did work.
It gives me that error about a missint INTO statement, but when I call the
command, it increments the number and returns the new number.

Thanks
 
Hi Lewis,

You can run multi sql-commands in one string. But you should use ‘;’ to
separate sql commands:

sqlcommandObj.CommandText = “sql_command_one; sql_command_two;…â€;
sqlcommandObj. ExecuteNonQuery();

HTH

Elton Wang
 
Thanks Elton,
I had tried using ; but it gave me a parse error. Perhaps I had a second
error...
 
Back
Top