Trying to filter datagrid according to selections made in dropdown lists

Question

I have a View Feedback page with two drop down lists to filter the gridview of answers to questions. The 1st ddList is Modules, once this is selected, the 2nd ddList of Questions becomes enabled and the user can select a question to see the answers for it in relation to the module selected, OR they can select to see all answers to all questions for the selected module.

I have it working if they select a question but if they select all I simply get all answers, not just the ones specific to the selected module.

Sorry I know thats not the clearest explanation but any help would be awesome.

My tables:

CREATE TABLE tblModules
(
Module_ID nvarchar(10) PRIMARY KEY,
Module_Title nvarchar(MAX) NOT NULL
);

CREATE TABLE tblQuestions
(
Q_ID int PRIMARY KEY IDENTITY(1,1),
Question_Text varchar(1000) NOT NULL

);

CREATE TABLE tblFeedback
(
Submission_ID int PRIMARY KEY IDENTITY(1,1),
Username varchar(100) NOT NULL,
Domain varchar(50) NOT NULL,
DateTime_Submitted datetime NOT NULL
Module_ID nvarchar(10)
FOREIGN KEY (Module_ID) REFERENCES tblModules (Module_ID);
);

CREATE TABLE tblAnswers
(
Q_ID int NOT NULL,
Submission_ID int NOT NULL,
Answer_Text varchar(max),
FOREIGN KEY (Q_ID) REFERENCES tblQuestions(Q_ID),
FOREIGN KEY (Submission_ID) REFERENCES tblFeedback(Submission_ID)
);

Here is the snippet of code I'm using to construct the Sql statement which is used to select the data which is then bound to my grid.

                // If they have selected view all questions, get all answers for module
                if (ddQuestions.SelectedItem.Value == "all")
                {
                    //selectQuery = "SELECT * FROM tblAnswers ORDER BY Submission_ID";
                    selective = false;
                    //gridviewFeedback.Columns[3].Visible = true;

                    selectQuery = "SELECT * FROM tblAnswers A ";
                    selectQuery += "WHERE EXISTS (SELECT * FROM tblModules M JOIN tblFeedback F ON M.Module_ID = F.Module_ID ";
                    selectQuery += "WHERE F.Module_ID = '" + this.selectedModuleID + "')";


                }
                // Instead, if they have selected a specific question, get the information for the selected module and question
                else
                {
                    selectQuery = "SELECT * FROM tblAnswers WHERE Q_ID = '" + qID + "' ORDER BY Submission_ID";
                    selective = true;
                    //gridviewFeedback.Columns[3].Visible = false;
                }


                DataSet objDs = new DataSet();
                SqlDataAdapter myCommand2;
                myCommand2 = new SqlDataAdapter(selectQuery, myConnection);

                myCommand2.SelectCommand.CommandType = CommandType.Text;
                myCommand2.Fill(objDs);

                gridviewFeedback.DataSource = objDs;
                gridviewFeedback.DataBind();

I think its my SQL statement as I'm not very experienced at SQL and have kind of edited a statement I used elsewhere that was given to me by someone else.

UPDATE Just realised my other statement isnt working either - its not providing module specific answers either.

Answer 1

You don't seem to have correlated table A into Table M or Table F. This will give you all rows in Table A if there are any modules or feedback anywhere with the provided module ID. You need to add an AND statement to correlate table A with what's in your EXISTS clause.

selectQuery = @"
    SELECT * FROM tblAnswers A 
    WHERE EXISTS (
            SELECT * FROM tblModules M 
            JOIN tblFeedback F ON M.Module_ID = F.Module_ID 
            WHERE F.Module_ID = @ModuleID
            AND   A.Submission_ID = F.Submission_ID)";

Also please consider using parameterized queries instead of appending variables into your strings. This causes SQL Server to use the plan cache more efficiently, which is faster, and also eliminates the possibility of a SQL injection attack.

Answer 2

Try this query instead. I think this should do it. This should help you get all the answers belonging to a specific module

SELECT ans.* FROM tblAnswers ans, tblFeedback fb
WHERE ans.Submission_ID = fb.Submission_ID
AND fb.Module_ID = 'selected module'