|
 |
asp_web_howto thread: help with sloooowww loop code
Message #1 by "Ryan vd Merwe" <ryan@8...> on Fri, 4 Oct 2002 12:02:22
|
|
Hi
I'm sure there is a better way to write this piece of code....
the second 'inner' loop takes very long
<%
do while not objRS.EOF
fromID = objRS("mailFromID")
attachID = objRS("mailAttachID")
tr ="<tr align=left valign=top>" &_
"<td width=10 class=tdsmall bgcolor=#ECE9E9
align=center><IMG SRC=images/" & imageName & "></td>"&_
"<td width=125 class=tdsmall bgcolor=#ECE9E9><a
href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID=" &
typeID & "&mailID=" & mailID & "' target='content'>"
Response.Write tr
cmdSQL.CommandText = "SELECT HIFIusers.userName FROM [HIFIusers]" &_
"INNER JOIN (HIFImail
INNER JOIN HIFImailSentTo ON "&_
"HIFImail.mailID=HIFImailSentTo.mailID)"&_
"ON
HIFIusers.userID=HIFImailSentTo.mailsentToID " &_
"WHERE
(HIFImailSentTo.mailID = " & mailID & " ) order by userName"
objRS2.Open cmdSQL, , 1, 3
do while NOT objRS2.EOF
username = objRS2("userName")
Response.Write username & "; "
objRS2.MoveNext
loop
objRS2.close
Response.Write "</a></td>"
tr = "<td width=140 class=tdsmall bgcolor=#ECE9E9><a
href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID=" &
typeID & "&mailID=" & mailID & "' target='content'>" & mailSubject
& "</a></td>" &_
"<td width=150 class=tdVsmall bgcolor=#ECE9E9>" & mailDate
& "</td>" &_
"</tr>"
Response.write (tr)
objRS.movenext
loop
%>
Message #2 by "Ken Schaefer" <ken@a...> on Tue, 8 Oct 2002 11:14:04 +1000
|
|
Instead of creating a recordsets in a loop (this is very expensive), why
don't you do a JOIN on all the data to end up with just the one recordset?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ryan vd Merwe" <ryan@8...>
Subject: [asp_web_howto] help with sloooowww loop code
: Hi
: I'm sure there is a better way to write this piece of code....
:
: the second 'inner' loop takes very long
:
: <%
:
: do while not objRS.EOF
: fromID = objRS("mailFromID")
: attachID = objRS("mailAttachID")
:
:
: tr ="<tr align=left valign=top>" &_
: "<td width=10 class=tdsmall bgcolor=#ECE9E9
: align=center><IMG SRC=images/" & imageName & "></td>"&_
: "<td width=125 class=tdsmall bgcolor=#ECE9E9><a
: href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID=" &
: typeID & "&mailID=" & mailID & "' target='content'>"
:
: Response.Write tr
:
: cmdSQL.CommandText = "SELECT HIFIusers.userName FROM [HIFIusers]" &_
: "INNER JOIN (HIFImail
: INNER JOIN HIFImailSentTo ON "&_
:
: "HIFImail.mailID=HIFImailSentTo.mailID)"&_
: "ON
: HIFIusers.userID=HIFImailSentTo.mailsentToID " &_
: "WHERE
: (HIFImailSentTo.mailID = " & mailID & " ) order by userName"
:
:
: objRS2.Open cmdSQL, , 1, 3
:
: do while NOT objRS2.EOF
:
: username = objRS2("userName")
:
: Response.Write username & "; "
:
: objRS2.MoveNext
: loop
: objRS2.close
:
: Response.Write "</a></td>"
: tr = "<td width=140 class=tdsmall bgcolor=#ECE9E9><a
: href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID=" &
: typeID & "&mailID=" & mailID & "' target='content'>" & mailSubject
: & "</a></td>" &_
: "<td width=150 class=tdVsmall bgcolor=#ECE9E9>" & mailDate
: & "</td>" &_
: "</tr>"
:
:
:
: Response.write (tr)
: objRS.movenext
: loop
:
: %>
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
Message #3 by "Ryan vd Merwe" <ryan@8...> on Tue, 8 Oct 2002 09:00:03 +0200
|
|
Hi Ken
thanks, but I'm a bit confused as to how that will work
I have a message that is sent to a number of people....I need to see the
message details as well as all the people it was sent to, how do I collect
all the people it was sent to without using a loop statement?
thanks
ryan
----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Tuesday, October 08, 2002 3:14 AM
Subject: [asp_web_howto] Re: help with sloooowww loop code
> Instead of creating a recordsets in a loop (this is very expensive), why
> don't you do a JOIN on all the data to end up with just the one recordset?
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Ryan vd Merwe" <ryan@8...>
> Subject: [asp_web_howto] help with sloooowww loop code
>
>
> : Hi
> : I'm sure there is a better way to write this piece of code....
> :
> : the second 'inner' loop takes very long
> :
> : <%
> :
> : do while not objRS.EOF
> : fromID = objRS("mailFromID")
> : attachID = objRS("mailAttachID")
> :
> :
> : tr ="<tr align=left valign=top>" &_
> : "<td width=10 class=tdsmall bgcolor=#ECE9E9
> : align=center><IMG SRC=images/" & imageName & "></td>"&_
> : "<td width=125 class=tdsmall bgcolor=#ECE9E9><a
> : href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID="
&
> : typeID & "&mailID=" & mailID & "' target='content'>"
> :
> : Response.Write tr
> :
> : cmdSQL.CommandText = "SELECT HIFIusers.userName FROM [HIFIusers]" &_
> : "INNER JOIN (HIFImail
> : INNER JOIN HIFImailSentTo ON "&_
> :
> : "HIFImail.mailID=HIFImailSentTo.mailID)"&_
> : "ON
> : HIFIusers.userID=HIFImailSentTo.mailsentToID " &_
> : "WHERE
> : (HIFImailSentTo.mailID = " & mailID & " ) order by userName"
> :
> :
> : objRS2.Open cmdSQL, , 1, 3
> :
> : do while NOT objRS2.EOF
> :
> : username = objRS2("userName")
> :
> : Response.Write username & "; "
> :
> : objRS2.MoveNext
> : loop
> : objRS2.close
> :
> : Response.Write "</a></td>"
> : tr = "<td width=140 class=tdsmall bgcolor=#ECE9E9><a
> : href='cont_home.asp?mailKind=" & mailKind & "&mailName=sentMail&TypeID="
&
> : typeID & "&mailID=" & mailID & "' target='content'>" & mailSubject
> : & "</a></td>" &_
> : "<td width=150 class=tdVsmall bgcolor=#ECE9E9>" & mailDate
> : & "</td>" &_
> : "</tr>"
> :
> :
> :
> : Response.write (tr)
> : objRS.movenext
> : loop
> :
> : %>
> :
> : ---
> :
> : Improve your web design skills with these new books from Glasshaus.
> :
> : Usable Web Menus
> : http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> : r-20
> : Constructing Accessible Web Sites
> : http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> : r-20
> : Practical JavaScript for the Usable Web
> : http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> : r-20
>
>
>
> ---
>
> Improve your web design skills with these new books from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> r-20
> Constructing Accessible Web Sites
> http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> r-20
> Practical JavaScript for the Usable Web
> http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> r-20
Message #4 by Imar Spaanjaars <Imar@S...> on Tue, 08 Oct 2002 09:04:46 +0200
|
|
Hi Ryan,
You could take a look at data shaping. This allows you to return a
"recordset" as a column in another recordset. Something like:
Name Address [AllSalesRecords]
in one recordset.
You can loop through the outer recordset, and display the Name and Address.
Once you reach the inner recordset, you can loop through those for the
current record in the outer recordset.
For more info, see:
http://www.asp101.com/articles/chris/datashaping/default.asp
Imar
At 09:00 AM 10/8/2002 +0200, you wrote:
>Hi Ken
>
>thanks, but I'm a bit confused as to how that will work
>
>I have a message that is sent to a number of people....I need to see the
>message details as well as all the people it was sent to, how do I collect
>all the people it was sent to without using a loop statement?
>
>thanks
>ryan
Message #5 by "Ken Schaefer" <ken@a...> on Tue, 8 Oct 2002 17:19:05 +1000
|
|
You are using the MailID from the outer recordset in the SQL Query for the
inner recordsets.
Why don't you just do a JOIN to join all the records you need into the one
recordset. You order this recordset by MessageID, and then by UserID.
You loop through this one recordset and use a bit of VBScript logic to work
out the presentation logic (ie
when you have started a new message or not)
Show us the details of your database schema, and the query you use for the
outer recordset, and I can show you how to create a single recordset for
your data.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ryan vd Merwe" <ryan@8...>
Subject: [asp_web_howto] Re: help with sloooowww loop code
: Hi Ken
:
: thanks, but I'm a bit confused as to how that will work
:
: I have a message that is sent to a number of people....I need to see the
: message details as well as all the people it was sent to, how do I collect
: all the people it was sent to without using a loop statement?
:
: thanks
: ryan
:
: ----- Original Message -----
: From: "Ken Schaefer" <ken@a...>
: To: "ASP Web HowTo" <asp_web_howto@p...>
: Sent: Tuesday, October 08, 2002 3:14 AM
: Subject: [asp_web_howto] Re: help with sloooowww loop code
:
:
: > Instead of creating a recordsets in a loop (this is very expensive), why
: > don't you do a JOIN on all the data to end up with just the one
recordset?
: >
: > Cheers
: > Ken
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Ryan vd Merwe" <ryan@8...>
: > Subject: [asp_web_howto] help with sloooowww loop code
: >
: >
: > : Hi
: > : I'm sure there is a better way to write this piece of code....
: > :
: > : the second 'inner' loop takes very long
: > :
: > : <%
: > :
: > : do while not objRS.EOF
: > : fromID = objRS("mailFromID")
: > : attachID = objRS("mailAttachID")
: > :
: > :
: > : tr ="<tr align=left valign=top>" &_
: > : "<td width=10 class=tdsmall bgcolor=#ECE9E9
: > : align=center><IMG SRC=images/" & imageName & "></td>"&_
: > : "<td width=125 class=tdsmall bgcolor=#ECE9E9><a
: > : href='cont_home.asp?mailKind=" & mailKind &
"&mailName=sentMail&TypeID="
: &
: > : typeID & "&mailID=" & mailID & "' target='content'>"
: > :
: > : Response.Write tr
: > :
: > : cmdSQL.CommandText = "SELECT HIFIusers.userName FROM [HIFIusers]" &_
: > : "INNER JOIN (HIFImail
: > : INNER JOIN HIFImailSentTo ON "&_
: > :
: > : "HIFImail.mailID=HIFImailSentTo.mailID)"&_
: > : "ON
: > : HIFIusers.userID=HIFImailSentTo.mailsentToID " &_
: > : "WHERE
: > : (HIFImailSentTo.mailID = " & mailID & " ) order by userName"
: > :
: > :
: > : objRS2.Open cmdSQL, , 1, 3
: > :
: > : do while NOT objRS2.EOF
: > :
: > : username = objRS2("userName")
: > :
: > : Response.Write username & "; "
: > :
: > : objRS2.MoveNext
: > : loop
: > : objRS2.close
: > :
: > : Response.Write "</a></td>"
: > : tr = "<td width=140 class=tdsmall bgcolor=#ECE9E9><a
: > : href='cont_home.asp?mailKind=" & mailKind &
"&mailName=sentMail&TypeID="
: &
: > : typeID & "&mailID=" & mailID & "' target='content'>" & mailSubject
: > : & "</a></td>" &_
: > : "<td width=150 class=tdVsmall bgcolor=#ECE9E9>" & mailDate
: > : & "</td>" &_
: > : "</tr>"
: > :
: > :
: > :
: > : Response.write (tr)
: > : objRS.movenext
: > : loop
: > :
: > : %>
: > :
: > : ---
: > :
: > : Improve your web design skills with these new books from Glasshaus.
: > :
: > : Usable Web Menus
: > :
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: > : r-20
: > : Constructing Accessible Web Sites
: > :
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: > : r-20
: > : Practical JavaScript for the Usable Web
: > :
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: > : r-20
: >
: >
: >
: > ---
: >
: > Improve your web design skills with these new books from Glasshaus.
: >
: > Usable Web Menus
: > http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: > r-20
: > Constructing Accessible Web Sites
: > http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: > r-20
: > Practical JavaScript for the Usable Web
: > http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: > r-20
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
Message #6 by "Ryan vd Merwe" <ryan@8...> on Tue, 8 Oct 2002 23:34:06 +0200
|
|
Hi Imran
I had a look at data shaping the code below works fine on a Access database, but when using it with a SQl 7 database i get the
following error:
"Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Syntax error or access violation
/datashape/default.asp, line 18"
I can't find the problem?
my code is as follows.......
<%
Set objConn = Server.CreateObject("ADODB.Connection")
connString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=hifiCorp;Data Source=mothacow"
objConn.Provider = "MSDataShape"
objConn.Open connString
Dim objRS, objRS2
Dim strShapeSQL
strShapeSQL = "SHAPE {SELECT mailID, mailSubject, mailFromID " & _
" FROM HIFIMail}" & _
" APPEND({SELECT HIFIMailSentTo.mailSentToID, HIFIMailSentTo.mailID, HIFIUsers.userName FROM HIFIMailSentTo, HIFIUsers Where
HIFIMailSentTo.mailSentToID=HIFIUsers.userID} AS hifiMailSentTo " & _
" RELATE mailID TO mailID) "
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strShapeSQL, connString
Do While Not objRS.EOF
Response.Write "<BR>"
Response.Write objRS("mailID") & " "
Response.Write objRS("mailSubject") & "<BR>"
Response.Write " <B>Sent To:</B><BR>"
Set objRS2 = objRS("hifiMailSentTo").Value
If Not objRS2.EOF Then
Do While Not objRS2.EOF
Response.Write " " & _
objRS2("mailSentToID") & "<BR>" &_
objRS2("userName") & "<BR>"
objRS2.MoveNext
Loop
objRS.MoveNext
end if
Loop
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
----- Original Message -----
From: "Imar Spaanjaars" <Imar@S...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Tuesday, October 08, 2002 9:04 AM
Subject: [asp_web_howto] Re: help with sloooowww loop code
> Hi Ryan,
>
> You could take a look at data shaping. This allows you to return a
> "recordset" as a column in another recordset. Something like:
>
>
> Name Address [AllSalesRecords]
>
> in one recordset.
>
> You can loop through the outer recordset, and display the Name and Address.
> Once you reach the inner recordset, you can loop through those for the
> current record in the outer recordset.
>
>
> For more info, see:
>
> http://www.asp101.com/articles/chris/datashaping/default.asp
>
>
> Imar
>
>
>
> At 09:00 AM 10/8/2002 +0200, you wrote:
> >Hi Ken
> >
> >thanks, but I'm a bit confused as to how that will work
> >
> >I have a message that is sent to a number of people....I need to see the
> >message details as well as all the people it was sent to, how do I collect
> >all the people it was sent to without using a loop statement?
> >
> >thanks
> >ryan
>
>
>
>
> ---
>
> Improve your web design skills with these new books from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> r-20
> Constructing Accessible Web Sites
> http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> r-20
> Practical JavaScript for the Usable Web
> http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> r-20
Message #7 by Imar Spaanjaars <Imar@S...> on Wed, 09 Oct 2002 07:51:56 +0200
|
|
Hi Ryan,
Who is Imran?? ;-)
You'll need to adjust your connection string. You need to use a Provider
and a Data Provider.
Checkout
http://www.able-consulting.com/ADO_Conn.htm#MSDataShapeSQLServer
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefmsdatashape.asp
HtH
Imar
At 11:34 PM 10/8/2002 +0200, you wrote:
>Hi Imran
>
>I had a look at data shaping the code below works fine on a Access
>database, but when using it with a SQl 7 database i get the following error:
>
>"Error Type:
>Microsoft OLE DB Provider for SQL Server (0x80040E14)
>Syntax error or access violation
>/datashape/default.asp, line 18"
>
>I can't find the problem?
>
>
>my code is as follows.......
>
><%
>
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> connString="Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Initial Catalog=hifiCorp;Data Source=mothacow"
> objConn.Provider = "MSDataShape"
> objConn.Open connString
>
> Dim objRS, objRS2
>
> Dim strShapeSQL
> strShapeSQL = "SHAPE {SELECT mailID, mailSubject, mailFromID " & _
> " FROM HIFIMail}" & _
> " APPEND({SELECT HIFIMailSentTo.mailSentToID, HIFIMailSentTo.mailID,
> HIFIUsers.userName FROM HIFIMailSentTo, HIFIUsers Where
> HIFIMailSentTo.mailSentToID=HIFIUsers.userID} AS hifiMailSentTo " & _
> " RELATE mailID TO mailID) "
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open strShapeSQL, connString
>
>
> Do While Not objRS.EOF
> Response.Write "<BR>"
> Response.Write objRS("mailID") & " "
> Response.Write objRS("mailSubject") & "<BR>"
>
> Response.Write " <B>Sent To:</B><BR>"
> Set objRS2 = objRS("hifiMailSentTo").Value
>
> If Not objRS2.EOF Then
> Do While Not objRS2.EOF
> Response.Write " " & _
> objRS2("mailSentToID") & "<BR>" &_
> objRS2("userName") & "<BR>"
> objRS2.MoveNext
> Loop
> objRS.MoveNext
> end if
>
> Loop
>
> objRS.Close
> Set objRS = Nothing
>
>
> objConn.Close
> Set objConn = Nothing
>
>%>
>
>----- Original Message -----
>From: "Imar Spaanjaars" <Imar@S...>
>To: "ASP Web HowTo" <asp_web_howto@p...>
>Sent: Tuesday, October 08, 2002 9:04 AM
>Subject: [asp_web_howto] Re: help with sloooowww loop code
>
>
> > Hi Ryan,
> >
> > You could take a look at data shaping. This allows you to return a
> > "recordset" as a column in another recordset. Something like:
> >
> >
> > Name Address [AllSalesRecords]
> >
> > in one recordset.
> >
> > You can loop through the outer recordset, and display the Name and
> Address.
> > Once you reach the inner recordset, you can loop through those for the
> > current record in the outer recordset.
> >
> >
> > For more info, see:
> >
> > http://www.asp101.com/articles/chris/datashaping/default.asp
> >
> >
> > Imar
> >
> >
> >
> > At 09:00 AM 10/8/2002 +0200, you wrote:
> > >Hi Ken
> > >
> > >thanks, but I'm a bit confused as to how that will work
> > >
> > >I have a message that is sent to a number of people....I need to see the
> > >message details as well as all the people it was sent to, how do I collect
> > >all the people it was sent to without using a loop statement?
> > >
> > >thanks
> > >ryan
> >
> >
> >
> >
> > ---
> >
> > Improve your web design skills with these new books from Glasshaus.
> >
> > Usable Web Menus
> > http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> > r-20
> > Constructing Accessible Web Sites
> > http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> > r-20
> > Practical JavaScript for the Usable Web
> > http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> > r-20
>
>
>
>---
>
>Improve your web design skills with these new books from Glasshaus.
>
>Usable Web Menus
>http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
>r-20
>Constructing Accessible Web Sites
>http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
>r-20
>Practical JavaScript for the Usable Web
>http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
>r-20
Message #8 by "Ryan vd Merwe" <ryan@8...> on Wed, 9 Oct 2002 10:00:22 +0200
|
|
Thank you IMAR.....sorry about that it was late last night :)
I have sorted out data shaping and the performance is much better.
I have one question though Ken Schaefer was suggesting using joins to do
this, which one is better performance wise Joins or Data shaping?
----- Original Message -----
From: "Imar Spaanjaars" <Imar@S...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Wednesday, October 09, 2002 7:51 AM
Subject: [asp_web_howto] Re: help with sloooowww loop code
> Hi Ryan,
>
> Who is Imran?? ;-)
>
> You'll need to adjust your connection string. You need to use a Provider
> and a Data Provider.
>
> Checkout
>
> http://www.able-consulting.com/ADO_Conn.htm#MSDataShapeSQLServer
>
> and
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
mdrefmsdatashape.asp
>
>
> HtH
>
> Imar
>
>
>
> At 11:34 PM 10/8/2002 +0200, you wrote:
> >Hi Imran
> >
> >I had a look at data shaping the code below works fine on a Access
> >database, but when using it with a SQl 7 database i get the following
error:
> >
> >"Error Type:
> >Microsoft OLE DB Provider for SQL Server (0x80040E14)
> >Syntax error or access violation
> >/datashape/default.asp, line 18"
> >
> >I can't find the problem?
> >
> >
> >my code is as follows.......
> >
> ><%
> >
> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > connString="Provider=SQLOLEDB.1;Persist Security Info=False;User
> > ID=sa;Initial Catalog=hifiCorp;Data Source=mothacow"
> > objConn.Provider = "MSDataShape"
> > objConn.Open connString
> >
> > Dim objRS, objRS2
> >
> > Dim strShapeSQL
> > strShapeSQL = "SHAPE {SELECT mailID, mailSubject, mailFromID " & _
> > " FROM HIFIMail}" & _
> > " APPEND({SELECT HIFIMailSentTo.mailSentToID, HIFIMailSentTo.mailID,
> > HIFIUsers.userName FROM HIFIMailSentTo, HIFIUsers Where
> > HIFIMailSentTo.mailSentToID=HIFIUsers.userID} AS hifiMailSentTo " & _
> > " RELATE mailID TO mailID) "
> >
> > Set objRS = Server.CreateObject("ADODB.Recordset")
> > objRS.Open strShapeSQL, connString
> >
> >
> > Do While Not objRS.EOF
> > Response.Write "<BR>"
> > Response.Write objRS("mailID") & " "
> > Response.Write objRS("mailSubject") & "<BR>"
> >
> > Response.Write " <B>Sent To:</B><BR>"
> > Set objRS2 = objRS("hifiMailSentTo").Value
> >
> > If Not objRS2.EOF Then
> > Do While Not objRS2.EOF
> > Response.Write " " & _
> > objRS2("mailSentToID") & "<BR>" &_
> > objRS2("userName") & "<BR>"
> > objRS2.MoveNext
> > Loop
> > objRS.MoveNext
> > end if
> >
> > Loop
> >
> > objRS.Close
> > Set objRS = Nothing
> >
> >
> > objConn.Close
> > Set objConn = Nothing
> >
> >%>
> >
> >----- Original Message -----
> >From: "Imar Spaanjaars" <Imar@S...>
> >To: "ASP Web HowTo" <asp_web_howto@p...>
> >Sent: Tuesday, October 08, 2002 9:04 AM
> >Subject: [asp_web_howto] Re: help with sloooowww loop code
> >
> >
> > > Hi Ryan,
> > >
> > > You could take a look at data shaping. This allows you to return a
> > > "recordset" as a column in another recordset. Something like:
> > >
> > >
> > > Name Address [AllSalesRecords]
> > >
> > > in one recordset.
> > >
> > > You can loop through the outer recordset, and display the Name and
> > Address.
> > > Once you reach the inner recordset, you can loop through those for the
> > > current record in the outer recordset.
> > >
> > >
> > > For more info, see:
> > >
> > > http://www.asp101.com/articles/chris/datashaping/default.asp
> > >
> > >
> > > Imar
> > >
> > >
> > >
> > > At 09:00 AM 10/8/2002 +0200, you wrote:
> > > >Hi Ken
> > > >
> > > >thanks, but I'm a bit confused as to how that will work
> > > >
> > > >I have a message that is sent to a number of people....I need to see
the
> > > >message details as well as all the people it was sent to, how do I
collect
> > > >all the people it was sent to without using a loop statement?
> > > >
> > > >thanks
> > > >ryan
> > >
> > >
> > >
> > >
> > > ---
> > >
> > > Improve your web design skills with these new books from Glasshaus.
> > >
> > > Usable Web Menus
> > >
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> > > r-20
> > > Constructing Accessible Web Sites
> > >
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> > > r-20
> > > Practical JavaScript for the Usable Web
> > >
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> > > r-20
> >
> >
> >
> >---
> >
> >Improve your web design skills with these new books from Glasshaus.
> >
> >Usable Web Menus
> >http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> >r-20
> >Constructing Accessible Web Sites
> >http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> >r-20
> >Practical JavaScript for the Usable Web
> >http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> >r-20
>
>
>
>
> ---
>
> Improve your web design skills with these new books from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
> r-20
> Constructing Accessible Web Sites
> http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
> r-20
> Practical JavaScript for the Usable Web
> http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
> r-20
Message #9 by "Imar Spaanjaars" <Imar@S...> on Wed, 9 Oct 2002 12:40:12 +0200 (CEST)
|
|
Hi Ryan,
I guess it depends. I don't really know whether data shaping is much
slower than ordinary queries.
However, JOINS carry some overhead in that the data from the outer table
is duplicated for each record in the inner table.
A small example with, say, an orders table:
JOINS:
CustomerNumber OrderNumber OrderItem
ABCD 123 12345
ABCD 123 12346
ABCD 123 12347
ABCD 123 12348
BCDE 124 12348
BCDE 124 12349
BCDE 124 12350
As you can see, the CustomerNumber and OrderNumber are passed for each
record in the OrderItem column.
With DataShaping, this would look like:
CustomerNumber OrderNumber OrderItem
ABCD 123 (12345
12346
12347
12348)
BCDE 124 (12348
12349
12350)
Where the stuff between ( and ) is a "chapter" or a recordset.
This creates less overhead in data transportation, but like I said, I
don't know about the performance overhead for datashaping. I wouldn't be
surprised if in the background, datashaping would STILL pass the extra
data.I think you'll need to perform some tests to find out.....
Cheers,
Imar
> Thank you IMAR.....sorry about that it was late last night :)
>
> I have sorted out data shaping and the performance is much better.
>
> I have one question though Ken Schaefer was suggesting using joins to
> do this, which one is better performance wise Joins or Data shaping?
>
>
>
> ----- Original Message -----
> From: "Imar Spaanjaars" <Imar@S...>
> To: "ASP Web HowTo" <asp_web_howto@p...>
> Sent: Wednesday, October 09, 2002 7:51 AM
> Subject: [asp_web_howto] Re: help with sloooowww loop code
>
>
|
|
|
View Cart
