SQL String will not work in ASP.NET!!

Hi!

I have some problems with this SQL string in ASP.NET:
It works fine with Access 2002:
sql = _
" select avg(cdbl(iif(resultat is null,0,resultat))/cdbl(iif(serier is null,1,serier))) as snitt ," & _
" s.namn, " & _
" s.ID," & _
" sum(serier) as totalt_serier," & _
" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'H') as snitt_hemma," & _
" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'B') as snitt_borta," & _
" sum(iif(H_B='H',serier,0)) as serier_hemma, " & _
" sum(iif(H_B='B',serier,0)) as serier_borta, " & _
" cdbl(sum(poang))/cdbl(sum(serier)) as poang_snitt, " & _
" max(resultat) as basta, " & _
" min(iif(serier = 4,resultat,null)) as samsta from medlemmar as s inner join resultat as r on s.Id = r.spelare " & _
" group by s.namn, id ORDER BY 1 DESC"

but when i changed it to ASP.NET it wont work.
Error in first row.

sqlCmd = "SELECT avg(cdbl iif(resultat is null,0,resultat))/cdbl(iif(serier is null,1,1,serier)) as snitt";
sqlCmd+="s.namn";
sqlCmd +="s.ID";
sqlCmd +="sum(serier) as totalt_serier";
sqlCmd +="(select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B ='H') as snitt_hemma";
sqlCmd +="(select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B ='B') as snitt_borta";
sqlCmd +="sum(iif(H_B='H',serier,0)) as serier_hemma,";
sqlCmd +="cdbl(sum(poang))/cdbl(sum(serier)) as poang_snitt,";
sqlCmd +="max(resultat) as basta,";
sqlCmd +="min(iif(serier = 4,resultat,null)) as samsta from medlemmar as s inner join resultat as r on s.Id = r.spelare";
sqlCmd +="GROUP BY s.namn, id ORDER BY 1 DESC";

Thanks in advance
Gert

SQL Server doesn't support iif statements, you need to use a Case statement. Like this

I'm using Access in my project, not SQL Server!

Can i do so in Access

Since you are using Access. Use that sql statement to create a query, then just call the query from your asp.net application.

It looks like you are losing your spaces (between statements) when you converted to ASP.NET

S-

Ok I changed in db so i'll don't have to convert. Now it work fine testing in MSDE but get error to Query in ACCESS


SELECT TOP 100 PERCENT AVG(DISTINCT r.Resultat / r.Serier) AS Snitt, s.FNamn, s.Id, SUM(r.Serier) AS Total_serier,
(SELECT AVG(resultat / serier)
FROM resultat
WHERE Spelare = s.ID AND H_B = 'H') AS Snitt_Hemma,
(SELECT AVG(resultat / serier)
FROM resultat
WHERE Spelare = s.ID AND H_B = 'B') AS Snitt_Borta
FROM dbo.Medlemmar s INNER JOIN
dbo.Resultat r ON s.Id = r.Spelare
GROUP BY s.FNamn, s.Id
ORDER BY Snitt DESC

ACCESS:

string sqlCmd = "SELECT AVG(DISTINCT r.Resultat / r.Serier) AS Snitt";
sqlCmd+="s.Namn,SUM(r.Serier) AS Total_serier, s.Id,";
sqlCmd+="(SELECT AVG(r.resultat / r.Serier) FROM resultat WHERE Spelare = s.ID AND H_B = 'H') AS Snitt_Hemma ,";
sqlCmd+="(SELECT AVG(r.resultat / r.Serier) FROM resultat WHERE Spelare = s.ID AND H_B = 'B') AS Snitt_Borta ,";
sqlCmd+="SUM(r.Poang)/SUM(r.Serier) AS Poang_Snitt , MAX(r.Resultat) AS Basta , MIN(r.Resultat) AS Samsta ,";
sqlCmd+="(SELECT SUM(Serier) FROM resultat WHERE H_B = 'H') AS serier_hemma ,";
sqlCmd+="(SELECT SUM(Serier) FROM resultat WHERE H_B = 'B') AS Serier_Borta";
sqlCmd+="FROM Medlemmar AS s INNER JOIN Resultat AS r ON s.Id = r.Spelare";
sqlCmd+="GROUP BY s.Namn , s.Id ORDER BY s.Namn DESC";

Operator missing AVG(DISTINCT r.Resultat / r.Serier) what means with that?
Best reg

This SELECT made my DataGrid to work out just the way i want it:

string SqlCmd = "SELECT m.Namn, Round(avg(IIF(resultat is null,0,resultat)/IIF(serier is null,1,serier)),1) as Snitt, SUM(r.Serier) AS [Antal serier], (SELECT ROUND(AVG(resultat / serier),1) FROM resultat WHERE Spelare = ID AND H_B = 'H') AS Hemmasnitt, SUM(iif(H_B='H',r.serier,0)) as [Serier hemma],(SELECT ROUND(AVG(resultat / serier),1) FROM resultat WHERE Spelare = ID AND H_B = 'B') AS Bortasnitt, SUM(iif(H_B='B',r.serier,0)) as [Serier borta], ROUND(SUM(r.poang)/SUM(r.serier),2) as Poängsnitt, MAX(r.resultat) AS Bästa, MIN(IIF(serier = 4,r.resultat,null)) as Sämsta FROM Medlemmar AS m INNER JOIN Resultat AS r ON m.Id = r.Spelare GROUP BY m.Namn,id ORDER BY 2 DESC";