SQL Select Query help | |
Hi,
trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I don't
want the query to count individual post codes but instead look at an area
found in the first 3 digits e.g. HA0 3TD is for a particular house but HA)
is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS CountOfCOLL_POST_CODE,
COLL_POST_CODE FROM dbo.booking_form GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POST_CODE DESC"
Regards
Simon Gare
The Gare Group Limited
website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk | | | | re: SQL Select Query help
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general: Quote:
Hi,
>
trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
>
>
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
You should mention the db-engine used for a correct answer.
I use this with the Jet engine:
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
'''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress) | | | | re: SQL Select Query help
Thanks Evertjan, having a problem though could you look below and suggest.
<%
Dim AreaColl
Dim AreaColl_numRows
Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()
AreaColl_numRows = 0
%>
and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>
Thanks in advance
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
> Quote:
Hi,
trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>
You should mention the db-engine used for a correct answer.
>
I use this with the Jet engine:
>
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
>
'''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
>
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
>
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
| | | | re: SQL Select Query help
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general: Quote:
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
>Simon Gare wrote on 05 jan 2007 in
>microsoft.public.inetserver.asp.general:
>>
>SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
> " FROM myTbl GROUP BY left(postcode,3)"
[Please do not toppost on usenet] Quote:
Thanks Evertjan, having a problem though could you look below and
suggest.
>
Set AreaColl = Server.CreateObject("ADODB.Recordset")
Quote:
<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE"). Value)%>
I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those long
names with multiple _'s and unnecessary ()'s. Quote:
.... having a problem though could you look below and
suggest.
If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.
btw, did my code work with you?
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress) | | | | re: SQL Select Query help
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98B022E385DEeejj99@194.109.133.242... Quote:
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
> Quote:
>"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
>news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
>>Simon Gare wrote on 05 jan 2007 in
>>microsoft.public.inetserver.asp.general:
>>>
>>SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>> " FROM myTbl GROUP BY left(postcode,3)"
>
[Please do not toppost on usenet]
> Quote:
>Thanks Evertjan, having a problem though could you look below and
>suggest.
>>
>Set AreaColl = Server.CreateObject("ADODB.Recordset")
> Quote:
><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE") .Value)%>
>
I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those
long
names with multiple _'s and unnecessary ()'s.
What do you use instead?
-Mark Quote: Quote:
>.... having a problem though could you look below and
>suggest.
>
If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.
>
btw, did my code work with you?
>
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
| | | | re: SQL Select Query help
"Simon Gare" <sg@simongare.comwrote in message
news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl... Quote:
Thanks Evertjan, having a problem though could you look below and suggest.
What is the problem?
[more comments inline...] Quote:
<%
Dim AreaColl
Dim AreaColl_numRows
>
Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
You should create an explicit connection object, rather than relying on ADO
to create one for you implicitly. Quote:
AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))
I doubt this is legal, LEFT expects varchar or text, COUNT returns int. I
think what you want is:
SELECT COUNT(*), LEFT(COLL_POST_CODE, 3) FROM dbo.booking_form GROUP BY
LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*) DESC
You might want to consider defining a computed column for the left 3 of the
postal code, for both ease of reference and db server efficiency.
-Mark Quote:
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()
>
AreaColl_numRows = 0
%>
>
and in the body
>
>
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>
>
Thanks in advance
>
>
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
>Simon Gare wrote on 05 jan 2007 in
>microsoft.public.inetserver.asp.general:
>> Quote:
Hi,
>
trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
>
>
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>>
>You should mention the db-engine used for a correct answer.
>>
>I use this with the Jet engine:
>>
>SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
> " FROM myTbl GROUP BY left(postcode,3)"
>>
>'''response.write SQL &"<hr>"
>set mDATA=CONNECT.Execute(SQL)
>>
>Response.Write "<table border=1><tr>" & vbcrlf
> Do Until mDATA.Eof
> tal = mDATA("tal")
> pc = mDATA("pc")
> if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
> Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
> mDATA.MoveNext
> Loop
>Response.Write "</table>" & vbcrlf
>>
>--
>Evertjan.
>The Netherlands.
>(Please change the x'es to dots in my emailaddress)
>
>
| | | | re: SQL Select Query help
Good idea. For a direct marketing app, I created a column which took the
first 2 letters of the postcode, which made more localised selections
easier. For a higher level of granularity, I would suggest all those
characters to the left of the space. The first 3 won't always work. BS2 is
in the centre of Bristol and BS21 is Clevedon - over 20 miles away for
instance, but would both be included in a search for Left(PostCode,3) =
"BS2"
--
Mike Brind
"Mark McGinty" <mmcginty@spamfromyou.comwrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl... Quote:
>
You might want to consider defining a computed column for the left 3 of
the postal code, for both ease of reference and db server efficiency.
>
>
-Mark
>
>
> Quote:
>AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
>COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>AreaColl.CursorType = 0
>AreaColl.CursorLocation = 2
>AreaColl.LockType = 1
>AreaColl.Open()
>>
>AreaColl_numRows = 0
>%>
>>
>and in the body
>>
>>
><td colspan=2>Top 10 collection post codes</td>
> </tr>
> <%
>While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
>%>
> <tr>
> <td width="100"
>class="DataSetText"><%=(AreaColl.Fields.Item("COL L_POST_CODE").Value)%></td>
> <td width="790"
>class="DataSetText"><%=(AreaColl.Fields.Item("Cou ntOfCOLL_POST_CODE").Value)
>%></td>
> </tr>
> <%
> Repeat1__index=Repeat1__index+1
> Repeat1__numRows=Repeat1__numRows-1
> AreaColl.MoveNext()
>Wend
>%>
>>
>Thanks in advance
>>
>>
>"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
>news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
>>Simon Gare wrote on 05 jan 2007 in
>>microsoft.public.inetserver.asp.general:
>>>
>Hi,
>>
>trying to retrieve postal codes from the db but only want the query to
>look at the first 3 digits of the code tried using
>(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
>don't want the query to count individual post codes but instead look
>at an area found in the first 3 digits e.g. HA0 3TD is for a
>particular house but HA) is for the area Harrow.
>>
>>
>"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
>CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
>COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>>>
>>You should mention the db-engine used for a correct answer.
>>>
>>I use this with the Jet engine:
>>>
>>SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
>> " FROM myTbl GROUP BY left(postcode,3)"
>>>
>>'''response.write SQL &"<hr>"
>>set mDATA=CONNECT.Execute(SQL)
>>>
>>Response.Write "<table border=1><tr>" & vbcrlf
>> Do Until mDATA.Eof
>> tal = mDATA("tal")
>> pc = mDATA("pc")
>> if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
>> Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
>> mDATA.MoveNext
>> Loop
>>Response.Write "</table>" & vbcrlf
>>>
>>--
>>Evertjan.
>>The Netherlands.
>>(Please change the x'es to dots in my emailaddress)
>>
>>
>
>
| | | | re: SQL Select Query help
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general: Quote: Quote:
>I never use a Recordset [you can easily do without it]
>and the code you show is much to complex for me to read with all those
>long
>names with multiple _'s and unnecessary ()'s.
>
What do you use instead?
Of what?
The multiple _'s or unnecessary ()'s?
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress) | | | | re: SQL Select Query help
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98B067CC9A574eejj99@194.109.133.242... Quote:
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:
> Quote: Quote:
>>I never use a Recordset [you can easily do without it]
>>and the code you show is much to complex for me to read with all those
>>long
>>names with multiple _'s and unnecessary ()'s.
>>
>What do you use instead?
>
Of what?
>
The multiple _'s or unnecessary ()'s?
Instead of Recordset.
-Mark Quote:
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
| | | | re: SQL Select Query help
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general: Quote:
>
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98B067CC9A574eejj99@194.109.133.242... Quote:
>Mark McGinty wrote on 06 jan 2007 in
>microsoft.public.inetserver.asp.general:
>> Quote:
>>>I never use a Recordset [you can easily do without it]
>>>and the code you show is much to complex for me to read with all those
>>>long names with multiple _'s and unnecessary ()'s.
>>>
>>What do you use instead?
>>
>Of what?
>>
>The multiple _'s or unnecessary ()'s?
>
Instead of Recordset.
Ah, that's what you mean. [I would never have guessed]
Well nothing.
The execute() command already gives me the info I nead when reading with
sql SELECT, and with the UPDATE and INSERT SQL string it does a good job
for writing to the db.
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress) | | | | re: SQL Select Query help
Hi Mark,
thanks for that works perfectly in the rs test but how do I display the data
on the page?
before it was
<%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)% >
and
<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE"). Value)%>
now there is no listing in the rsAreaColl
Any ideas?
Regards
Simon
"Mark McGinty" <mmcginty@spamfromyou.comwrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl... Quote:
>
"Simon Gare" <sg@simongare.comwrote in message
news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl... Quote:
Thanks Evertjan, having a problem though could you look below and
suggest. Quote:
>
What is the problem?
>
[more comments inline...]
>
> Quote:
<%
Dim AreaColl
Dim AreaColl_numRows
Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
>
You should create an explicit connection object, rather than relying on
ADO Quote:
to create one for you implicitly.
> Quote:
AreaColl.Source = "SELECT
(Left(Count(dbo.booking_form.COLL_POST_CODE),3)) Quote:
>
I doubt this is legal, LEFT expects varchar or text, COUNT returns int. I
think what you want is:
>
SELECT COUNT(*), LEFT(COLL_POST_CODE, 3) FROM dbo.booking_form GROUP BY
LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*) DESC
>
You might want to consider defining a computed column for the left 3 of
the Quote:
postal code, for both ease of reference and db server efficiency.
>
>
-Mark
>
>
> Quote:
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()
AreaColl_numRows = 0
%>
and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td> class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value) Quote: Quote:
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>
Thanks in advance
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
>
Hi,
trying to retrieve postal codes from the db but only want the query
to Quote: Quote: Quote:
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>
You should mention the db-engine used for a correct answer.
>
I use this with the Jet engine:
>
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
>
'''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
>
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
>
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
>
>
| | | | re: SQL Select Query help
Thanks guys just solved it
AreaColl.Source = "SELECT COUNT(*)AS COUNT, LEFT(COLL_POST_CODE, 3) AS PC
FROM dbo.booking_form GROUP BY LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*)
DESC"
Thanks for all your help its been driving me mad for 2 days.
Regards
Simon
"Mark McGinty" <mmcginty@spamfromyou.comwrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl... Quote:
>
"Simon Gare" <sg@simongare.comwrote in message
news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl... Quote:
Thanks Evertjan, having a problem though could you look below and
suggest. Quote:
>
What is the problem?
>
[more comments inline...]
>
> Quote:
<%
Dim AreaColl
Dim AreaColl_numRows
Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
>
You should create an explicit connection object, rather than relying on
ADO Quote:
to create one for you implicitly.
> Quote:
AreaColl.Source = "SELECT
(Left(Count(dbo.booking_form.COLL_POST_CODE),3)) Quote:
>
I doubt this is legal, LEFT expects varchar or text, COUNT returns int. I
think what you want is:
>
SELECT COUNT(*), LEFT(COLL_POST_CODE, 3) FROM dbo.booking_form GROUP BY
LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*) DESC
>
You might want to consider defining a computed column for the left 3 of
the Quote:
postal code, for both ease of reference and db server efficiency.
>
>
-Mark
>
>
> Quote:
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()
AreaColl_numRows = 0
%>
and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL _POST_CODE").Value)%></td> class="DataSetText"><%=(AreaColl.Fields.Item("Coun tOfCOLL_POST_CODE").Value) Quote: Quote:
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>
Thanks in advance
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98AFEAF8F82A2eejj99@194.109.133.242... Quote:
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
>
Hi,
trying to retrieve postal codes from the db but only want the query
to Quote: Quote: Quote:
look at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
>
You should mention the db-engine used for a correct answer.
>
I use this with the Jet engine:
>
SQL = "SELECT left(postcode,3) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3)"
>
'''response.write SQL &"<hr>"
set mDATA=CONNECT.Execute(SQL)
>
Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td><b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf
>
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
>
>
| | | | re: SQL Select Query help
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98B0E351487Eeejj99@194.109.133.242... Quote:
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:
> Quote:
>>
>"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
>news:Xns98B067CC9A574eejj99@194.109.133.242... Quote:
>>Mark McGinty wrote on 06 jan 2007 in
>>microsoft.public.inetserver.asp.general:
>>>
>>>>I never use a Recordset [you can easily do without it]
>>>>and the code you show is much to complex for me to read with all those
>>>>long names with multiple _'s and unnecessary ()'s.
>>>>
>>>What do you use instead?
>>>
>>Of what?
>>>
>>The multiple _'s or unnecessary ()'s?
>>
>Instead of Recordset.
>
Ah, that's what you mean. [I would never have guessed]
>
Well nothing.
>
The execute() command already gives me the info I nead when reading with
sql SELECT, and with the UPDATE and INSERT SQL string it does a good job
for writing to the db.
ADODB.Connection.Execute returns an object of type ADODB.Recordset.
var cn = new ActiveXObject("ADODB.Connection");
cn.Open("Provider=[...]");
var obj = cn.Execute("SELECT [...]");
In the example above, "obj" is, in fact, a recordset.
Point being that whether or not you explicitly create a recordset is
inconsequential. Lack of explicit creation does not mean that you never use
recordset, rather, it means that you use it [apparently] without knowing
what you have used.
-Mark Quote:
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
| | | | re: SQL Select Query help
Mark McGinty wrote on 07 jan 2007 in
microsoft.public.inetserver.asp.general: Quote:
ADODB.Connection.Execute returns an object of type ADODB.Recordset.
>
var cn = new ActiveXObject("ADODB.Connection");
cn.Open("Provider=[...]");
var obj = cn.Execute("SELECT [...]");
>
In the example above, "obj" is, in fact, a recordset.
What's in a name? ;-) Quote:
Point being that whether or not you explicitly create a recordset is
inconsequential. Lack of explicit creation does not mean that you
never use recordset, rather, it means that you use it [apparently]
without knowing what you have used.
>
This gets interesting.
Why do all these people declare/create recordsets
if it is inconsequential?
Do they get additional benefits?
I never felt the need sofar, Mark.
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress) | | | | re: SQL Select Query help
Evertjan. wrote: Quote:
This gets interesting.
>
Why do all these people declare/create recordsets
if it is inconsequential?
1. That's how many of the online samples they've seen show it to be done
or,
2. They need a non-default cursor type Quote:
>
Do they get additional benefits?
>
Sure, they gain the ability to set cursor properties before opening it.
Granted, if all you need is a default server-side forward-only cursor, and
you are planning to use the Execute() method anyways, then it is, indeed, a
waste of time to instantiate a recordset object.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" | | | | re: SQL Select Query help
"Evertjan." <exjxw.hannivoort@interxnl.netwrote in message
news:Xns98B1748D4E7EFeejj99@194.109.133.242... Quote:
Mark McGinty wrote on 07 jan 2007 in
microsoft.public.inetserver.asp.general:
> Quote:
>ADODB.Connection.Execute returns an object of type ADODB.Recordset.
>>
> var cn = new ActiveXObject("ADODB.Connection");
> cn.Open("Provider=[...]");
> var obj = cn.Execute("SELECT [...]");
>>
>In the example above, "obj" is, in fact, a recordset.
>
What's in a name? ;-)
> Quote:
>Point being that whether or not you explicitly create a recordset is
>inconsequential. Lack of explicit creation does not mean that you
>never use recordset, rather, it means that you use it [apparently]
>without knowing what you have used.
>>
>
This gets interesting.
>
Why do all these people declare/create recordsets
if it is inconsequential?
What I meant was, it's inconsequential in determining whether or not
recordset is used by any given code. It can be returned by other objects. Quote:
Do they get additional benefits?
>
I never felt the need sofar, Mark.
An explicitly created recordset has more cursor and lock option
possibilities than does the default recordset returned by
Connection.Execute -- which is just a "firehose" (forward-only, read-only.)
For example, if you need to traverse the recordset more than once, and/or
call MovePrevious/MoveFirst/MoveLast, you'd need to create an explicit
recordset, connect it, and open it with appropriate parameters to make it
capable of bidirectional scrolling. Another reason would be to open a
persisted recordset from XML, or some other stream.
If you use GetString and/or GetRows a lot, you might not ever miss
explicitly creating recordsets (even though you are still most definitely
using them.)
-Mark Quote:
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
|  |
Similar ASP / Active Server Pages bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,439 network members.
|
