Pull Record from DB based on variable 1

[werD420]

Hello all
Im having another issue with connecting to a db and pulling only a record matching the one posted

im using the following code to connect

<!--#include file="adovbs.inc"-->
dim Comp,UN,PW
set UN=Request.Form("UN")
set PW=Request.Form("PW")
dim objConn,objRec 
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open Server.MapPath("master.mdb")
Set objRec = Server.CreateObject("ADODB.Recordset")   
 objRec.Open "SELECT * FROM USERS WHERE [UN]=" & UN & "",objConn,1,3 'line 25
i receive the following error

Microsoft JET Database Engineerror '80040e10'

No value given for one or more required parameters. /logme.asp, line 25

ive also tried it as follows with no luck
i must just be missing something I know this eror is normally related to column names but i have my table named appropriately

can anyone be of assistance to me?

<!--#include file="adovbs.inc"-->
dim Comp
UN=Request.Form("UN")
PW=Request.Form("PW")
dim objConn,objRec 
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open Server.MapPath("master.mdb")
Set objRec = Server.CreateObject("ADODB.Recordset")   
 objRec.Open "SELECT * FROM USERS WHERE [UN]=" & UN & "",objConn

Thanks in advance for any help or suggestions

 

[DotNetGnat]

Try this:

objRec.Open "SELECT * FROM USERS WHERE [UN]='"&UN&"' ",objConn,1,3

observe the single quotes...

-DNG

 

[DotNetGnat]

Also:

objRec.Open "SELECT * FROM [USERS] WHERE [UN]='"&UN&"' ",objConn,1,3

-DNG

 

[werD420]

That did it
Thanks alot, quotes make me dizzy some times

I really appreciate the quick response

 

[DotNetGnat]

not a problem, glad that it worked for you.

-DNG

 

[mikeyp3]

Any way to do that with a variable that is a date? I get a data type mismatch in criteria expression...

MP

 

[DotNetGnat]

post your query...it can be done with dates too...

-DNG

 

[Sheco]

mikeyp3,

It depends on which kind of database you are using. Some of them use single quotes to deliniate dates and others use the # sign.

 

[mikeyp3]

I got it working...Thanks for the responses...I swear I had tried that an hour ago...FYI below is some of the code...


Private Sub Txtdate_Exit(Cancel As Integer)
Dim db As New ADODB.Connection

Dim rst As New ADODB.Recordset
Dim counter

Set db = CurrentProject.Connection
Dim formdate As Date

formdate = Forms![Selection Form]![Txtdate]
rst.Open "Select * From [CA Check] where datebooked = #" & formdate & "#", db, adOpenDynamic, adLockOptimistic

counter = 0
rst.MoveFirst

Do Until rst.EOF
counter = counter + 1
rst.MoveNext
Loop

Forms![Selection Form].Text10.Value = counter
End Sub

 

[DotNetGnat]

Yep...that works...#'s are delimiters for dates in MS ACCESS...

glad its working for you...

-DNG