Problem on querying Access link table in ASP page

Hi All

I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.

My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
That's the reason I use the company email address to verify username
login name.

After I made the link to the Global Address List (rename to
StaffList), I can see the Alias field. I wrote ASP code to use Alias
field to verify user login name. But, the ASP page seems experiencing
difficulties. I always got the "Connection Timeout" Error.

The procedure I connect Exchange to Access:
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish]

My ASP code is as follow:

<%@ Language=VBScript %>

<% Response.Buffer = true %>
<%

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

The ASP code for connection.asp:
<%
Dim databaseConnection
Set databaseConnection = Server.CreateObject("ADODB.Connection")
databaseConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("/intranet/IntranetDB.mdb") & ";"
databaseConnection.Open
%>

Please tell me what do you observe? I'm really stuck.

Thank you.

Re: Problem on querying Access link table in ASP page

"tabonni" wrote in message
news:fb0148c2.0410131748.43e6c82d@posting.google.com...
> Hi All
>
> I connected MS Exchange Server to MS Access database using Access link
> table function. I can open the database table and see the fields and
> data inside the link table. However, the table seems cannot be queried
> by SQL statement.
>
> My situation is:
> I'm building an intranet. I have a ASP login page for all staff in the
> company to login. Other people can't register or login the intranet.
> That's the reason I use the company email address to verify username
> login name.
>
> After I made the link to the Global Address List (rename to
> StaffList), I can see the Alias field. I wrote ASP code to use Alias
> field to verify user login name. But, the ASP page seems experiencing
> difficulties. I always got the "Connection Timeout" Error.
>
> The procedure I connect Exchange to Access:
> [New Table -> Link Table -> File of Type [I choose Exchange()] ->
> Choose "Global Adress List" -> Rename to "StaffList -> Finish]
>
> My ASP code is as follow:
>
> <%@ Language=VBScript %>
>
> <% Response.Buffer = true %>
> <%
>
> Dim RecordSet, strSQL, strUsername
>
> strUsername = Request.Form("Username")
>
> strSQL = "SELECT Alias FROM StaffList"
>
> Set RecordSet = Server.CreateObject("ADODB.Recordset")
> RecordSet.Open strSQL, databaseConnection
>
> Do Until RecordSet.EOF
> If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
> Response.Write ("You are staff")
> Else
> RecordSet.MoveNext
> Loop
>
> RecordSet.Close
> Set RecordSet = Nothing
> databaseConnection.Close
> Set databaseConnection = Nothing
> %>
>
> The ASP code for connection.asp:
> <%
> Dim databaseConnection
> Set databaseConnection = Server.CreateObject("ADODB.Connection")
> databaseConnection.ConnectionString =
> "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
> Server.MapPath("/intranet/IntranetDB.mdb") & ";"
> databaseConnection.Open
> %>
>
> Please tell me what do you observe? I'm really stuck.
>
> Thank you.


Take a closer look at your code:

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

When there's a match you're in an infinite loop!

Either add "RecordSet.MoveNext" after your "Response.Write()" or, better
yet, write asn effective SQL statement; something like:

strSQL = "SELECT Alias FROM StaffList"
strSQL = strSQL & " WHERE Alias ='" & strUsername &"'"

If Not RecordSet.EOF Then
Response.Write ("You are staff")
End If

Re: Problem on querying Access link table in ASP page

"McKirahan" wrote in message
news:ASrbd.238350$MQ5.105850@attbi_s52...
> "tabonni" wrote in message
> news:fb0148c2.0410131748.43e6c82d@posting.google.com...

[snip]

Take another closer look at your code:

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

Where's your "End If"? Perhaps you wanted:

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Exit Do
End If
RecordSet.MoveNext
Loop

But (as I said before) why loop through the entire recordset for one
record?!

Re: Problem on querying Access link table in ASP page

Not to mention he could just make use of a WHERE clause and skip the loop
altogether.

Ray at work

"McKirahan" wrote in message
news:ASrbd.238350$MQ5.105850@attbi_s52...

>
> Take a closer look at your code:
>
> Do Until RecordSet.EOF
> If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
> Response.Write ("You are staff")
> Else
> RecordSet.MoveNext
> Loop
>
> When there's a match you're in an infinite loop!
>
> Either add "RecordSet.MoveNext" after your "Response.Write()" or, better
> yet, write asn effective SQL statement; something like:
>
> strSQL = "SELECT Alias FROM StaffList"
> strSQL = strSQL & " WHERE Alias ='" & strUsername &"'"
>
> If Not RecordSet.EOF Then
> Response.Write ("You are staff")
> End If
>
>

Re: Problem on querying Access link table in ASP page

I changed my code, but I still can't make it work. Sometimes, I got
"Microsoft JET Database Engine (0x80004005)The Microsoft Jet database
engine has already been initialized.", after I enter the username and
press submit button.

Sometimes,the page took a long time to load and got "Connection
Timeout Error" Eventually.

Because Alias is the field name of Exchange table. So, I need to put
Alias as [Alias] in SQL statment. I changed code as follow:

<%@ Language=VBScript %>

<% Response.Buffer = true %>
<%
Dim RecordSet, strUsername,strSQL
strUsername = TRIM(Request.Form("Username"))
strSQL = "SELECT [Alias] FROM StaffList WHERE [Alias]='" & strUsername
& "'"
Set RecordSet = Server.CreateObject("ADODB.Recordset"
RecordSet.Open strSQL, databaseConnection
If NOT RecordSet.EOF Then
Response.Write "You are staff"
Else
Response.Write "You are NOT staff"
End If

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

I'm wondering it is something wrong on the procedure when I connect
the MS Exchange to MS Access.
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish].

I tried to choose Exchange() and Outlook(). But, I still can't make it
work. BTW, what's the difference between Exchange() and Outlook()?

Thank you.



"Ray Costanzo [MVP]" wrote in message news:...
> Not to mention he could just make use of a WHERE clause and skip the loop
> altogether.
>
> Ray at work
>
> "McKirahan" wrote in message
> news:ASrbd.238350$MQ5.105850@attbi_s52...
>
> >
> > Take a closer look at your code:
> >
> > Do Until RecordSet.EOF
> > If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
> > Response.Write ("You are staff")
> > Else
> > RecordSet.MoveNext
> > Loop
> >
> > When there's a match you're in an infinite loop!
> >
> > Either add "RecordSet.MoveNext" after your "Response.Write()" or, better
> > yet, write asn effective SQL statement; something like:
> >
> > strSQL = "SELECT Alias FROM StaffList"
> > strSQL = strSQL & " WHERE Alias ='" & strUsername &"'"
> >
> > If Not RecordSet.EOF Then
> > Response.Write ("You are staff")
> > End If
> >
> >

Re: Problem on querying Access link table in ASP page

If you are using an exchange server then you have to be running an
active directory server

there is a easier way of doing this with out linking an exchange table
to the access db

you make it so that the login page submits to a script that takes there
username and password and varifies that with an ldap object on the
domain

the sample below uses the full e-maill (me@somedomain.com)

just replace the somedomain with your domain name (and the com with your
extension (may be .local)


'Start Sample Code
Dim dso
Dim obj1
dim msUserName
dim msPassWord

msUserName = Request.Form("UserName")
msPassWord = Request.Form("Password")

Set dso = GetObject("LDAP:")
on error resume next
' Supply full credentials to initiate a server connection.
Set obj1 = dso.OpenDSObject( _
"LDAP://somedomain.com/DC=somedomain,DC=com", _
msUserName, _
msPassword, _
ADS_SECURE_AUTHENTICATION + ADS_SERVER_BIND)

if err.number <> 0 then
'The Loging failed
else
'The User and pass are valid
end if
'End Sample Code

as long as your web server is part of the domain you shouldn't have any
problem in resolveing the ldap server.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!