CODEWITHSUNDEEP
c#asp.netsql-server-2008
Arti
Arti

Reputation: 3071

Dynamically generate SQL query based on selection in ASP.net

I have a table 1:

enter image description here

and table 2 :

enter image description here

Each question can have multiple choices (ex: questionId 1 has two choices choiceId 1 and 2).

I have a form which will allow users to select multiple choices like this:

enter image description here

There is also a table for answers:

enter image description here

Now my question is: I want to form a SQL query based on the selection and show the count of selected answers.(ex: I want count of answers having gender as female and education as grade 1). How do I do that?

I tried something like this:

select * from answers where (questId =2 and choiceIndex='2.0000') and (questId =3 and choiceIndex='1.0000') and someId=72

This query does not give me the correct count as same choiceIndex can exists for a different choice.

How can I achieve this? Also user can selected many choices from the form so is there any way by which I can dynamically generate my SQL query?

Eidt

Thanks @Marc Gravell for the solution. It helped a lot. Now once I achieved this I want to include arithmetic operators in my query. (ex: I want count of answers having gender as female + education as grade 1 - education as grade3).

Is there a way by which I can include arithmetic operators(+, -, *, /, %, count, average) in my query?

Scenario:I have designed a form in which a user will generate formula from database values. (ex:user will select choice1[female] + choice2[grade 1] * choice3[grade3] and generate formula will be saved in DB for further use)

I tried like this:

 select ((select COUNT(*) from answers where (questId =2 and answer='1.0000') and someId=72) +
 (select COUNT(*) from answers where (questId =3 and answer='1.0000') and someId=72))

In this query I am trying to perform add operation on two select statements. In the same way can I do *,/,percentage etc?

Is there a better way to do this?

Upvotes: 0

Views: 1125

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062965

choiceIndex is never going to be 2.0000 and 1.0000 at the same time; I assume, therefore, that you mean:

select * from answers where (
    (questId=2 and choiceIndex='2.0000') or
    (questId=3 and choiceIndex='1.0000')
) and someId=72

Note that if you are building a query based on input values, you should probably parameterize it, so it ends up as:

select * from answers where (
    (questId=@questId0 and choiceIndex=@choiceIndex0) or
    (questId=@questId1 and choiceIndex=@choiceIndex1)
) and someId=@someId

having added parameters with values:

  • @questId0 => 2
  • @questId1 => 3
  • @choiceIndex0 => '2.0000'
  • @choiceIndex1 => '3.0000'
  • @someId => 72

Upvotes: 1

Related Questions