Problems Passing Variables from URL to SQL statement.

[Deekman]

Hi guys I've got a bit of a problem.
I'm trying to code a website in asp and pass a couple of variables through the url so that whn the next page loads it'll only show those specific records.
Problem is though I keep recieving this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/Deekman/stories_full.asp, line 50 --

This is the query string i'm using to try and access the database:

querystring = "SELECT stories.ID, stories.PosterID, stories.StoryNumber, stories.Chapter, stories.Body, story_list.StoryName FROM story_list INNER JOIN stories ON story_list.IdNumber = stories.StoryNumber WHERE StoryNumber ='" & request("IdNumber") & "' AND Chapter =" & request("ChapterNumber")

It seems to all work fine until I try and make it pass multiple variables, that's when it falls to pieces.

The URL looks like this when and it's definatly passing the variable as I have tried getting rid of one from the query statement and it works fine.
/stories_full.asp?IdNumber=1&ChapterNumber=1

I'm completely stumped, I do not have much experience with ASP but from other places I have looked and researched my SQL statement seems correct, can anyone help me out?

[Brandoe85]

Hi :)

Is the StoryNumber field in your table set as text? Response.Write your querystring variable and make sure everything looks right. Post the output from the response.write(querystring) as well.

Good luck;

[Deekman]

Nope the field is in the database as a number. As I said when I change the query string to something like
WHERE Chapter =" & request("ChapterNumber")
or
WHERE StoryNumber=" & request("IdNumber")

It works fine, the problem is when I go to do both at once.
That's when it stops working. It makes me think something is wrong with the statement itself but if there is I can't see it.

Ok I did a response.write (been so long since I coded in asp last I forgot to even try that!)
SELECT stories.ID, stories.PosterID, stories.StoryNumber, stories.Chapter, stories.Body, story_list.StoryName FROM story_list INNER JOIN stories ON story_list.IdNumber = stories.StoryNumber WHERE StoryNumber ='1' AND Chapter =1

Turns out it's not passing the chapter variable properly. Anyone know how I can edit the query to fix this? (I know it's something simple but It's just not coming to me)

[Brandoe85]

If it's not text this:
StoryNumber ='1'
Shouldn't have single quotes around the 1, as the field is not set up as text.

You should be providing which table you're trying to grab the value from, the stories table, or the story_list table. Try removing the single quotes and specifying the table name in the query (stories.StoryNumber) and see how it goes.

Good luck;

[Deekman]

AHA!
That got it! The single qoutes were the problem I just had to change the statement (note the single qoutes in red were deleted):

WHERE StoryNumber ='" & request("IdNumber") & "' AND Chapter =" & request("ChapterNumber")

Thanks alot for your help it's GREATLY apreciated!

[Brandoe85]

Great! You're welcome :)

[Deekman]

Ok so I don't clog up the board with my threads about stupid questions.
How would I go about creating a URL with response.Write that has a set of variables in it such as this:
response.Write("<a href='stories_full.asp?IdNumber=currentIdNumber&ChapterNumber=gotoprevious'>Back</a>")
Where the parts in red are variables to be passed to the URL (which are obviously incorrect).

The variables are as follows:
gotonext = request("ChapterNumber") + 1
gotoprevious = request("ChapterNumber") - 1
currentIdNumber = request("IdNumber")

Once again I'm sure this is something simple but I just cannot remember and there's no real help online

[Brandoe85]

If I understand the question correctly, you can just concatenate the variables onto the string:

Response.Write("<a href='stories_full.asp?IdNumber=" & currentIdNumber & "&ChapterNumber=" & gotoprevious & "'>Back</a>")


Good luck;

[Deekman]

Yep that got it.
You're my hero.

Thanks again for the help.