Easy ASP with Access DB Question!

[holty]

Hi Guys,

Haven't been coding for a while coz of my roadtrip, I have a very simple problem that i require some guidance on.

I'm creating a web app with a Access DB (first one i have done - used to do mainly SQL Backends with stored procs).

Right the problem - basically I have a page with a collection of records each with an ID. When you click on a record for more information it passes the ID to another page. Okay, how do i pull out that specific record? In my SQL statement i do my selects and where clauses - this is where i say where AdvertID = Request("AdvertID") , which doesn't work!

Any help would be great!

[whammy]

Hey holty - the same holty from wsabstract.com ? :D

That should be

"SELECT * FROM tablename WHERE AdvertID = " & Request("AdvertID")

Assuming AdvertID is numeric in your database.

If you were using other text variables as well, it might look like:

"SELECT * FROM tablename WHERE AdvertID = " & Request("AdvertID") & " AND someothervar = '" & someothervar & "'"

Notice the single quotes around text datatypes. With dates (in Access) you'd use # instead of the single quotes... i.e.:

WHERE myDate = #" & myDate & "#"

or

WHERE myDate = #1/1/2003#

:)

[Mhtml]

What the?:confused: I think I'm missing something here, request("AdvertId") will that work?

I thought that since it would be coming from a link you would use request.querystring("AdvertId") which would be

"SELECT * FROM Table_Name WHERE AdvertId="&request.querystring("AdvertId")

[whammy]

Actually, I think by default if you don't specify .form or .querystring ASP just looks at Request.ServerVariables("HTTP_REQUEST_METHOD") and gets it that way.

I'm in the habit of coding a bit more strictly (mainly for other developers that may be working in the app, so they know it was posted by form or querystring), but there's no reason that won't work. :)

[Mhtml]

Uh huh...Makes sense.
Thanks Whammy! :)

[whammy]

P.S. I could be wrong, I remember reading something about that in my beginner's book a year ago, but it's not handy, since it's been at work on my bookshelf gathering dust for ages.

It might look at both . form and . querystring(?). But I'm not sure, I'd have to test it out.

That's usually my solution anyway - to make sure there are no glitches or bugs, I like to throw all kinds of bad information at my apps while pretending I live in a trailer park and my crack-smoking wife is nagging me for money for her next fix (in other words I try to look at this stuff from a user's point of view, lol). :D

[dominicall]

Have to agree with whammy on this one...

Request("AdvertID") will work since it will check querystring first, then form, but best way to do it is to be specific whenever using the request object, i.e.

Request.QueryString("AdvertID")

It's very helpful when other ppl are reviewing the code and helps when debugging too.

Dominic :D

[whammy]

Thanks for clarifying that. :) I thought it might have been "querystring, form" but I wasn't sure.

FYI, you can check the method, using:

Request.ServerVariables("HTTP_REQUEST_METHOD")

and then use .form or .querystring that way, too. :)

[holty]

Whammy, yep its me!

Thanks for your quick response - can see where i went wrong! Need to head my head in gear!

Yeah you can use just Request and it will go through .QueryString .Form etc. I would have used Request.QueryString but i'm lazy and can't be bothered to type the full lot.

Thanks again!

[Roy Sinclair]

Most of the forms I write are done using POST but during development I often test using the QUERYSTRING method so I'll initially code as REQUEST("fieldname") and only when I'm sure the page is working change them all to REQUEST.FORM("fieldname") so that users don't gain the ability to easily access those pages without going through the proper forms.

Basically what I'm saying is that there's a good case for doing it the shorthand way and a good case for doing it the long form, and both cases can apply to the same page during different stages of it's life cycle.

[whammy]

Heh... where I work pages don't have a life cycle (unless I can force one, by insisting I have the details I really need to do the application right the first time)... they are either complete or not :(

LOL

[holty]

Got a little syntax snag...

Heres my SQL statement -

strSQL = "SELECT tblAdvert.AdvertID, tblAdvert.Description, tblAdvert.Year, tblAdvert.Price, tblVWModel.VWModelDescription, tblVWModelType.VWModelTypeDescription FROM tblAdvert, tblVWModel, tblVWModelType WHERE tblVWModel.VWModelDescription = " & Request.form("cboModel") & " AND tblVWModelType.VWModelTypeDescription = " & Request.form("cboModelType") & ";"

Heres my error -

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblVWModel.VWModelDescription = Golf AND tblVWModelType.VWModelTypeDescription = MK2 GTI'.

Can anyone see where i'm going wrong?!

[dominicall]

Hi holty

Try this...

strSQL = "SELECT tblAdvert.AdvertID, tblAdvert.Description, tblAdvert.Year, tblAdvert.Price, tblVWModel.VWModelDescription, tblVWModelType.VWModelTypeDescription FROM tblAdvert, tblVWModel, tblVWModelType WHERE tblVWModel.VWModelDescription = '" & Request.form("cboModel") & "' AND tblVWModelType.VWModelTypeDescription = '" & Request.form("cboModelType") & "';"

If you're doing a SELECT statement and looking for text you need to surround the text you're searching for with single inverted commas - if you look closely you'll see I've coloured them red above.

So your query would then look like...

tblVWModel.VWModelDescription = 'Golf' AND tblVWModelType.VWModelTypeDescription = 'MK2 GTI'

Also, if you're selecting from different tables as you are then you need to do a join between the related tables. Not knowing the relative UIDs for each table I substituted where appropriate, so your code should look like...


strSQL = "SELECT tblAdvert.AdvertID, tblAdvert.Description, tblAdvert.Year, " &_
"tblAdvert.Price, tblVWModel.VWModelDescription, tblVWModelType.VWModelTypeDescription " &_
"FROM tblVWModelTYpe " &_
"RIGHT OUTER JOIN tblVWModel ON tblVWModelType.VWModelID = tbVWModel.VWModelID " &_
"RIGHT OUTER JOIN tbltblAdvert ON tblAdvert.ModelID = tbVWModel.ModelID " &_
"WHERE tblVWModel.VWModelDescription = '" & Request.form("cboModel") & "' AND tblVWModelType.VWModelTypeDescription = '" & Request.form("cboModelType") & "';"


That should select all records from tblAdvert where the model matches your selection criteria.

If it's not quite right it's probably the joins - I cheat when I build my joing queries by using the query builder in SQL Server and then testing them - LOL.

Let me know how you get on.

Dominic :D

[whammy]

Actually, with a JOIN like that you might want to do it in Access yourself, in Design View.

If you google it there are probably some Access tutorials on the 'net that will show you how to:

"Access"+"create a query in design view"

http://webfaculty.aub.edu.lb/~webwork/Tutorial/jmu/access/queries/createquery.shtml

http://www.tutorialbox.com/tutors/off2000/access/queries.html

http://www.jmu.edu/computing/tutorials/microsoft/access/queries/createquery.shtml

http://www.nova.edu/techtrain/apptrain/access/iaccess2.html

It's a lot easier than writing it yourself, and then once you know it works you can use aliases as well.

[dominicall]

LOL - sorry, it's been so long since I've used Access I couldn't remember whether you could do the joins visually.

Do everything is SQL Server now.

Whammy's right - I do exactly the same with SQL Server - deisgn the join syntax visually as you can test it as you go along and then when it's right cut and paste it to your page.

Makes life a lot easier.

:D

Dominic

[holty]

Cheers dominicall and whammy, I will give them a try!

Will let you know how I get on! LOL :)

:thumbsup:

[holty]

whammy, just looked at them links. I know how to create a query in Access as I have done this loads of times before. What I would like to know is how to call these queries in ASP. Is it similiar to calling stored procedures in a SQL Server database?

[whammy]

Nah, just switch from design view to SQL view (once you have a query doing what you want), and you can copy the code for the query into your ASP script. :)

Actually there might be a way to call a saved query in Access, but not sure how to do it if it's possible, since I mainly use SQL Server myself.

[holty]

Thats ace, didn't realise there was a SQL view, where have I been all this time? Gotta admit, never been a big fan of Access!!

Cheers again!:)

[dominicall]

Yeah - know that feeling holty...

I switched to SQL Server about 12 mths ago and haven't looked back since... you should consider it... much more powerful and MUCH more scalable.

Dominic :D

[glenngv]

just a note on directly accessing via Request(variable)...

All variables can be accessed directly by calling Request(variable) without the collection name. In this case, the Web server searches the collections in the following order: QueryString, Form, Cookies, ClientCertificate, then ServerVariables.

So it is always good practice to use the proper collection name for faster execution and accurate results. :D

[whammy]

QueryString, Form, Cookies, ClientCertificate, then ServerVariables

Cool, thanks for sharing that, it might come in handy sometime. I can see where by NOT specifying which collection you're requesting from, you'd take a serious performance hit.

Good thing I always specify it like I learned in the first ASP book I got (Beginning ASP 3.0). I don't think they ever explained why, though. Either that or it was forgotten not long after I read it at the time. ;)