|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
using 2 sql commands in 1 string?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. -- "OK - I've inserted the second disk... now the're BOTH stuck :(
Show quote
"Lewis" <Le***@discussions.microsoft.com> wrote in message Yes. But there's a killer feature of SQL 2005 that makes this unnecessary: news:AFA62A62-CB8B-4E59-8549-FCED9348A277@microsoft.com... >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. 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? -- Show quote"OK - I've inserted the second disk... now the're BOTH stuck :( "David Browne" wrote: > > "Lewis" <Le***@discussions.microsoft.com> wrote in message > news:AFA62A62-CB8B-4E59-8549-FCED9348A277@microsoft.com... > >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. > > > 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 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 -- Show quote"OK - I've inserted the second disk... now the're BOTH stuck :( "David Browne" wrote: > > "Lewis" <Le***@discussions.microsoft.com> wrote in message > news:AFA62A62-CB8B-4E59-8549-FCED9348A277@microsoft.com... > >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. > > > 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 > > > 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 Show quote "Lewis" wrote: > 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. > > -- > "OK - I've inserted the second disk... now the're BOTH stuck :( Thanks Elton,
I had tried using ; but it gave me a parse error. Perhaps I had a second error... -- Show quote"OK - I've inserted the second disk... now the're BOTH stuck :( "Elton W" wrote: > 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 > > "Lewis" wrote: > > > 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. > > > > -- > > "OK - I've inserted the second disk... now the're BOTH stuck :( It suppose work. Anyway, you can use Stored Procedure to achieve it.
HTH Elton Wang Show quote "Lewis" wrote: > Thanks Elton, > I had tried using ; but it gave me a parse error. Perhaps I had a second > error... > -- > "OK - I've inserted the second disk... now the're BOTH stuck :( > > > "Elton W" wrote: > > > 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 > > > > "Lewis" wrote: > > > > > 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. > > > > > > -- > > > "OK - I've inserted the second disk... now the're BOTH stuck :(  |
|||||||||||||||||||||||
Other interesting topics