SQL SELECT statement syntax

[sea]

I'm using this SQL SELECT statement: int surveyNo sqlTotal= string.Format("SELECT COUNT(survey{0}) FROM ResultsTbl WHERE survey{0}<>0", surveyNo); I know the syntax is wrong in the parentheses after the COUNT. How do I fix this syntax, to put the variable value in? sea#

[Dave Kreskowiak]

First, you should be using parameterized queries to do this. Your SQL statement should look like this:

SELECT COUNT survey FROM ResultsTbl WHERE survey = @surveyNum

The @surveyNum is a placeholder for a SQL parameter. More information can be found in this[^] article by Colin Angus Mackay. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[sea]

I need to bring the column no. as parameter. The table has 4 columns: username, survey1, survey2, survey3. SrNo is an int variable the method get. I want to find out How do take this SrNo and "glue" it with survey This is what I got from the article sqlTotal= "SELECT COUNT(survey@SrNo) FROM ResultsTbl "+ "WHERE survey@SrNo<>0"; cmdTotal.Parameters.Add("0", survey@SrNo); not to sue about survey@SrNo syntax. Got error in cmdTotal.Parameters.Add("0", survey@SrNo); under survey@SrNo. sea#

[Dave Kreskowiak]

sea# wrote: sqlTotal= "SELECT COUNT(survey@SrNo) FROM ResultsTbl "+ "WHERE survey@SrNo<>0"; cmdTotal.Parameters.Add("0", survey@SrNo); Parameters don't work that way. I don't know what your trying to return and I don't know what the conditions are. You need to explain how the data is setup in your table and what you want to accomplish. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[sea]

This is my full method: public void CalcResults(int SrNo, out int total, out int Ans1, out int Ans2, out int Ans3) { string sqlTotal; string sqlAns1; string sqlAns2; string sqlAns3; //SQL statements if (SrNo>=1 & SrNo<=3) { sqlTotal= string.Format("SELECT COUNT(survey2) FROM ResultsTbl WHERE survey{0}<>0",SrNo); sqlAns1= string.Format("SELECT COUNT(survey2) FROM ResultsTbl WHERE survey{0}=1",SrNo); sqlAns2= string.Format("SELECT COUNT(survey2) FROM ResultsTbl WHERE survey{0}=2",SrNo); sqlAns3= string.Format("SELECT COUNT(survey2) FROM ResultsTbl WHERE survey{0}=3",SrNo); } else sqlAns1=sqlAns2=sqlAns3=sqlTotal="No answer was chosen"; //Command objects OleDbCommand cmdTotal = new OleDbCommand(sqlAns1, cnSurvey); OleDbCommand cmdAns1=new OleDbCommand(); OleDbCommand cmdAns2=new OleDbCommand(); OleDbCommand cmdAns3=new OleDbCommand(); cmdTotal.Connection = cnSurvey; cmdTotal.CommandType = CommandType.Text; cmdTotal.CommandText = sqlTotal; cmdAns1.Connection = cnSurvey; cmdAns1.CommandType = CommandType.Text; cmdAns1.CommandText = sqlAns1; cmdAns2.Connection = cnSurvey; cmdAns2.CommandType = CommandType.Text; cmdAns2.CommandText = sqlAns2; cmdAns3.Connection = cnSurvey; cmdAns3.CommandType = CommandType.Text; cmdAns3.CommandText = sqlAns3; //Connect cnSurvey.Open(); //Execute command total = int.Parse(cmdTotal.ExecuteScalar().ToString()); Ans1 = int.Parse(cmdAns1.ExecuteScalar().ToString()); Ans2 = int.Parse(cmdAns2.ExecuteScalar().ToString()); Ans3 = int.Parse(cmdAns3.ExecuteScalar().ToString()); //Disconnect cnSurvey.Close(); } The data is table like: username | survey1 | survey2 | survey3 ----------------------------------------------- sea# | 1 | 3 | 2 These are answers of a user to a survey. I need to collect all results from all users +total, so I can calculate statistics on it etc. TIA, sea#