Classic ASP Discussion - Help With Code

Print Page | Close Window

Help With Code

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=13253
Printed Date: 29 March 2026 at 4:41am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Help With Code

Posted By: felix.akinyemi
Subject: Help With Code
Date Posted: 07 January 2005 at 12:56pm

Can someone please tell me if there is anything wrong with this code


UserID = Request.Form("ID")   (lets say its equal to 5)
dbfile = Server.MapPath("db.mdb")
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & dbfile

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM tblUsers WHERE User_ID ='" & UserID & "'"
rs.Open sql, OBJdbConnection, 3, 3

If Not rs.EOF Then
 PageTitle = rs.fields("field1")
 PageCode = rs.fields("field2")
 PagePassword = rs.fields("field2")
End If

The problem is that its not getting the values from the database tables...

PageTitle, PageCode and PagePassword are returning nothing

am i doing something wrong...

Replies:

Posted By: dj air
Date Posted: 07 January 2005 at 1:20pm

not sure 100% why its not working

but this dbfile = Server.MapPath("db.mdb")

should be
dbfile = "Server.MapPath("db.mdb") "

also can someone tell me what the 3,3 does, i don't use them i ussually have rs.open strSQL, strCon

what does the ,3,3 do?

also might be best to use SELECT tblUsers.*.

defines the query better.

not usre if its ment to be but you have the PagePassword and PageCode the same value.

NOTE:
its best to havge

if rs.EOF then

Else

END IF

or the other way round, prevents an error , when a record isn't found.

Posted By: meteor
Date Posted: 07 January 2005 at 3:05pm

dbfile = Server.MapPath("db.mdb")

it is Correct

try Diffrent Driver Like Jet Engine

is Your Field Names True ?
Change

If Not rs.EOF Then
PageTitle = rs.fields("field1")
PageCode = rs.fields("field2")
PagePassword = rs.fields("field2")
End If

Do While Not rs.Eof

...

rs.moveNext

Loop

There is No need to use 3,3 Remove Them
For more Information About ADO Constant Open file ADOVBS.INC

At Last Write Your Error Here.. Realty Error- try to remove Check mark Of Friendly Error Message at Your IE (Internet Option-> Advanced)

-------------
Sincerely
--------------------
http://www.TacPlusPlus.com - PowerFull Scripts For NTTacPlus

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 3:22pm

Ok, the error im getting is...


Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The code on the error line is


rs.Open sql, OBJdbConnection

i tried it with the do while loop, same error...

with or with the 3,3 still the same thing... and Friendly Error is of!!!

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 3:26pm

I tried these and both giving the same errors


sql = "SELECT * FROM tblUsers WHERE User_ID ='" & UserID & "'"

sql = "SELECT tblUsers.User_ID FROM tblUsers WHERE tblUsers.User_ID ='" & UserID & "'"

Posted By: dj air
Date Posted: 07 January 2005 at 3:33pm

the error is on that line because that the line the SQL is ececuted.

by any change the UserID isn't a number Tongue

if so you can take the ' ' from the query so use the below



sql = "SELECT tblUsers.User_ID FROM tblUsers WHERE tblUsers.User_ID = " & UserID

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 3:51pm

ok, thanks, that worked... but still another error


ADODB.Recordset error '800a0cc1' 

Item cannot be found in the collection corresponding to the requested name or ordinal.

and the code on the line is


PageTitle = rs.fields("field1")

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 3:54pm

and

PageTitle = rs.fields("field1")

it has text not numbers...

Posted By: dj air
Date Posted: 07 January 2005 at 3:55pm

the full query needs to be






sql = "SELECT tblUsers.* FROM tblUsers WHERE tblUsers.User_ID = " & UserID

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 4:27pm

thanks, that got it workin!!!

Posted By: felix.akinyemi
Date Posted: 07 January 2005 at 5:08pm

dj air wrote:

the full query needs to be



sql = "SELECT tblUsers.* FROM tblUsers WHERE tblUsers.User_ID = " & UserID

i used this query to update but im gettin an error...


ADODB.Recordset error '800a0cb3' 
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

and the code on line is

e.g.


rs.fields("name") = name

do i have to change the query

Posted By: dj air
Date Posted: 07 January 2005 at 7:03pm

before rs.open

i think you need

rs.cursortype = 3

then it will be ok.

Posted By: dj air
Date Posted: 08 January 2005 at 6:54am

sorry, at 1 thismorning, i releaised i posting the wrong code its

rs.locktype = 3

not the above one.

iots one of those cases we where tallking about (realise after the computer is turned off).

Posted By: felix.akinyemi
Date Posted: 08 January 2005 at 8:11am

LoL... i no what u mean...

rs.locktype = 3

that got it workin, thanks... but can u pls explain what it means and what it does!!!

Posted By: dj air
Date Posted: 08 January 2005 at 2:55pm

the record is not locked unless its being updated.

i believe thats what it does. by default its set to not allow it,

Posted By: felix.akinyemi
Date Posted: 08 January 2005 at 3:39pm

one more problem, im tryin so make it so that it checks if the username already exsist in the database im usin this, but its not workin...


 Set rs = Server.CreateObject("ADODB.Recordset")
 sql = "SELECT * FROM tblUsers"
 rs.Open sql, OBJdbConnection
 Do While Not rs.EOF
  If rs.fields("UserID") <> Username Then
   rs.movenext
  Else
   Response.Redirect("errorpage.asp?code=1")
  End If
 Loop

lil help!!

Posted By: snapey
Date Posted: 08 January 2005 at 7:18pm

You are comparing the username "fred" with the UserID (5)

I'm no SQL expert, but there should be a SQL string to do this rather than walking through the recordset.

how about something like;

sql "SELECT UserID FROM tblusers WHERE username= '" & username & "';"

if this returns an empty recordset then you know that the user is unique, if not then you have the ID of the row and you can use this to update or report.

I always stick a line like

response.write (sql & "<br>") underneath the above so that I can see what my SQL statement ended up being. Then comment it out when its all working.

Also, if you response.redirect in the middle of the script you will leave the recordset object open, tying up server resources?

Mark

Posted By: felix.akinyemi
Date Posted: 09 January 2005 at 5:19am

snapey wrote:

You are comparing the username "fred" with the UserID (5)

There are both txt...

thanks... its cool, i got it workin!!

Posted By: felix.akinyemi
Date Posted: 09 January 2005 at 1:07pm

There one more thing... i want to get information from two different columns in the same row.

eg.

i want to get the values from field1 and field2 both text and from the table info

im not sure on how to write the query.

i was trying but its not workin...


"SELECT info.* FROM info WHERE info.field1 = '" & string1 & "' + info.field2 = '" & string2 & "

Posted By: dj air
Date Posted: 09 January 2005 at 1:16pm

"SELECT info.* FROM info WHERE info.field1 = '" & string1 & "' AND info.field2 = '" & string2 & "' "

that should then be ok.