ASP with MySQL

bpierce · November 17th, 2004, 04:39 PM

I'm having a problem passing a variable from one page to another. This code worked when it was on a NT server with Access.

Page 1:

Response.Write "<a href=""pics.asp?cid=" & rsCat("categoryID") & """>" & rsCat("Category") & "</a>"

This from a page that is just a list with links (it works fine with mySQL)

Page 2: - the problem
<%
strQuery = "SELECT * FROM pics WHERE categoryID = ' " & Request.QueryString("cid") & " ' "
Set rstemp = adoDataConn.Execute(strQuery)
%>

<head>
<title><%=rstemp("category")%> Pictures</title>
</head>

<body <% if request.querystring("cid")= "1" then %> bgcolor="#A39A36"
      <% elseif request.querystring("cid")= "2" then %> bgcolor="#749FD4"
      <% elseif request.querystring("cid")= "3" then %> bgcolor="#7CBD75"
      <% elseif request.querystring("cid")= "4" then %> bgcolor="#D4AE61"
      <% elseif request.querystring("cid")= "5" then %> bgcolor="#D4AE61"
        <%end if%>

On page 2 it just comes up completely blank.

Thanks in advance for any help.

Brad

mat41 · November 17th, 2004, 06:49 PM

You say:
;;;I'm having a problem passing a variable from one page to another, does ?cid have a value in the URL when page two loads?

Have you written your query to the page (strQuery) and checked categoryID is equal to some value?

If yes is the answer to both the above questions, your problem is not passing a variable from one page to another. If one of the answers was no you would be getting an error here:

<title><%=rstemp("category")%> Pictures</title>

Is the category showing as it should in the title of your page?

NOTE; Your query:

strQuery = "SELECT * FROM pics WHERE categoryID = ' " & Request.QueryString("cid") & " ' "

Is the categoryID field a string data type, id's are almost always integers (for very good reason)

;;;On page 2 it just comes up completely blank
From the look of your code it should be however if the request.querystring("cid") value is equal to a STRING VALUE of "1" "2" "3" "4" "5" there should be a background color to the page

Providing there is a value to the cid=?? querystring try removing the sets of double quotes around the numbers 1-5

Wind is your friend
Matt

bpierce · November 18th, 2004, 12:19 AM

Matt - first off thanks for the help.

categoryID is equal to a value and it is showing up in the URL of the second page.

The categoryID field is set up as INT. I tried removing the double quotes with no luck.

Is there anything I need to change to the database itself? I just exported the two tables from Access to the mySQL server. I did check when the blank page comes up I view the source and got this:

<html>

<head>
<title>
<p> error '800a0cc1'
<p>
ADO Could not find the object in the collection corresponding to the name or ordinal reference requested by the collection.
<p>
/scrapbook/pics.asp, line 17

Here is the URL to the page so that you can see what I'm trying to explain. On the default page I've put a link at the top with a zip file that contains the code for both pages as they are now.

www.fourandfive.com/scrapbook/default.asp

Thanks again
Brad

mat41 · November 18th, 2004, 12:39 AM

No worries. if the categoryID field is an integer you need to change:

strQuery = "SELECT * FROM pics WHERE categoryID = ' " & Request.QueryString("cid") & " ' "

To (single quotes removed)

strQuery = "SELECT * FROM pics WHERE categoryID = " & Request.QueryString("cid") & ";"

;;;Is there anything I need to change to the database itself?

No, check all your primary keys are still primary keys. For some wierd reason these can get left behind, i'm still no sure why myself. A good thing for you to look into now you are using SQLServer is 'setting up relationships'

Wind is your friend
Matt

rodmcleay · November 18th, 2004, 12:45 AM

Brad/Matt,

I dont think it is the double quotes, VBScript the querystring does pass the values as a string and "1" is ok.
Even if not ok the Variant data type would cast this without too much trouble.

I did note that your query had single quotes arround the categoryID where clause.
"SELECT * FROM pics WHERE categoryID = ' " & Request.QueryString("cid") & " ' "

I am not sure if this is the problem but I would first try
"SELECT * FROM pics WHERE categoryID = " & Request.QueryString("cid")
Widhout the single quotes.

Having said that I would have expected a different error for that.

Also check that the field "category" exists in your database and check for case sensitivity also while you are there.

Also, as an aside that may well be connected to the error, you should always check that the recordset has a record in it before using the values that you think are there.

eg

Code:

<title>
<%
if not rstemp.eof and not rstemp.bof then
 response.write(rstemp("category") & " Pictures")
else
 response.write("No records available")
end if
%> 
</title>

In this case I think you will find that there are no record inthe recordset, you next task is to find out why....

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

rodmcleay · November 18th, 2004, 12:47 AM

Sorry to bud in Matt, you beat me to it.
But Brad, do check the recordset EOF.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

mat41 · November 18th, 2004, 12:59 AM

Oh I see you are using mySql not SQLServer, sorry I thought this was mis typed. I write PHP using mySql but have never written ASP using mySql, what platform is the mySql sitting on?

I code PHP/mySql on my windows machine for development using a tool called Sokkit. Apache, mySql and PHP for windows - works great!!

On the same machine I have IIS/SQLServer also. Apache and IIS canât both be using port 80 at the same time however.

Have you established your connection string works?
Have you got 'show friendly HTTP error messages' turned off in the tools/internet options/advanced area? Your page should be rendering some information, an error at least.

IMO: create a new page with just a connection and a query, response.write the query and paste it into mySql directly to ensure it gets records (did your transfer execute properly)

TIP: The best mySql windows GUI SQLYog - wow what a tool, 500 times better than PHPMyAdmin

Wind is your friend
Matt

mat41 · November 18th, 2004, 01:08 AM

rodmcleay
The more the merrier

bpierce
As Rod said checking for EOF should always be done

Wind is your friend
Matt

bpierce · November 18th, 2004, 09:57 AM

Thank you both - but now I must go crawl under a rock. Yes its working now. When checking to make sure the text in the code matched the tables in the db I noticed that in my hurry to cut and paste - I called the wrong table in the first SELECT statement. The page calls on both tables at different places (pics and categories). I had it calling the pics table both places. So, the field category is not in the pics table its in the categories table.

Long story short - its working now and thank you both for your help and allowing me to see my mistake.

Brad

mat41 · November 18th, 2004, 06:50 PM

Ahhhh now:

;;;ADO Could not find the object in the collection corresponding to the name or ordinal reference requested by the collection.

makes sense

Wind is your friend
Matt