Niall Niall -4 years ago 95
C# Question

SQL Statement Not Selecting

I'm using SQL Server in a C# project for a troubleshooting program and I have a table that contains ID,Question,QuestionId,Solution and Rank. I want there to be multiple solutions to a problem and the program will choose the best ranked solution, which is just chosen by the highest number which gets incremented every time it is correct. For this I have the following SQL statement:

sql = "SELECT Solution FROM dbo.Questions WHERE Rank=(SELECT MAX(Rank) FROM
dbo.Questions) AND QuestionId =" + questionId;

When I had just one solution available this worked fine, but when I have multiple solutions it doesn't.

Answer Source

You need to properly parameterize your queries. Bobby Tables: A guide to preventing SQL injection

sql = "SELECT Solution FROM dbo.Questions q WHERE Rank=(SELECT MAX(Rank) FROM 
dbo.Questions i where i.QuestionId = q.QuestionId) AND q.QuestionId =" + questionId;

This makes sure the max(rank) returned by the subquery is the max(rank) of the solution for the QuestionId you are querying for.

You can also do this if you just want one Solution:

select top 1 Solution 
from dbo.Questions q 
where QuestionId = @QuestionId
order by [Rank] desc
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download