SQL Select Query help

Discussion in 'ASP General' started by Simon Gare, Jan 5, 2007.

  1. Simon Gare

    Simon Gare Guest

    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
     
    Simon Gare, Jan 5, 2007
    #1
    1. Advertising

  2. Simon Gare

    Evertjan. Guest

    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)
     
    Evertjan., Jan 5, 2007
    #2
    1. Advertising

  3. Simon Gare

    Simon Gare Guest

    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("CountOfCOLL_POST_CODE").Value)
    %></td>
    </tr>
    <%
    Repeat1__index=Repeat1__index+1
    Repeat1__numRows=Repeat1__numRows-1
    AreaColl.MoveNext()
    Wend
    %>

    Thanks in advance


    "Evertjan." <> wrote in message
    news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    > 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)
     
    Simon Gare, Jan 5, 2007
    #3
  4. Simon Gare

    Evertjan. Guest

    Simon Gare wrote on 05 jan 2007 in
    microsoft.public.inetserver.asp.general:

    > "Evertjan." <> wrote in message
    > news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    >> 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]

    > Thanks Evertjan, having a problem though could you look below and
    > suggest.
    >
    > Set AreaColl = Server.CreateObject("ADODB.Recordset")


    > <%=(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.

    > .... 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)
     
    Evertjan., Jan 5, 2007
    #4
  5. Simon Gare

    Mark McGinty Guest

    "Evertjan." <> wrote in message
    news:Xns98B022E385DEeejj99@194.109.133.242...
    > Simon Gare wrote on 05 jan 2007 in
    > microsoft.public.inetserver.asp.general:
    >
    >> "Evertjan." <> wrote in message
    >> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    >>> 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]
    >
    >> Thanks Evertjan, having a problem though could you look below and
    >> suggest.
    >>
    >> Set AreaColl = Server.CreateObject("ADODB.Recordset")

    >
    >> <%=(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



    >> .... 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)
     
    Mark McGinty, Jan 6, 2007
    #5
  6. Simon Gare

    Mark McGinty Guest

    "Simon Gare" <> wrote in message
    news:%...
    > Thanks Evertjan, having a problem though could you look below and suggest.


    What is the problem?

    [more comments inline...]


    > <%
    > 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.

    > 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



    > 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("CountOfCOLL_POST_CODE").Value)
    > %></td>
    > </tr>
    > <%
    > Repeat1__index=Repeat1__index+1
    > Repeat1__numRows=Repeat1__numRows-1
    > AreaColl.MoveNext()
    > Wend
    > %>
    >
    > Thanks in advance
    >
    >
    > "Evertjan." <> wrote in message
    > news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    >> 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)

    >
    >
     
    Mark McGinty, Jan 6, 2007
    #6
  7. Simon Gare

    Mike Brind Guest

    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" <> wrote in message
    news:...
    >


    >
    > 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
    >
    >
    >
    >> 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("CountOfCOLL_POST_CODE").Value)
    >> %></td>
    >> </tr>
    >> <%
    >> Repeat1__index=Repeat1__index+1
    >> Repeat1__numRows=Repeat1__numRows-1
    >> AreaColl.MoveNext()
    >> Wend
    >> %>
    >>
    >> Thanks in advance
    >>
    >>
    >> "Evertjan." <> wrote in message
    >> news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    >>> 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)

    >>
    >>

    >
    >
     
    Mike Brind, Jan 6, 2007
    #7
  8. Simon Gare

    Evertjan. Guest

    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?

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jan 6, 2007
    #8
  9. Simon Gare

    Mark McGinty Guest

    "Evertjan." <> wrote in message
    news:Xns98B067CC9A574eejj99@194.109.133.242...
    > 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.


    -Mark


    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    Mark McGinty, Jan 6, 2007
    #9
  10. Simon Gare

    Evertjan. Guest

    Mark McGinty wrote on 06 jan 2007 in
    microsoft.public.inetserver.asp.general:

    >
    > "Evertjan." <> wrote in message
    > news:Xns98B067CC9A574eejj99@194.109.133.242...
    >> 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.


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jan 6, 2007
    #10
  11. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:...
    >
    > "Simon Gare" <> wrote in message
    > news:%...
    > > Thanks Evertjan, having a problem though could you look below and

    suggest.
    >
    > What is the problem?
    >
    > [more comments inline...]
    >
    >
    > > <%
    > > 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.
    >
    > > 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
    >
    >
    >
    > > 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("CountOfCOLL_POST_CODE").Value)
    > > %></td>
    > > </tr>
    > > <%
    > > Repeat1__index=Repeat1__index+1
    > > Repeat1__numRows=Repeat1__numRows-1
    > > AreaColl.MoveNext()
    > > Wend
    > > %>
    > >
    > > Thanks in advance
    > >
    > >
    > > "Evertjan." <> wrote in message
    > > news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    > >> 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)

    > >
    > >

    >
    >
     
    Simon Gare, Jan 7, 2007
    #11
  12. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:...
    >
    > "Simon Gare" <> wrote in message
    > news:%...
    > > Thanks Evertjan, having a problem though could you look below and

    suggest.
    >
    > What is the problem?
    >
    > [more comments inline...]
    >
    >
    > > <%
    > > 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.
    >
    > > 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
    >
    >
    >
    > > 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("CountOfCOLL_POST_CODE").Value)
    > > %></td>
    > > </tr>
    > > <%
    > > Repeat1__index=Repeat1__index+1
    > > Repeat1__numRows=Repeat1__numRows-1
    > > AreaColl.MoveNext()
    > > Wend
    > > %>
    > >
    > > Thanks in advance
    > >
    > >
    > > "Evertjan." <> wrote in message
    > > news:Xns98AFEAF8F82A2eejj99@194.109.133.242...
    > >> 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)

    > >
    > >

    >
    >
     
    Simon Gare, Jan 7, 2007
    #12
  13. Simon Gare

    Mark McGinty Guest

    "Evertjan." <> wrote in message
    news:Xns98B0E351487Eeejj99@194.109.133.242...
    > Mark McGinty wrote on 06 jan 2007 in
    > microsoft.public.inetserver.asp.general:
    >
    >>
    >> "Evertjan." <> wrote in message
    >> news:Xns98B067CC9A574eejj99@194.109.133.242...
    >>> 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


    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    Mark McGinty, Jan 7, 2007
    #13
  14. Simon Gare

    Evertjan. Guest

    Mark McGinty wrote on 07 jan 2007 in
    microsoft.public.inetserver.asp.general:

    > 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? ;-)

    > 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)
     
    Evertjan., Jan 7, 2007
    #14
  15. Evertjan. wrote:
    > 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
    >
    > 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"
     
    Bob Barrows [MVP], Jan 7, 2007
    #15
  16. Simon Gare

    Mark McGinty Guest

    "Evertjan." <> wrote in message
    news:Xns98B1748D4E7EFeejj99@194.109.133.242...
    > Mark McGinty wrote on 07 jan 2007 in
    > microsoft.public.inetserver.asp.general:
    >
    >> 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? ;-)
    >
    >> 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.


    > 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



    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    Mark McGinty, Jan 7, 2007
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stephan Bour

    Select SQL query column

    Stephan Bour, Nov 3, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    4,700
    Stephan Bour
    Nov 3, 2003
  2. Anonymous
    Replies:
    0
    Views:
    1,548
    Anonymous
    Oct 13, 2005
  3. dada
    Replies:
    0
    Views:
    164
  4. Guy Hocking

    ASP / SQL Query - Conditional SELECT Statement

    Guy Hocking, Jan 21, 2004, in forum: ASP General
    Replies:
    7
    Views:
    214
    Bob Barrows
    Jan 21, 2004
  5. palmiere
    Replies:
    1
    Views:
    471
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page