Hi

I have a simple system for my site which will always choose the correct delivery cost for the products added to a cart - well mostly.

The system works by when user enters the site they are given a unique reference which stays with them through the site - and when they add an item to the cart - it also enters that products delivery charge and their unique id. This works fine, but the problem comes in when they enter 3 products, it still needs to use the highest delivery charge out of the 3.

How can i get the highest value record out or my recordset which matches the current user ID?

Hope this makes sense.

Access DB & ASP VBScript

Any help greatly appreciated.

Simon

Hi Simon

Select Top 1 column _name
From table_name
Where blah
Order By column_name DESC

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Many thanks for reply - will give it a go

regards

simon

Hi Simon

Not sure if the MAX exists in Access, if it does you can also do:

SELECT MAX(DISTINCT column_name)
FROM table_name


Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Digga,

thanks for your help.

I now seem to have the following problem.

When, for the first time, an item gets added to the cart, it says that no record exists, but when you do it the second time it picks up the first record - if get what i mean.

I tried doing the following to rectify this:

&lt;% dim objDel
If (Rs_postageTrack.Fields.Item("Postage_charge".Value) &lt;&gt; "" then
objDel = (Rs_postageTrack.Fields.Item("Postage_charge".Value)
Else
objDel = request.form("delivery"
End If
%&gt;

But still saying the that it requires a current record on the first time it is run.

How can i get it to use the first form value that is sent then check the database afterwards!

Hope this makes sense

regards

Hi Simon

Are you saying that it's always one record behind what you expect? 1 delivery submitted, none exist in db, 2 deliveries submitted, 1 exist db etc or this this only for the first delivery submission, so after the second you have 2 in the db?

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Yes - It always seems 1 behind - so when first one submitted it returns nothing, on second submission it returns the first record, on 3rd submission it returns 2nd and so on.

Any ideas?

Hi Simon

Can you post your code?

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee,

Will post the code later this afternoon- not at that machine at the moment.

cheers

Simon

DWMX 2004 | ASP | VBScript

Hi Lee.

Code below:

'request informatio from previous page
&lt;%

if(session("UserTrack" &lt;&gt; "" then Command1__varSession = session("UserTrack"

if(request("delivery" &lt;&gt; "" then Command1__varPostage = request("delivery"

%&gt;

'add information to database
&lt;%

set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_chat_track_STRING
Command1.CommandText = "INSERT INTO Tbl_postcharge (ID_UserSessionID, Postage_charge) VALUES ('" + Replace(Command1__varSession, "'", "''" + "', '" + Replace(Command1__varPostage, "'", "''" + "') "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()

%&gt;

'get session
&lt;%
Dim Rs_postageTrack__varuser
Rs_postageTrack__varuser = "%"
If (session("UserTrack" &lt;&gt; "" Then
Rs_postageTrack__varuser = session("UserTrack"
End If
%&gt;

'select record from database
&lt;%
Dim Rs_postageTrack
Dim Rs_postageTrack_numRows

Set Rs_postageTrack = Server.CreateObject("ADODB.Recordset"
Rs_postageTrack.ActiveConnection = MM_chat_track_STRING
Rs_postageTrack.Source = "SELECT MAX(Postage_charge) AS 'Postage_charge' FROM Tbl_postcharge WHERE ID_UserSessionID = '" + Replace(Rs_postageTrack__varuser, "'", "''" + "'"
Rs_postageTrack.CursorType = 0
Rs_postageTrack.CursorLocation = 2
Rs_postageTrack.LockType = 1
Rs_postageTrack.Open()

Rs_postageTrack_numRows = 0
%&gt;

'put record into variable
&lt;% dim objstr, objDel
objstr = (Rs_postageTrack.Fields.Item("'Postage_charge'".Value)
objDel = formatcurrency(objstr)
%&gt;

hope this helps

Simon

DWMX 2004 | ASP | VBScript

Hey Simon

I've been bit busy, just letting you know I've not fogotten this, I'll have a look now and post back later.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

cheers mate

regards

Simon

DWMX 2004 | ASP | VBScript

Hi Simon

Ok first thing, do a response.write to screen to make sure these have values:

&lt;%
if(session("UserTrack" &lt;&gt; "" then Command1__varSession = session("UserTrack"
if(request("delivery" &lt;&gt; "" then Command1__varPostage = request("delivery"
%&gt;

You don't need the single quotes here:
(Rs_postageTrack.Fields.Item("'Postage_charge'".Value)

And in the SELECT MAX statement you could get away without them to.

Post back

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee,

Have made sure and the data is being passed fine on the form and session element. Its just anoying that it doesnt seem to see the first record on the first load of the page. I have checked the order of the actions in the code and the data is being added before the query is being run on the database. I just dont get it!

Its doing my head right in!!



Simon

DWMX 2004 | ASP | VBScript

Hi Lee,

Seem to have found a solution - I am now getting it to update the database from the previous page - so when they click to add to cart it is then processing it and adding to the database, then moving to the next page and calling the values from the DB.

just wanted to say thanks for taking the time to help with this.

Regards

Simon

DWMX 2004 | ASP | VBScript

This seems to point to an issue that i have experienced also...

In that updates submitted to the database have not been completed before the resulting record(s) is then called..

Its almost like there is an overlap in the time these things take to process....

I have found that altering the properties of the recordset can affect the outcome (cursor location etc)..

Try a client side cursor...or as you've said above...do the updating in a seperate script..

This, im sure, can also be affected by the power of the machine running the scripts/..

Id be interested in hearing anyone who had definative answers or stats on this type of issue.

Cheers

:: Son, im Thirty.... ::