Question SQL problem...

[Samurai X]

Hello!

I am trying to populate a gridview in my asp.net webform with average scores retrieved from my database.

The code i have written so far is as follows....

Dim Count As Integer
Dim myBM As String

Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ../SR.mdb"

sql = "Select Distinct BMno From Skills"

DShold = StatDC.SelectData(sql, con)

Count = DShold.Tables(0).Rows.Count
Count = Count - 1

Do While Count >= 0

myBM = Convert.ToString(DShold.Tables(0).Rows(Count)("BMno"))

sql = "SELECT Avg(Competence) AS CompetenceAvg" _
& " FROM Skills" _
& " Where BMno = BM401582"

DSstat = StatDC.SelectData(sql, con)

Count = Count - 1

Loop

gv1.DataSource = DSstat
gv1.DataBind()

My problem is that it doesnt like the SQL staement Select AVG(competennce)..... I have run the query in access and it runs fine?

Also i would like to add the record returned to the dataset DSstat i think how i have got it at the minute will just replace the contents of the dataset with the results of the query. so how would i add a record?

I hope that i have made some kind of sense!

Thanks for looking and any help!!

Iain

 

[Herman]

Modify your query:

SELECT AVG(Competence) FROM Skills AS CompetenceAvg WHERE BMno = 'BM401582'

Access and SQL have different query syntax for almost everything except the simplest queries. Also don't forget the single quotes around the string.

 

[Samurai X]

Modify your query:

SELECT AVG(Competence) FROM Skills AS CompetenceAvg WHERE BMno = 'BM401582'

Access and SQL have different query syntax for almost everything except the simplest queries. Also don't forget the single quotes around the string.

Hello, Yeah i got ot working thanks!

however i have run into amnother problem...

the sql i have is
Select BMno, AVG(Competence) As AvgComp From Skills Where BMno = 'BM401582' GROUP BY BMno

This works fine

but i now need to search by two fields

Select BMno, AVG(Competence) As AvgComp From Skills Where BMno = 'BM401582' And 'BM401530' GROUP BY BMno


this doesn't work! Is there a problem with the way I have written the SQL?


Thanks!

 

[Herman]

Look at my original reply. The AS needs to be after the FROM.

 

[Samurai X]

Ok Thanks, i'll try it wil the AS moved ofter the from statment!