CODEWITHSUNDEEP
asp.netsqlvb.net
user1082487
user1082487

Reputation: 37

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

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?

Upvotes: 0

Views: 238

Answers (2)

competent_tech
competent_tech

Reputation: 44971

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.

Upvotes: 2

drdwilcox
drdwilcox

Reputation: 3951

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

Upvotes: 3

Related Questions