Results 1 to 5 of 5
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    ASP and Access 2000

    I am building an ASP Access 2000 driven website. I have been running it on my computer under win 98 and PWS, and what I have done so far works OK.
    I am now moving it to a test environment on a web server, and am changing my connection method.On my computer, I created a file dsn in the odbc control panel, then made the connection using
    conn.open "FILEDSN=Findthatwill.dsn"
    For the web server I have changed that to:
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("findthatwill.mdb") & ";"
    Generally this works, I can log in, retrieve and edit details. I can't add new records however.

    this code produces : Microsoft JET Database Engine error '80040e14'
    Syntax error in INSERT INTO statement.

    sql = "Insert into tblusers (username, password, Emailaddress, Firstname, Lastname, Hphone, WPhone) Values (" _
    & "'" & Request.Form("username") & "', "_
    & "'" & Request.Form("password") & "', "_
    & "'" & Request.Form("emailaddress") & "', "_
    & "'" & Request.Form("firstname") & "', "_
    & "'" & Request.Form("lastname") & "', "_
    & "'" & Request.Form("homephone") & "', "_
    & "'" & Request.Form("workphone") &"')"
    conn.Execute sql
    If I change the connection method back to the dsn it works ok, and if I capture the sql and paste it into Access it works OK, but it won't work in this context.

    Can anyone suggest what is going on?
    Regards
    John



  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: ASP and Access 2000

    I have had another go at this today, and changed my code to the following, which now works.

    set conn = Server.createObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("findthatwill.mdb") & ";"
    sql = "select * from tblUsers"
    Set rsUser = Server.CreateObject("ADODB.Recordset")
    rsUser.CursorType = adOpenkeyset
    rsUser.LockType = adlockOptimistic
    rsUser.Open sql, conn
    rsUser.AddNew
    rsUser("username") = Request.Form("username")
    rsUser("password") = request.form("password")
    rsUser("emailaddress") = Request.Form("emailaddress")
    rsUser("firstname") = Request.Form("firstname")
    rsUser("Lastname") = Request.Form("lastname")
    rsUser("Hphone") = Request.Form("homephone")
    rsUser("Wphone") = Request.Form("workphone")
    rsUser.update

    I then went on to anther page which still used the previous code and was about to change that as well, but found that the previous version worked OK there.
    So maybe I have just made some simple typo I can't see?
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP and Access 2000

    Hi John,

    I didn't notice any typo in your first statement. And the fact that it worked in Access makes me believe there was a locking/cursor conflict somewhere. The second method you've posted is the way I usually add and update data - using the ADO Recordset object. I find this is a little easier to control and as far as I can tell is just as efficient as feeding the data in with an SQL statment.

    Another property that I find invaluable is the Connection's and Recordset's CursorLocation - either adUseClient or adUseServer (be sure to set it before opening the recordset). This allows you to perform sorts and filters with your recordset.

    It sounds like you're on the right track - good luck!

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: ASP and Access 2000

    Thanks mark
    I will look into CursorLoaction.

    Another quick question. According to one of my books I should be able to use this syntax
    rsUser![username] = Request.Form("username")
    but I found it produced a compilation error (with the pointer at !] so I had to change to
    rsUser!("username") = Request.Form("username")

    I am more familiar with DAO than ADO, and so I am used to either syntax working.
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP and Access 2000

    Regarding the syntax, I've always used: recordset("field")
    (without the exclamation point !)

    I used to be an exclusive DAO person until I discovered how great ADO can be. That's not to say that ADO is always the best choice, but for general use (ASP, Access, SQL server, etc...) I find ADO to be the best choice for my purposes.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •