Recordset Problems

Night_Zero · July 14th, 2004, 01:14 PM

I'm trying to open up a database for a work project and do several things with it. I believe that once I actually get the database to open and make a record set I can handle it but so far I keep having problems. Here's the code:

<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing</TITLE>
</HEAD>
<BODY>

<%
    Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
    adOpenForwardOnly = 0
    adLockReadOnly = 1
    adCmdTable = 2

    Dim objConn, objRS
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRS = Server.CreateObject("ADODB.Recordset")

    Dim strDatabaseType
    strDatabaseType = "Access"

    If strDatabaseType = "Access" Then
        objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source = c:\inetpub\database\ftp.mdb;" & _
                 "Persist Security Info=False"
    Else
        objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
                 "USER ID=;Initial Catalog=Password;" & _
                 "Initial File Name=c:\inetpub\database\ftp.mdb;"
    End If

    objRS.Open "PASSWORD", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

    dim strSel
    strSel = "SELECT [Password] FROM Password WHERE [Password] = pillow"
    objRS.Open strSel

    While Not objRS.EOF
        Response.Write objRS("PASSWORD") & "<BR>"
        objRS.MoveNext
    Wend

    objRS.Close
    objConn.Close
    Set objRS = Nothing
    Set objConn = Nothing
%>
</BODY>
</HTML>

The error is: Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.
/adamstuff/test.asp, line 31

Night_Zero · July 14th, 2004, 02:28 PM

*Bump*

DaveGerard · July 14th, 2004, 03:07 PM

Does *Bump* mean you figured it out?

[Password] = 'pillow'
NOT
[Password] = pillow

Night_Zero · July 14th, 2004, 03:31 PM

No, bump as in bump it to the top of the list to get more people to look at it. And I changed what you said but that still isn't the problem. Its thinking that:
objRS.Open "PASSWORD", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

has a from clause in it somewhere.

DaveGerard · July 14th, 2004, 03:58 PM

Just curious, you have a field called 'Password' in a table called 'Password' is that correct? Can you try this out and see what happens?

cn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Password.mdb"

set rs = server.createobject("adodb.recordset")
sql = "select Password from Password where Password = 'Pillow'"
rs.open sql, cn
if not rs.eof then
    do while not rs.eof
    response.write rs("Password") & "<br>"
    rs.movenext
    loop
end if
rs.close
set rs = nothing

Night_Zero · July 14th, 2004, 04:11 PM

I had to change your stuff to this:

    dim cn, sql, rs
    cn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\database\ftp.mdb"
    set rs = server.createobject("adodb.recordset")
    sql = "select Password from Password where Password = 'Pillow'"
    rs.open sql, cn
    if not rs.eof then
        do while not rs.eof
        response.write rs("Password") & "<br>"
        rs.movenext
        loop
    end if
    rs.close
    set rs = nothing

The password.mdb is not the database name so it didn't exist so I changed it. Yes there is a password field in a password table. I didn't design the database and don't have access to change it but no, I still got the same error with the from clause.

DaveGerard · July 14th, 2004, 04:51 PM

This is weird. I created the same scenario and get the same error. I changed the table name and the recordset worked. I wonder if the word 'Password' is a reserved (or restricted) word when it comes to database tables.

happygv · July 15th, 2004, 06:27 AM

Yes, it is a RESERVED word in either ADO, OLEDB. Check out this page.
Scenario 1 - Syntax error in FROM clause

You have no other go, than changing the names for both the table and column(which I would suggest) or atleast either of that names.

When you execute a "SELECT PASSWORD FROM PASSWORD" directly on your database, that would result all the rows with password. But when you do that using ADO or OLEDB, you would get this error.

Hope that explains.
cheers!

_________________________
- Vijay G
Strive for Perfection

Night_Zero · July 15th, 2004, 09:15 AM

Well, I appreciate the help guys but I have a problem. My network admin says they can't change either of the names. He said that using square brackets around it would solve the problem and he has run into it before but gotten around it. It doesn't seem to fix the problem for me though. Any ideas? Thanks.

Night_Zero · July 15th, 2004, 09:26 AM

Thanks guys, I got it working.