export data from asp to .mdb?

export data from asp to .mdb?

am 07.03.2006 16:30:27 von MrPen

Hi

I am currently trying to get a simple data submission asp form to export
data to an access database, this is the code for the contact_insert page








This page was generated using ASP Helper [www.a2zhelp.com]















Adding data to the database

<%const DATABASE_NAME="bigbrother.mdb"
dim Con
set Con=server.createobject("ADODB.Connection")
Con.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&
server.mappath(DATABASE_NAME) & ";User Id=Admin;Password="
%>

<%'-------Getting Name
dim Name
Name=Request("Name")
Name=Replace(Name,"'","''")%>
<%if trim(Name)="" then
bHasError=true
sErrMessage=sErrMessage & "
  • Name cannot be empty."
    end if%>
    <%'-------Getting Email
    dim Email
    Email=Request("Email")
    Email=Replace(Email,"'","''")%>
    <%if trim(Email)="" then
    bHasError=true
    sErrMessage=sErrMessage & "
  • Email cannot be empty."
    end if%>
    <%'-------Getting Query
    dim Query
    Query=Request("Query")
    Query=Replace(Query,"'","''")%>
    <%if trim(Query)="" then
    bHasError=true
    sErrMessage=sErrMessage & "
  • Query cannot be empty."
    end if%>
    <%if bHasError then%>

    width=100%><%Response.Write("Sorry we cannot proceed because of the following
    errors") %>
    <%response.write
    sErrMessage%>

    <%else%>
    <%Dim sSQL
    sSQL ="insert into [Contact]([Name],[Email],[Query]) values( " & "'" & Name
    & "','"&Email&"','"&Query&"')"
    con.Execute sSQL
    %>
    Congratulations!!! Database was successfully updated.
    <%End if
    %>
    		•






    color="#0000FF">

    This
    page was automatically generated using Kalptaru Infotech Ltd's ASP
    Helper,

    the automatic ASP Code generation software.

    For more information on how to improve the efficiency and speed of
    your ASP Programming and to know more about ASP Helper visit href="http://www.a2zhelp.com">www.a2zhelp.com
    or send an email to href="mailto:asphelper@a2zhelp.com">asphelper@a2zhelp.com





    and this is the error message i get:

    Technical Information (for support personnel)

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
    query.
    /contact_insert.asp, line 65


    Browser Type:
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

    Page:
    POST 81 bytes to /contact_insert.asp

    POST Data:
    Name=paul&Email=pen@hotmail.com&Query=how+much+is+that+recor d+in+the+window%3F...

    Time:
    07 March 2006, 15:11:00

    any help would be gratefully appreciated

    paul

    Re: export data from asp to .mdb?

    am 07.03.2006 17:34:30 von Paxton

    Mr Pen wrote:
    > Hi
    >
    > I am currently trying to get a simple data submission asp form to export
    > data to an access database, this is the code for the contact_insert page
    >
    >
    >
    >
    >
    >
    >
    >
    > This page was generated using ASP Helper [www.a2zhelp.com]
    >
    >
    >
    >


    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    Adding data to the database

    > <%const DATABASE_NAME="bigbrother.mdb"
    > dim Con
    > set Con=server.createobject("ADODB.Connection")
    > Con.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&
    > server.mappath(DATABASE_NAME) & ";User Id=Admin;Password="
    > %>
    >
    > <%'-------Getting Name
    > dim Name
    > Name=Request("Name")
    > Name=Replace(Name,"'","''")%>
    > <%if trim(Name)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Name cannot be empty."
    > end if%>
    > <%'-------Getting Email
    > dim Email
    > Email=Request("Email")
    > Email=Replace(Email,"'","''")%>
    > <%if trim(Email)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Email cannot be empty."
    > end if%>
    > <%'-------Getting Query
    > dim Query
    > Query=Request("Query")
    > Query=Replace(Query,"'","''")%>
    > <%if trim(Query)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Query cannot be empty."
    > end if%>
    > <%if bHasError then%>
    >
    >
    > width=100%><%Response.Write("Sorry we cannot proceed because of the following
    > errors") %>
    <%response.write
    > sErrMessage%>

    > <%else%>
    > <%Dim sSQL
    > sSQL ="insert into [Contact]([Name],[Email],[Query]) values( " & "'" & Name
    > & "','"&Email&"','"&Query&"')"
    > con.Execute sSQL
    > %>
    > Congratulations!!! Database was successfully updated.
    > <%End if
    > %>

    • >
    >
    >
    >
    >

    >

    > color="#0000FF">

    > This
    > page was automatically generated using Kalptaru Infotech Ltd's ASP
    > Helper,

    > the automatic ASP Code generation software.

    > For more information on how to improve the efficiency and speed of
    > your ASP Programming and to know more about ASP Helper visit > href="http://www.a2zhelp.com">www.a2zhelp.com
    > or send an email to > href="mailto:asphelper@a2zhelp.com">asphelper@a2zhelp.com

    >
    >


    >

    >
    >
    >
    > and this is the error message i get:
    >
    > Technical Information (for support personnel)
    >
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    > [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
    > query.
    > /contact_insert.asp, line 65
    >
    >
    > Browser Type:
    > Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
    >
    > Page:
    > POST 81 bytes to /contact_insert.asp
    >
    > POST Data:
    > Name=paul&Email=pen@hotmail.com&Query=how+much+is+that+recor d+in+the+window%3F...
    >
    > Time:
    > 07 March 2006, 15:11:00
    >
    > any help would be gratefully appreciated
    >
    > paul

    Where to start....

    The error message you are receiving is usually to do with permissions
    on the database. The IUSR_machinename account needs read and write
    permission on the directory holding the database file.

    Second - you are using the obselete ODBC driver. You should be using
    the native Jet OLEDB driver instead.

    Third - you should be using parameter markers instead of dynamic sql
    http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

    Or better still saved parameter queries:
    http://groups.google.com/group/microsoft.public.inetserver.a sp.db/msg/b3d322b882a604bd

    (Those last two links courtesy of Bob Barrows.)

    Fourth, your use of the variable Name could cause problems in certain
    circumstances. It is a VBScript native keyword.

    Frankly, if "ASPHelper" came up with this code, I'd ditch it and learn
    ASP properly.

    --
    Mike Brind

    Re: export data from asp to .mdb?

    am 07.03.2006 17:41:07 von McKirahan

    "Mr Pen" wrote in message
    news:59876DDE-BE6B-4464-AD42-6CA0F928D61D@microsoft.com...
    > Hi
    >
    > I am currently trying to get a simple data submission asp form to export
    > data to an access database, this is the code for the contact_insert page
    >
    >
    >
    >
    >
    >
    >
    >
    > This page was generated using ASP Helper [www.a2zhelp.com]
    >
    >
    >
    >


    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    Adding data to the database

    > <%const DATABASE_NAME="bigbrother.mdb"
    > dim Con
    > set Con=server.createobject("ADODB.Connection")
    > Con.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&
    > server.mappath(DATABASE_NAME) & ";User Id=Admin;Password="
    > %>
    >
    > <%'-------Getting Name
    > dim Name
    > Name=Request("Name")
    > Name=Replace(Name,"'","''")%>
    > <%if trim(Name)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Name cannot be empty."
    > end if%>
    > <%'-------Getting Email
    > dim Email
    > Email=Request("Email")
    > Email=Replace(Email,"'","''")%>
    > <%if trim(Email)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Email cannot be empty."
    > end if%>
    > <%'-------Getting Query
    > dim Query
    > Query=Request("Query")
    > Query=Replace(Query,"'","''")%>
    > <%if trim(Query)="" then
    > bHasError=true
    > sErrMessage=sErrMessage & "
  • Query cannot be empty."
    > end if%>
    > <%if bHasError then%>
    >
    >
    > width=100%><%Response.Write("Sorry we cannot proceed because of the
    following
    > errors") %>
    <%response.write
    > sErrMessage%>

    > <%else%>
    > <%Dim sSQL
    > sSQL ="insert into [Contact]([Name],[Email],[Query]) values( " & "'" &
    Name
    > & "','"&Email&"','"&Query&"')"
    > con.Execute sSQL
    > %>
    > Congratulations!!! Database was successfully updated.
    > <%End if
    > %>

    • >
    >
    >
    >
    >

    >

    > color="#0000FF">

    > This
    > page was automatically generated using Kalptaru Infotech Ltd's
    ASP
    > Helper,

    > the automatic ASP Code generation software.

    > For more information on how to improve the efficiency and
    speed of
    > your ASP Programming and to know more about ASP Helper visit
    > href="http://www.a2zhelp.com">www.a2zhelp.com
    > or send an email to > href="mailto:asphelper@a2zhelp.com">asphelper@a2zhelp.com

    >
    >


    >

    >
    >
    >
    > and this is the error message i get:
    >
    > Technical Information (for support personnel)
    >
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    > [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
    > query.
    > /contact_insert.asp, line 65
    >
    >
    > Browser Type:
    > Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
    >
    > Page:
    > POST 81 bytes to /contact_insert.asp
    >
    > POST Data:
    >
    Name=paul&Email=pen@hotmail.com&Query=how+much+is+that+recor d+in+the+window%
    3F...
    >
    > Time:
    > 07 March 2006, 15:11:00
    >
    > any help would be gratefully appreciated
    >
    > paul

    Basically, you're using the wrong "cursor".
    http://www.aspfaq.com/show.asp?id=2062

    Also, "Name" is a reserved word.
    It should not be used as the name of a field or variable.

    It's not practice to mix CSS and tags.

    The link http://www.aspfaq.com/show.asp?id=2126 receommends using
    "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source="
    instead of
    "DRIVER={Microsoft Access Driver (*.mdb)};User Id=Admin;Password=";DBQ="

    Also, you should use Server.MapPath() to resolve your databases' path.

    "Option Explicit" should be used to ensure that all variables are declared.


    Perhaps this untested rewrite will help. Watch for word-wrap.

    <%@ Language="VBScript" %>
    <% Option Explicit
    '*
    Const cMDB = "bigbrother.mdb"
    Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database
    Password=;Data Source="
    '*
    Const adCmdTable = &H0002
    Const adLockBatchOptimistic = 4
    Const adOpenKeySet = 1
    '*
    Dim sERR
    sERR = ""
    Dim sMSG
    '*
    Dim sName
    sName = Trim(Request("Name"))
    Dim sEmail
    sEmail = Trim(Request("Email"))
    Dim sQuery
    sQuery = Trim(Request("Query"))
    '*
    If sName = "" Then
    sERR = sERR & "
  • Name cannot be empty."
    End If
    If sEmail = "" Then
    sERR = sERR & "
  • Email cannot be empty."
    End If
    If sQuery = "" Then
    sERR = sERR & "
  • Query cannot be empty."
    End If
    '*
    If sERR <> "" Then
    sMSG = "
    "
    sMSG = sMSG & "Sorry, we cannot proceed because of the following
    errors"
    sMSG = sMSG & "
    "
    sMSG = sMSG & sERR
    sMSG = sMSG & "
    "
    Else
    Dim oADO
    Set oADO = Server.CreateObject("ADODB.Connection")
    oADO.Open cDSN & Server.MapPath(cMDB)
    Dim oRST
    Set oRST = Server.CreateObject("ADODB.Recordset")
    oRST.Open "[Contact]", oADO, adOpenKeySet, adLockOptimistic,
    adCmdTable
    oRST.AddNew
    oRST("Name").Value = Replace(sName,"'","''")
    oRST("Email").Value = Replace(sEmail,"'","''")
    oRST("Query").Value = Replace(sQuery,"'","''")
    oRST.Update
    oRST.Close
    Set oRST = Nothing
    oADO.Close
    Set oADO = Nothing
    sMSG = "Congratulations!!! Database was successfully updated."
    End If
    %>


    This page was generated using ASP Helper [www.a2zhelp.com]


















    Adding data to the database
      <%=sMSG%>  









    This page was automatically generated using Kalptaru Infotech Ltd's
    ASP Helper,


    the automatic ASP Code generation software.


    For more information on how to improve the efficiency and speed of
    your ASP Programming and to know more about ASP Helper visit

    or send an email to



    • Re: export data from asp to .mdb?

    am 07.03.2006 17:48:56 von reb01501

    Mr Pen wrote:
    >
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    > [Microsoft][ODBC Microsoft Access Driver] Operation must use an
    > updateable query.
    > /contact_insert.asp, line 65
    >
    http://www.aspfaq.com/show.asp?id=2062

    General recommendations:
    http://www.aspfaq.com/show.asp?id=2126
    http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.