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:

> 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 &"

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


Top 10 collection post codes

<%
While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
%>

class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>
class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
%>

<%
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 &"

Re: SQL Select Query help

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)

Re: SQL Select Query help

"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)

Re: SQL Select Query help

"Simon Gare" wrote in message
news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl...
> 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
>
>
> Top 10 collection post codes
>
> <%
> While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
> %>
>
> > class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>
> > class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
> %>
>
> <%
> 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 &"

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" wrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl...
>

>
> 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
>>
>>
>> Top 10 collection post codes
>>
>> <%
>> While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
>> %>
>>
>> >> class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>
>> >> class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
>> %>
>>
>> <%
>> 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 &"

Re: SQL Select Query help

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)

Re: SQL Select Query help

"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)

Re: SQL Select Query help

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)

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" wrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl...
>
> "Simon Gare" wrote in message
> news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
> > Top 10 collection post codes
> >
> > <%
> > While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
> > %>
> >
> > > >
class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>
> > > >
class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
> > %>
> >
> > <%
> > 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 &"

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" wrote in message
news:ei7lOfUMHHA.1252@TK2MSFTNGP02.phx.gbl...
>
> "Simon Gare" wrote in message
> news:%238KjxlRMHHA.1280@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
> > Top 10 collection post codes
> >
> > <%
> > While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
> > %>
> >
> > > >
class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>
> > > >
class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
> > %>
> >
> > <%
> > 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 &"

Re: SQL Select Query help

"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)

Re: SQL Select Query help

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)

Re: SQL Select Query help

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"

Re: SQL Select Query help

"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)