469,607 Members | 2,207 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,607 developers. It's quick & easy.

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
Jan 5 '07 #1
15 3123
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)
Jan 5 '07 #2
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." <ex**************@interxnl.netwrote in message
news:Xn********************@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)

Jan 5 '07 #3
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)
Jan 5 '07 #4

"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn*******************@194.109.133.242...
Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:
>"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)

Jan 6 '07 #5

"Simon Gare" <sg@simongare.comwrote in message
news:%2****************@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
<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." <ex**************@interxnl.netwrote in message
news:Xn********************@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)


Jan 6 '07 #6
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" <mm******@spamfromyou.comwrote in message
news:ei**************@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
<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." <ex**************@interxnl.netwrote in message
news:Xn********************@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)



Jan 6 '07 #7
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)
Jan 6 '07 #8

"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)

Jan 6 '07 #9
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:
>
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)
Jan 6 '07 #10
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" <mm******@spamfromyou.comwrote in message
news:ei**************@TK2MSFTNGP02.phx.gbl...
>
"Simon Gare" <sg@simongare.comwrote in message
news:%2****************@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
<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." <ex**************@interxnl.netwrote in message
news:Xn********************@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)


Jan 7 '07 #11
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" <mm******@spamfromyou.comwrote in message
news:ei**************@TK2MSFTNGP02.phx.gbl...
>
"Simon Gare" <sg@simongare.comwrote in message
news:%2****************@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
<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." <ex**************@interxnl.netwrote in message
news:Xn********************@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)


Jan 7 '07 #12

"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn*******************@194.109.133.242...
Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:
>>
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)

Jan 7 '07 #13
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)
Jan 7 '07 #14
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"
Jan 7 '07 #15

"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@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)

Jan 7 '07 #16

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.