Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 17th, 2005, 05:31 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting Unique ID from Database

Hi there. I have been struggling now for 3 days trying to figure a code.

Sorry, but I am a bit new to ASP, so please bear with me

Basically, Im building an ecommerce site and I've only got as far as everything working on the index page!

Based on a DVD store, from the index page, folk can click on a Genre of their choice which should lead them to all the records in that Genre. The links are based in a navi.asp include file, which all the file consists of is: -

Code:
<a href="overview.asp?CartID=<%=CartID%>&genre=Family"><img  border="0" src="images/family.jpg" alt=""></a><br>

<a href="overview.asp?CartID=<%=CartID%>&genre=Adventure"><img  border="0" src="images/adventure.jpg" alt=""></a><br>

<a href="overview.asp?CartID=<%=CartID%>&genre=Horror"><img  border="0" src="images/horror.jpg" alt=""></a><br>

<a href="overview.asp?CartID=<%=CartID%>&genre=Comedy"><img  border="0" src="images/comedy.jpg" alt=""></a><br>

<a href="overview.asp?CartID=<%=CartID%>&genre=Sci-Fi"><img  border="0" src="images/scifi.jpg" alt=""></a><br>

<a href="overview.asp?CartID=<%=CartID%>&genre=Children"><img  border="0" src="images/children.jpg" alt=""></a><br>
So, hopefully, when someone clicks on the link, it should take them to the overview.asp page with the correct records. (this is the first thing I am struggling with!)

The Overview.asp page consists of :
Code:
<%

Dim myRS, Image, TitleID, Summary, Genre, mySQL, intID
set myRS = server.createObject("ADODB.Recordset")
myRS.Open "SELECT * FROM AllMovies WHERE ID ='Genre'", myConn

Image = myRS ("image")
Summary = myRS ("Summary")

Response.write "<b>Movie Selection</b><br><br>"
while not myRS.EOF
%>

<table width="500" border="0">

<%
Response.write    myRS("Movie") &  myRS ("Summary")& "</td>" 
%>

<a href="products.asp?CartID=<%=CartID%>&ID=<%="Genre"%>"><img src="/<%=image%>"></a>
<%

myRS.MoveNext
Wend

myRS.Close
set myRS = Nothing
myConn.Close
set myConn = Nothing
%>
It probably all looks a right mess to you, but this is my first attempt! Im getting error after error just from the SELECT * FROM section (Then it will probably show more once thats fixed) but I was hoping someone might take pity on me and show me where I'm going wrong if you can figure it out from the info I've shown you.

The includes e.t.c. are built into the overview.asp page btw.

Any help much appreciated, sorry it was long winded.

Nicky




 
Old January 17th, 2005, 06:21 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Where does "Genre" come from? It looks like you're trying to use a variable, but instead use a hard code value:

SELECT * FROM AllMovies WHERE ID ='Genre'

This queries the database for records where the ID matches the word Genre, not very likely for an ID (which is usually a number, although this doesn't necessarily have to be so)

The same happens when you write out the genre in ASP:

<%="Genre"%>

This outputs the word Genre, not the value of some variable.

What you're probably trying to do is get the Genre from the QueryString, right? If so, try this:

Dim theGenre
theGenre = Request.QueryString("Genre")
theGenre = Replace(theGenre, "'", "'')

The last line is necessary to work with single quotes in the genre, and to prevent Sql injections (a way to hack your database by sending incorrect values to your database)

Next, fix the Sql statement:

SELECT * FROM AllMovies WHERE ID = '" & theGenre & "'"

This adds the value of the variable theGenre to your Sql statement.

Are you sure ID is text so you query it for words like Adventure and Horror? Shouldn't that be:

SELECT * FROM AllMovies WHERE Genre = '" & theGenre & "'"

While you're still learning, it might be a good idea to start good: Select * is bad for a few reasons. Try to limit the query to the stuff you really need:

SELECT ID, Name, Director FROM AllMovies WHERE Genre = '" & theGenre & "'"

I am just making up columns, but I am sure you get my point.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old January 17th, 2005, 06:37 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply Imar.

In the meantime of posting this question, I put

Code:
Dim strGenre
strGenre = Request("Genre")
Dim myRS, Image, TitleID, Summary, Genre
set myRS = server.createObject("ADODB.Recordset")
myRS.Open "SELECT * FROM AllMovies WHERE Genre ='" & strGenre & "'", myConn
Which actually worked! (after 3 days of trying!) So, I'm hoping that is ok??? Once I get a bit more confident with the code, I'll try different variations, but as you can imagine, I darent touch it at the mo incase it takes me another 3 days to get it working again.

The problem I am having now, is linking the from individual images which in turn will be clicked on to the get individual spec, i.e. Click on the image troy.jpg, then all requested information for the film Troy will show up, such as - Summary, Title, Price, e.t.c. I'm not sure how to write the code for such a request?

Another thing (you'll get fed up with me before getting to know me ) The summary can be quite a few lines, so on the overview.asp page, I want to limit how much text is shown from the Summary Field but so that it is all shown in the product.asp page?

Thanks for you help.

sorry - Genre is a Field from the ALLMovies table.
 
Old January 17th, 2005, 08:19 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right - managed to sort out how to get the info for the next page. For some reason tho, it doesnt display the first record in the "Summary" field? Plus still trying to work out how to minimise the data that is given in the overview page? Too late to look into any more tonight.

 
Old January 24th, 2005, 03:23 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I somehow missed your replies to this thread.

Are you still having problems with this?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: New Skin by Incubus (Track 3 from the album: S.C.I.E.N.C.E.) What's This?
 
Old January 24th, 2005, 08:17 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar. Problems seem to snowball.
I managed to get the code to include the first record, however, still no joy reducing the data from the memo field. I did attemp a suggestion made, but it didnt work unfortunately.

I have a new problem which I have tried to sort out. (Sorry, newbie so full of questions), I have a shopping cart, so have a CartID to follow people through the site, this was working just fine, but then decided to stop working, I now get an error stating

Code:
Error Type:
Provider (0x8002000A)
Out of present range.
/moviebox/dbconx.asp, line 19
The Code is:

Code:
<%
Dim myConn
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open "MovieBox"
Dim CartID
CartID = Request.QueryString("CartID")
If CartID = "" Then
 Dim randomnum, datenum
 randomize
 randomnum = int((999999-100000+1) * rnd + 100000)
 datenum = replace(date,"/","")
 CartID = randomnum & datenum
 
 Dim myRScart, CustID
 Set myRScart = Server.CreateObject("ADODB.Recordset")
 myRScart.Open "SELECT * FROM Carts", myConn, 1, 3
 myRScart.AddNew
 myRScart("CartID") = CartID ***LINE 19***
 myRScart("CustID") = CustID
 myRScart("CreatedD") = Date
 myRScart("CreatedT") = Time
 myRScart.Update
 myRScart.Close

End If
%>
I cant figure out what the problem is as I didnt change anything. Once I got the problem, I messed around that much, I ended up deleting the Cart table and building it again. As I said, it was working fine and all the records were being added to the database to show what was happening in the site. If I set the If CartID = "" Then to Null I can access the site, but obviously there is not random cartID so therefore no record of the visit.

Sorry its a long post, but any advice is much appreciated.

Thanks
Nicky
 
Old January 25th, 2005, 06:43 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

A few things:

1. What is the datatype for the CartId in the database? Is it large enough to store a number like 25631820040126.

2. Why are you using a random number? Can't you insert the cart in the database and then use the unique ID of the record as the card ID? Much easier, and guaranteed to be unique.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old January 26th, 2005, 08:10 AM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The datatype is General number (I think thats what your asking) and the reason for using a random number is because I'm following a module which suggests that way of using it. I have just completed Wrox ASP Beginners also.

Unfortunately, being a learner, I'm not very confident with the coding and still trying to grasp why certain things work in particular ways. There seems to be quite a few varietys of how you can write the codes to accomplish the same results, and I get the feeling my code may be looking quite scruffy whilst trying to figure things out.

It just seems safer to follow the module at the moment, I have no idea how to write a code so the cart will use the unique ID of a field rather than a random number. Its confusing learning all this on your own.

 
Old January 26th, 2005, 04:26 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

In that case, I think 25631820040126 is too long to fit in the Number type if you're using an Access database. I did a quick test with a Number column, and I wasn't able to paste 25631820040126 as the column value.

However, with you random number generator, that's what you get: 256318 is the random number, and 20040126 is the current date.

Probably the best way to do this is to use the AutoNumber ID from the Cart table. For some ideas, check out this article: http://www.adopenstatic.com/experime...autonumber.asp

If that seems to difficult for you, you could change the type of the CartId from a Number to Text. Since I don't know the entire application I don't know what consequences this will have, but at least it will fix the "Out of present range" error.

Cheers,

Imar

P.S. Don't give up yet. I know it can be frustrating when things keep failing. However, the reward you get when it works is infinitely larger.... ;)

(Updated my post by adding the link to the article)
 
Old January 26th, 2005, 04:45 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Incredible! I've been banging my head against a brick wall trying to figure this problem out! Changing the CartID to text worked a treat! I'm far from over with different errors coming up for different pages, but can see some light at the end of the tunnel! Thanks for your encouragement. ;)






Similar Threads
Thread Thread Starter Forum Replies Last Post
generate unique id capri SQL Server 2000 1 April 6th, 2008 10:18 AM
Create Unique Id langer123 Classic ASP Basics 0 April 6th, 2005 01:27 PM
create a unique id in asp code langer123 Classic ASP Basics 1 March 15th, 2005 03:55 AM
Creating a Unique ID for each row maxworlund SQL Server DTS 1 March 8th, 2005 09:33 PM
Generate unique ID when populating a table sroman SQL Server 2000 2 August 5th, 2003 03:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.