Help with an ASP/SQL script :-)

[Andrew Milner]

Hi folks.

This is my first post here at ERT.  Hope you guys can help me out with this one.


I have written a script that allows me to show alternate products on my e-commerce site by searching my stock database and randomly selecting a product.  This part works fine.  I have now however expanded it so that I can overwride the random part for particular products by specifying alternate product codes for the current product.

It works perfectly providing I specify both alternate products.  But I may wish to only display one, either on the left or right.  If I ommit one from the SQL table then it throws me off with an either EOF or BOF is true which I can't understand as I have added the:

if not ovrchqRS1.eof then
   overide1 = "True"

part to stop this from happening.

Maybe it's something really minor that I'm overlooking.

Any chance you could take a look and let me know what you think.

The script is at:

http://www.andrewmilner.f2s.com/altproducts.txt


Thanks in advance,

Andrew.

[VGR]

hello, I didn't understand where exactly lies your problem , and what you mean exactly "if I provide only one to the left or right" and "it stops with EOF or BOF" : where ? On which query or line ?

[rdivilbiss]

This is my first post here at ERT.  Hope you guys can help me out with this one.

As opposed to some other places, help here may be a little different. Rather than just debugging your code for you, we might suggest using cleaner logic and help you rewrite it into something more efficient and more maintainable.

In looking at your code:

1. you are creating new objects every time you query the database,
2. you have at least one object you do not dispose of when finished, and
3. you perform some actions in VBScript that the database could handle more effiently.

If you get a large number of simultaneous hits on this page (the goal of every shopping cart) your server is going to consume memory which will not be released quick enough to keep your application from crashing. Even if you do not get very many simultaneous hits, as the code is written, it is going to be unstable and slower than necessary.

Reading your post and the comments in your code, I think I know what you are trying to do but it isn't completely clear. So lets start with the problem definition.

You say you have some "Primary" product that is going to be displayed. On that page you wish to display some alternative choices.

To determine which alternative choices (products) to also show you initially just picked two at random.

Now you wish to refine this further.

If the primary product has one or two additional product codes then you will choose alternative product choices related to those additonal product codes.

Is this correct?

As to the specific question. When you have only one additional product code your query fails with "either rs.bof or rs.eof is true". This is a different logical condition than not rs.eof.

For example.

Code:

Set rs = conn.execute(query)
if rs.bof and rs.eof then
    ' No records were returned
else if rs.eof
    ' I may or may not have records, but
    ' I know my recorset cursor is at the end
end if

I suspect, when we look at your query, it returns no records when you only supply one additional product code. If so, your test for eof is not sufficient. But I suggest we look at a better way of doing all of this, and especially cleaning up the creation and disposal of recordset and connection objects. We can fix the query later, and may not even use that style of query after we clean up the rest of the code.

First, let be know if I understand the problem description, then you can decide how to proceed.

[Andrew Milner]

Thanks for getting back to me on this one.

I have now acheived what I initially set out to do which as you rightly understand is to be able to overwride the random partno display with ones that I can specifiy.

I have played around with the eof and bof and achieved a working result.  The code I have used is below.

Code:

' First we must check to see if we have opted to have a specific partno for the related one in the AltProdOveride table.

ovrchqSQL1 = "SELECT targetpartno1 from AltProdOveride where sourcepartno = '" & thepartno & "'"
Set ovrchqRS1 = Server.CreateObject("ADODB.Recordset")
ovrchqRS1.open ovrchqSQL1, template

if ovrchqRS1.eof then
overide1 = "False"
elseif ovrchqRS1.bof then
ovrchqRS1.movenext
end if

If ovrchqRS1.eof then
overide1 = "False"
elseif trim(ovrchqRS1("targetpartno1")) > 0 then
overide1 = "True"
ovrtargetpartno1 = trim(ovrchqRS1("targetpartno1"))
end if

ovrchqRS1.close
Set ovrchqRS1 = nothing


' Providing a specific partno has been linked to the current then we proceed to get the details of that part no, else we proceed to random.

if overide1 = "True" then

I suspect that the code is even more messy now than before but for the moment it is working which gives me a certain level of satisfaction.

I am however concerned with your comments that the code is so messy that it may cause the entire application to crash.  At the moment we are not getting a great deal of page views as we are a seasonal business which is set to rocket into the summer.


This is really my first ASP script and so would expect it to not be as good as it could be.  I am however keen to learn the best methods to use as they will benefit many other scripts I wish to write for outer functions.

Where do we really go from here? as i understand that you are not simply going to say, yes this is the code you need, use it as this site is more learning orientated which is ultimatly going to be more beneficial.


Perhaps we could work through the script in segments, starting with number 1 on your list

1. you are creating new objects every time you query the database.

What would your thoughts be on a more cleaner and efficient way to perform the operations?

As regards number 2 on the list, it is closed on the calling page of this script and is therefore perhaps not relative.

As regards your understanding of the purpose of the script, you are pretty much right on.


Thanks again for you help.

Andrew.

[rdivilbiss]

I am glad I understood the problem. I am also glad that you have a working solution. I may not have much time to help the next few days, so if the immediate problem is solved, discussing ways to improve your ASP code can be more leeisurly.

The connection and recordset objects are created in server memory. It adds up. These days, your web server will probably have quite a bit of memory, even in a shared host, but it is finite.

A bit of recent history. With IIS 5, you could not separate the memory pools used by various sites like you can in IIS 6. The ISP would take your site down if you leaked memory because you could crash the server.

In classic ASP the two most expensive operations in terms of execution time and memory use are object creation and disposal and string concatenation.

I'll run through your code and see if I can make some suggestions for improvement.

[Andrew Milner]

That's great.  I really appreciate any suggestions you can give.

Andrew.