SQL: Write 24 rows undependent of how many sows sql returns.

[phredator]

Hello i have a sql question, it looks like this.

sQuery = "SELECT COUNT(ID) as CNT, datepart(hh,TimeStamp) FROM CS WHERE ID=? GROUP BY datepart(hh,TimeStamp)"
 
......
 
While oReader.Read()
 
  strXML = strXML & "<set name='" & oReader.GetInt32(1) & "' value='" & oReader.GetInt32(0) & "' />"
 
End While
 

This does the job. But, i want to write out 24 of this

"<set name='00' value='0' />"
"<set name='02' value='0' />"
"<set name='03' value='0' />"
"<set name='04' value='0' />"
 
and so on...
 

as you can see i group by hh then you may know what im looking for, i want to whrite out all 24 hours and when the reader has a row with a value i want to whrite this:

"<set name='" & oReader.GetInt32(1) & "' value='" & oReader.GetInt32(0) & "' />"
 

I hope you understand my question and my english, if you dont please tell me.

Regards
Phred

[ck9663]

your query turns out right...writing it out 24x would depend with your APPS...

-- ck

[phredator]

oops, sorry. i think i posted this in the wrong category :/

it should be under .NET or ASP maybe ?

its a ASP.NET 1.x app.

/Phred