Please help me out : DSN/DSN-less

[equuelus]

Here is the problem. I have tried both the DSN method and the DSN-less method. but the thing is the DSN-less method doesn't seem to work properly; could not obtain the records in the database. These are my code. See if you could figure it out.

'DSN' method code :
___________________________________________________
Dim objConnect, objRecord, strQuery, text

Set objConnect = Server.CreateObject( "ADODB.Connection" )
Set objRecord = Server.CreateObject( "ADODB.RecordSet" )
Call objConnect.Open( "DSN=Student" )
strQuery = "SELECT * FROM Student WHERE " & _
"Username = '" & Request( "txtUser" ) & _
"' AND Password = '" & Request( "txtPass" ) & "'"

Call objRecord.Open( strQuery, objConnect, 1, 2 )
__________________________________________________

'DSN-less" Method Code :
______________________________________________
Dim objConnect, sConnString, strQuery, objRecord

Set objConnect = Server.CreateObject("ADODB.Connection")
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("\equuelus\db\Student.mdb") & ";"

objConnect.Open(sConnString)

strQuery = "SELECT * FROM Student WHERE " & _
"Username = '" & Request( "txtUser" ) & _
"' AND Password = '" & Request( "txtPass" ) & "'"

Set objRecord = objConnect.Execute(strQuery)

_________________________________________

The question is, does these two codes obtains the same records set? I get a different results. What is the problem?

I really appreciate any replys, thank you.

[Grummel]

Can´t see anything wrong with the code.

Except this..
What is this code doing.. Request("txtUser") ?
Shouldn´t it be request.querystring("txtUser") or request.form("txtUser") depending on what method you´re using when sending the data from the web form?

[equuelus]

txtUser is from the Username textfield where as txtPass is from the Password txtfield...

[Grummel]

Yeah
But you can´t use Request("txtUser")

This got to be either Request.Querystring("txtUser") or Request.Form("txtUser"). Am I wrong? I´m not sure if you can use Request by itself and it then uses Querystring as default.

What do you get when you write out the SQL line in the webbrowser, like this Response.Write("strQuery") ?

[equuelus]

I got the reply that i want, but still i could not get access to the database.

the strQuery = SELECT * FROM Student Where Username = 'username' AND Password = 'password';

the string seems to be okey, right?

[Grummel]

A correct SQL query would be like this

SELECT * FROM Studen WHERE username="Bill" AND password="Microsoft"

Your SQL query seems okey, except the ; at the end of the query.


What is the actual problem? You can´t connect to the database or you get different output depending on the use of DSN-less and not DSN-less?

Do you get an error? If so, what?

[msdnexpert]

I know it sounds silly but just check out if you are connecting to the same database in both the options.

[equuelus]

The actual problem is I get different output. It seems to work using the DSN but doesn't when using the DSN-less.

I have check the connection to the database, both, DSN and DSN-less refers to the same database.

I think it has something got to do with the server. I use www.brinkster.com. Does this has anything got to do with it?

Maybe you guys should have a look at yourselves. www16.brinkster.com/equuelus/login.asp

According to the database, the username is 'equuelus' where else the password is 'password'.

The output will give you the status of the record, and displays the SQL query.

[equuelus]

Here is the full code for your reference :

<% @Language = "VBScript" %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<%
' filename :login.asp

Dim objConnect, objRecord, strQuery, errorMessage, sConnString

If Request( "cmdButton" ) <> Empty Then
If Request( "txtUser" ) <> Empty And _
Request( "txtPass" ) <> Empty Then


Set objConnect = Server.CreateObject("ADODB.Connection")
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("\equuelus\db\Student.mdb") & ";"

objConnect.Open(sConnString)

strQuery = "SELECT * FROM Student WHERE " & _
"Username = '" & Request( "txtUser" ) & _
"' AND Password = '" & Request( "txtPass" ) & "'"
Set objRecord = objConnect.Execute(strQuery)



' Set objConnect = erver.CreateObject =("ADODB.Connection" )
'Set objRecord = Server.CreateObject( "ADODB.RecordSet" )
'Call objConnect.Open( "DSN=Student" )
'strQuery = "SELECT * FROM Student WHERE " & _
' "Username = '" & Request( "txtUser" ) & _
' "' AND Password = '" & Request( "txtPass" ) & "'"

'Call objRecord.Open( strQuery, objConnect, 1, 2 )

If objRecord.RecordCount > 0 Then
Session( "StudentLoggedIn" ) = _
objRecord( "PuteraID" )



errorMessage = "Record Available"

'Call Response.Redirect( "profile.asp" )
Else
Session( "StudentLoggedIn" ) = Empty
errorMessage = "Record Not Available."
End If
Else
errorMessage = "Invalid Username / Password pair."
End If
End If
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>

<HEAD>
<TITLE>Royal Military College</TITLE>
<LINK REL = "stylesheet" TYPE = "text/css" HREF = "style.css">
<STYLE TYPE="text/css">
<!--
A:link {color:#003399}
A:visited {color:#800080}
A:hover {color:#FF3300}
-->
</STYLE>
<SCRIPT LANGUAGE = "JavaScript">

// Validate the username and password fields
function validate()
{
if ( login.txtUser.value == "" ||
login.txtPass.value == "" ) {
alert( "Some fields empty" );

return false;
}

return true;
}
</SCRIPT>
</HEAD>

<BODY>
<div id="Layer1" style="position:absolute; left:176px; top:55px; width:345px; height:215px; z-index:1; background-color: #FFFFFF; layer-background-color: #FFFFFF; border: 1px none #000000">
<p><strong class="labelTitle">Members Login</strong></p>
<p class = "error"><% =errorMessage %><BR><% =strQuery %></p>
<form name="login" id="login" method="POST" action="login.asp" onSubmit="return validate()">
<table width="56%" border="0">
<tr>
<td width="33%" class="TableLable">Username</td>
<td width="67%">
<input type="text" name="txtUser" class="typing" id="txtUser" size="10">
</td>
</tr>
<tr>
<td width="33%" class="TableLable">Password</td>
<td width="67%">
<p>
<input type="password" name="txtPass" class="typing" id="txtPass" size="10">
</p>
</td>
</tr>
</table>
<p align="center">
<input type="submit" name="cmdButton" value="Login" class="button">
</p>
</form>
<p><br>
</p>
</div>
</BODY>
</HTML>


It does not display well here but i think you guys are able to understand.

[equuelus]

I have added codes that checks weather there are records or not :

Dim testResults
If objRecord.BOF and objRecord.EOF
testResults = "No Records were Returned"
Else
testResults ="Records Available"
End If

The output shows that the records are available. What else is actually wrong.?

[equuelus]

I have found out the solution. Thanks guys..

I used code :

If objRecord.EOF and objRecord.BOF then
'Invalid password or username
Else
'Password and Username right
'Execute next action
End if

Instead of

If objRecord.Count > 0
'Password and Username correct
'Execute next action
Else
'Invalid password or username
End if

Thanks for replying and giving help to me. But still i have not figured why objRecord.Count could be used? Could someone answer that?

[Clunietp]

how are you opening your recordset?

When you use:
objConnect.Execute(strQuery)

you get a server side, read only, forward only recordset which will always return -1 for its recordcount value

you need to explictly create a recordset object and open it with a client side, static recordset in order to use the recordcount property:

Dim objRS

set objRS = Server.CreateObject("ADODB.Recordset")

objRS.CursorLocation = 3
objRS.Open strQuery, objCn, 3