[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select Max Value And Enter Into DB - URGENT 500 POINTS

Posted on 2006-04-25
6
Medium Priority
?
301 Views
Last Modified: 2008-02-01
Can anyone see a problem with this code:

<%
intCartID = Session("CartID")

IF intCartID = 0 THEN
      
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT MAX(intCartID) AS intCartID1 FROM tblCarts"
      RS.Open SQL,Connection,3,3
      
      intLastCartID = intCartID1
      intNewCartID = intLastCartID + 1
            
      RS.AddNew
            RS.Fields("txtCartStatus") = "OPEN"
            RS.Fields("intCartID") = intNewCartID
      RS.Update
      
      RS.Close
      Set RS = Nothing

ELSE
      
      Response.Write "CartID = " & intCartID
      
END IF
%>
0
Comment
Question by:net-workx
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 16537750
what is your problem.  is it not working?  are you getting an error?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 16537773
If this:

     intLastCartID = intCartID1
     intNewCartID = intLastCartID + 1

Is supposed to be reading the value from your SQL query then it should say:

     intLastCartID = RS("intCartID1")
     intNewCartID = intLastCartID + 1
0
 

Author Comment

by:net-workx
ID: 16537884
sorrry! cardinal sin, i forgot the error...

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/includes/cart/processing/add_to_cart.asp, line 17


bascially is should be selecting the max value from a column called intCartID in a table called tblCarts

Once i have established what the max value in that column is i need to put it into a variable, add 1 to it and put that into another variable

This second variable then needs to be entered into the DB and into the column intCartID

the RS.Fields("txtCartStatus") = "OPEN" does not matter at this point, ill sort that out later...

Cheers,
Carl
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:net-workx
ID: 16537972
This code runs and adds a record into the DB but each record has an intCartID of 1

<%
intCartID = Session("CartID")

IF intCartID = 0 THEN
      
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT MAX(intCartID) AS intCartID1 FROM tblCarts"
      RS.Open SQL,Connection,3,3
      
      intLastCartID = intCartID1
      intNewCartID = intLastCartID + 1
      
      RS.Close
      Set RS = Nothing
      
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT * FROM tblCarts WHERE intCartID = 0"
      RS.Open SQL,Connection,3,3
      
      RS.AddNew
            RS.Fields("txtCartStatus") = "OPEN"
            RS.Fields("intCartID") = intNewCartID
      RS.Update
      
      RS.Close
      Set RS = Nothing

ELSE
      
      Response.Write "CartID = " & intCartID
      
END IF
%>

I then changed it to:

<%
intCartID = Session("CartID")

IF intCartID = 0 THEN
      
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT MAX(intCartID) AS intCartID1 FROM tblCarts"
      RS.Open SQL,Connection,3,3
      
      intLastCartID = RS("intCartID1")
      intNewCartID = intLastCartID + 1
      
      RS.Close
      Set RS = Nothing
      
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT * FROM tblCarts WHERE intCartID = 0"
      RS.Open SQL,Connection,3,3
      
      RS.AddNew
            RS.Fields("txtCartStatus") = "OPEN"
            RS.Fields("intCartID") = intNewCartID
      RS.Update
      
      RS.Close
      Set RS = Nothing

ELSE
      
      Response.Write "CartID = " & intCartID
      
END IF
%>

and it works ok so i was needing to create a new recordset for some reason and then i entered the fix that carl_tawn suggested.

Any improvements then please let me know.

Points are awarded to carl_tawn

Cheers,
Carl
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16538037
The reason that you needed to open a second recordset is because the original created from "SELECT MAX(intCartID) AS intCartID1 FROM tblCarts" only contains a single value. It doesn't actually contain the columns "intCartStatus" and "intCartID" that you were trying to write to after calling AddNew.

So, as you have done, you needed to retireve the full record so that the columns would then be available for writing your values to.
0
 

Author Comment

by:net-workx
ID: 16538064
Top Stuff,

Thank you for the explaination and clarification.

Regards,
Carl
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses
Course of the Month20 days, 7 hours left to enroll

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question