[RESOLVED] COUNT DISTINCT & Progressbar Error

[Radjesh Klauke]

Having much troubles with COUNT DISTINCT since yesterday and I can't find any solution on the internet. The weird thing is that I have several errors, but never on the same time. When I think I solved something another error pops.

The SQL-command:

Dim cmd_plaats As New SqlCeCommand("SELECT DISTINCT plaats FROM beslagdata", connStr)
Dim cmd_count_plaats As New SqlCeCommand("SELECT COUNT (plaats) FROM (SELECT DISTINCT plaats FROM beslagdata)", connStr)

With frm_main.ts_progress
    .Value = 0   '  reset the value
    .Maximum = Convert.ToInt32(cmd_count_plaats.ExecuteScalar)    '   count the records
End With

Details:

{"There was an error parsing the query. [ Token line number = 1,Token line offset = 68,Token in error = ) ]"}

Very frustrating. Hope someone sees and could tell me what I'm doing wrong.

[jmcilhinney]

Try naming the result set of your inner query:

SELECT COUNT (plaats) FROM (SELECT DISTINCT plaats FROM beslagdata) R

What you name it isn't important, just that you name.

[Radjesh Klauke]

Thanks for the tip

"SELECT COUNT (plaats) FROM (SELECT DISTINCT plaats FROM beslagdata) As blablabla"

Why do you have to name it (as string) if it isn't needed anywhere, or is that a SQL-standard in the COUNT-case?


What dit you mean with the R btw?

[mickey_pt]

Good practice...

If you need to refer the column anywhere in your code, it's better to have a name that you know instead of a name that was automatically generated by SQL.

The R question i can't answer...

[jmcilhinney]

It's not a string. It's an identifier, like a table name or column name. It has nothing to do with the COUNT function. It assigns a name to the result set of the inner query so it can be treated like a table, which it kinda is. In this case you don't need to use the name so, if it was supported, you could simply omit it but if you were to join that result set with another result set, table or view then you might well need to be able to refer to it to distinguish its columns from those from elsewhere with the same name. Why exactly the decision was made to require I don't know.

I just used R for Result.

[Radjesh Klauke]

Aha. Thank you both for the clear answer.