Run time Exception with SQL Statement in VB (ASP.NET)

Question

I have this SQL Statement:

de.SqlStatement = "SELECT A.Name, A.Catagory , COUNT(Patient_ID) AS PNO FROM PatientApplyToAssociation P INNER JOIN Association A on A.Association_ID = P.Association_ID WHERE A.Catagory='" & "health" & " '" & "' GROUP BY A.Name '"

with this Exception:

System.Data.SqlClient.SqlException was unhandled by user code
Class=16
ErrorCode=-2146232060
LineNumber=1
Message=Column 'Association.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I had this exception when I added the where clause, It seems that the compiler cant see the Group By Section , because of the quotation ending before it, I tried to write it like this:

de.SqlStatement = "SELECT A.Name, A.Catagory , COUNT(Patient_ID) AS PNO FROM PatientApplyToAssociation P INNER JOIN Association A on A.Association_ID = P.Association_ID WHERE A.Catagory='" & "health" & "' GROUP BY A.Name '" & " '"

But it was an Incorrect syntax near ' '.

Any ideas?

Ben · Accepted Answer · 2011-12-05 23:39:53Z

3

I'm pretty sure you don't want the ' around the GROUP BY clause:

SELECT A.Name, A.Catagory
     , COUNT(Patient_ID) AS PNO 
  FROM PatientApplyToAssociation P 
 INNER JOIN Association A 
    ON A.Association_ID = P.Association_ID 
 WHERE A.Catagory='" & "health" & " '" & " GROUP BY A.Name

edited Dec 5, 2011 at 23:39

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Dec 5, 2011 at 23:22

drdwilcox

3,96121 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

competent_tech · Accepted Answer · 2011-12-06 00:13:37Z

In order to use the statement in your original question, you will need to revise it to be:

de.SqlStatement = "SELECT A.Name, A.Catagory, COUNT(1) AS PNO FROM PatientApplyToAssociation P INNER JOIN Association A on A.Association_ID = P.Association_ID WHERE A.Catagory='health' GROUP BY A.Name, A.Catagory"

There are 3 changes in this statement:

1) The extraneous string joins in the where and group by have been removed.

2) Group by A.Catagory has been added so that you will not receive another error similar to error in the question for this field.

3) COUNT(Patient_ID) has been changed to COUNT(1) as a possibly better practice. If the field you are counting contains nulls, it will not be included in the count. If this is the desired behavior, then your previous code was correct; otherwise you should use the revision.

Collectives™ on Stack Overflow

Run time Exception with SQL Statement in VB (ASP.NET)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related