Sql query doesn't display results only messages

Question

0.00/5 (No votes)

See more:

I made an sql query for a transaction, when I execute the query it is successful however it only displays a message not the results of the query. How do I change it so the results of the query are displayed when I execute it.

Heres the query

SQL

BEGIN TRAN

UPDATE  Fish
SET FishType = 'Cod'
WHERE   id = '3'

COMMIT TRAN

Posted 8-Jan-12 2:27am

Youngguns

Updated 8-Jan-12 2:57am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2012-01-08T02:30:00

Solution 1

Without the query its quite impossible to figure out what is going on.
However, make sure you have values that can be returned after applying all the where clauses (from the query) to the tables.

Posted 8-Jan-12 2:30am

Abhinav S

Comments

Wendelius 8-Jan-12 8:48am

So true, 5+.

Abhinav S 8-Jan-12 23:46pm

Thanks.

Youngguns 8-Jan-12 8:58am

I've added my query to the question.

Monjurul Habib 8-Jan-12 9:54am

5!

Abhinav S 8-Jan-12 23:46pm

Thanks.

VJ Reddy 13-May-12 20:55pm

Good point. 5!

Abhinav S 14-May-12 1:57am

Thank you.

Wendelius · Accepted Answer · 2012-01-08T02:47:00

Solution 2

As Abhinav said, you should post the relevant pieces of code. Also if you're for example calling a stored procedure, check that you don't have NOEXEC on, see: SET NOEXEC[^]

Posted 8-Jan-12 2:47am

Wendelius

Comments

Youngguns 8-Jan-12 8:58am

I've added my query to the question.

Wendelius 8-Jan-12 9:02am

Since this is an UPDATE statement, it isn't supposed to return the contents of the modified rows, just the amounf of the rows affected. If you want it to return some actual modified data you have to include OUTPUT clause, see: OUTPUT Clause[^]

Youngguns 8-Jan-12 9:13am

Would I also need to use the output clause if it was an insert statement?

Wendelius 8-Jan-12 9:19am

Yes, if you want to return the values. In many cases the output isn't needed since the program knows what's added but if triggers etc are involved the using OUTPUT is the solution to get the values. Another option would be to select the row again, but that would require an extra round-trip.

Monjurul Habib 8-Jan-12 9:53am

5!

Wendelius 8-Jan-12 10:13am

Thanks Monjurul :)

VJ Reddy 13-May-12 20:54pm

Good answer. 5!

Wendelius 14-May-12 0:18am

Thank you

Monjurul Habib · Accepted Answer · 2012-01-08T03:53:00

Solution 3

Mika already provide a good solution for using OUTPUT Clause. I just want to add one more if you are using SQL Server 2008:
"SQL Server 2005 introduced the new features of after update, after insert and after delete triggers that almost solved the problem of tracking changes in data. A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC)." Ref. : Introduction to Change Data Capture (CDC) in SQL Server 2008

Posted 8-Jan-12 3:53am

Monjurul Habib

v2

Comments

Abhinav S 8-Jan-12 23:47pm

Good additional point. For some reason system is just not allowing me to vote a 5.

Monjurul Habib 9-Jan-12 1:34am

thank you

Wendelius 8-Jan-12 23:57pm

Good addition. I would also vote but seems that there are big problems with CP working right now

Monjurul Habib 9-Jan-12 1:35am

thank you

Wendelius 9-Jan-12 13:45pm

And now the vote should also be in place since it seemed to work again :)

Monjurul Habib 9-Jan-12 14:17pm

:)

VJ Reddy 13-May-12 20:55pm

Good addition. 5!

Monjurul Habib 14-May-12 3:20am

thank you :)