Professional Web Applications Themes

Unable to save recordset into an access database - ASP Database

Hi I am trying to make a page where i simply collect data from a form and update it in the database. i am useing a recodset to do the above. The vars are being passed into the script and everythign seems to be working fine, but when i do the recorset.Update command i dont think the data is actualy being saved in the database i have also tried beginTrans and commitTrans they dont work either.....i would really appreciate if u could give me any sugestions...here's the code: <code> <% ' Connect to database Set conn_add = Server.CreateObject("ADODB.Connection") 'conn_add.mode = ...

  1. #1

    Default Unable to save recordset into an access database

    Hi
    I am trying to make a page where i simply collect data from a form and
    update it in the database. i am useing a recodset to do the above. The
    vars are being passed into the script and everythign seems to be
    working fine, but when i do the recorset.Update command i dont think
    the data is actualy being saved in the database i have also tried
    beginTrans and commitTrans they dont work either.....i would really
    appreciate if u could give me any sugestions...here's the code:
    <code>

    <%
    ' Connect to database
    Set conn_add = Server.CreateObject("ADODB.Connection")
    'conn_add.mode = 3 'readWrite mode
    conn_add.Open "DSN=semda; UserID=USERIDSTRING; pwd=PWDSTRING"

    'conn_add.BeginTrans

    if ucase(TypeName(conn_add)) = "CONNECTION" then
    response.Write("CONNECTION MADE!")
    end if


    If Request.Form("DescShort") = "" Or Request.Form("DescLong") = ""
    Then
    ErrStr = "You didn't fill in all the required fields. Please go back
    and enter all required data."
    Else 'Insert into database

    'pulling out max_job
    Set maxJobID = Server.CreateObject("ADODB.Recordset")
    maxJobID.open "Select max(JobID) as max_job from JobList", conn_add
    Do while not maxJobID.eof
    max_job=maxJobID("max_job")
    maxJobID.MoveNext
    Loop
    response.Write("<br>MAX JOB ID IS: "& max_job &"<br>")
    max_job=max_job+1
    maxJobID.Close
    Set maxJobID= Nothing

    Set sthRecordset = Server.CreateObject("ADODB.Recordset")
    on error resume next

    sthRecordset.open "JobList",conn_add,2,3
    '2,3 adOpenDynamic=2/adOpenKeyset = 1, adLockOptimistic=3

    'check for errors
    on error resume next
    If conn_add.Errors.count > 0 Then
    Set objErr = Server.CreateObject("ADODB.Error")
    for each objErr in conn_add.Errors
    If objErr.Number <> 0 Then
    response.Write("Number: " & objErr.Number & "<p>")
    response.Write("Description: " & objErr.Description & "<p>")
    response.Write("Source: " & objErr.Source & "<p>")
    response.Write("SQLState: " & objErr.SQLState & "<p>")
    response.Write("NativeError: " & objErr.NativeError & "<p>")
    End If
    next
    Else
    sthRecordset.AddNew
    response.Write("NEW RECORD ADDED")
    sthRecordset.Fields("JobID") = max_job
    sthRecordset.Fields("DescShort") = Request.Form("DescShort")
    sthRecordset.Fields("DescLong") = Request.Form("DescLong")
    sthRecordset.Fields("TypeID") = Request.Form("TypeID")
    sthRecordset.Fields("PayLow") = Request.Form("PayLow")
    sthRecordset.Fields("PayHi") = Request.Form("PayHi")
    sthRecordset.Fields("ContactName") = Request.Form("ContactName")
    sthRecordset.Fields("Phone") = Request.Form("Phone")
    sthRecordset.Fields("Fax") = Request.Form("Fax")
    sthRecordset.Fields("Email") = Request.Form("Email")
    sthRecordset.Fields("Web") = Request.Form("Web")
    sthRecordset.Fields("Verified") = "No"
    If Request.Form("ExpireDate") = "" Then
    sthRecordset.Fields("ExpireDate") = Date()+30
    Else
    sthRecordset.Fields("ExpireDate") = Request.Form("ExpireDate")
    End if
    sthRecordset.Fields("EnterDate") = Now()

    If sthRecordset.Fields("ExpireDate") = "" Then
    ErrStr = "There was a problem converting the expiration date you
    entered. " &_
    "Please go back and make sure you entered a date in the format
    specified."
    response.Write("<br>IN HERE<br>")
    sthRecordset.CancelUpdate
    'conn_add.RollbackTrans
    Else
    sthRecordset.Update
    'conn_add.CommitTrans
    JobID = sthRecordset.Fields("JobID")
    response.Write("<br>NEW RECORD ADDED TOTALY with job id: "& JobID)
    EXPdate = sthRecordset.Fields("ExpireDate")
    response.Write("<br>EXP DATE: "& EXPdate)
    End If
    sthRecordset.Close
    Set sthRecordset = Nothing
    End If
    End If
    %>

    </code>
    Anand Guest

  2. #2

    Default Re: Unable to save recordset into an access database

    Anand wrote:
    > Hi
    > I am trying to make a page where i simply collect data from a form and
    > update it in the database. i am useing a recodset to do the above. The
    > vars are being passed into the script and everythign seems to be
    > working fine, but when i do the recorset.Update command i dont think
    > the data is actualy being saved in the database i have also tried
    > beginTrans and commitTrans they dont work either.....i would really
    No error messages? Have you made sure the IUSR account has NTFS Change
    permissions on the folder containing your database?

    [url]http://www.aspfaq.com/show.asp?id=2062[/url] - updatable cursor
    [url]http://www.aspfaq.com/show.asp?id=2009[/url] - 80004005 errors

    > appreciate if u could give me any sugestions...here's the code:
    > <code>
    >
    > <%
    > ' Connect to database
    > Set conn_add = Server.CreateObject("ADODB.Connection")
    "conn_add"? Why not just "cn"? :-)
    Save yourself some typing, and make your code a little easier to read ...
    :-)
    And actually, I see in the code below that you are using this connection to
    do more than simply add a record, so tacking on that "_add" may be
    misleading.
    > 'conn_add.mode = 3 'readWrite mode
    Not necessary.

    > conn_add.Open "DSN=semda; UserID=USERIDSTRING; pwd=PWDSTRING"
    >
    Don't use ODBC. See [url]www.able-consulting.com/ado_conn.htm[/url] for examples of
    connection strings using the native OLEDB Provider for Microsoft Jet.

    Why are you providing a username and password? Is the database protected
    using a workgroup file?

    > 'conn_add.BeginTrans
    >
    > if ucase(TypeName(conn_add)) = "CONNECTION" then
    > response.Write("CONNECTION MADE!")
    > end if
    >
    >
    > If Request.Form("DescShort") = "" Or Request.Form("DescLong") = ""
    > Then
    > ErrStr = "You didn't fill in all the required fields. Please go back
    > and enter all required data."
    > Else 'Insert into database
    >
    > 'pulling out max_job
    > Set maxJobID = Server.CreateObject("ADODB.Recordset")
    Again, less-than-optimal naming convention. It is not obvious that you are
    creating a recordset variable here, At least, use a prefix, "rs", so people
    (including yourself two years from now) who encounter this variable later on
    in your code can see that it's a recordset without scrolling back up to find
    the CreateObject statement ...

    <snip>

    You do realize that if two users run this query simultaneously, that they
    will both get the same max_job value? If this app will not have a lot of
    activity, you may get away with this. However, I recommend that you convert
    JobID to an autonumber field and let Jet assign values to it.
    >
    > Set sthRecordset = Server.CreateObject("ADODB.Recordset")
    > on error resume next
    Stop here. Do not use a recordset to modify data in your database. Yes, I
    know that using a recordset makes it easy for you to code, but it is very
    inefficient and will limit the number of concurrent users that your database
    will be able to handle.

    I suggest you use Access to create a saved parameter query to do this
    insert. Here are the steps:

    1. Open the database in Access.
    2. Go to the Queries tab and click the button to create a new query in
    Design mode.
    3. Select the JobList table from the dialog to add it to the upper pane of
    the Design window, and close the dialog box.
    4. Go to the Query menu and select Append Query. In the ensuing dialog,
    select the Joblist table again from the dropdown and click OK.
    4. Use the Shift key to select/highlight all the fields you wish to insert
    data into from the table in the upper pane and drag and drop them into the
    first row (the "Field" row) of the first column in the grid. They will all
    be added to the grid. In each column, you will see that Access has put the
    corresponding field name into the Append To row.
    5. Switch to SQL View (use the View menu, or the toolbar button, or the
    right-click context menu). You will see something like

    INSERT INTO JobList (<columns>)
    SELECT <columns> FROM JobList

    What you are going to do is replace the SELECT... portion with a VALUES
    clause, so it will look like this:

    INSERT INTO JobList (<columns>)
    VALUES (<columns>)

    The easiest thing to do is replace the word "SELECT" with VALUES, put
    parentheses around the column names list, and delete the FROM clause.
    In the parentheses, change the column names to parameter names. Use a naming
    convention to guarantee that the parameter names do not match any of the
    actual field names in the table. I usually prefix them with a "p".
    It's a good idea to leave the brackets around each parameter name (or put
    brackets there if Access did not put brackets around each column name). What
    you will end up with should look like this:

    INSERT INTO JobList (JobID, DescShort, ...)
    VALUES ([pJobID], [pDescShort], ...)

    (if you take my advice to convert JobID to an autonumber field, you do not
    want to include it in this query: that field is automatically assigned a
    value when a record is inserted, including it in the query should cause an
    error when you test it)

    Save the query, giving it a name such as qInsJobListRecord. Do not switch
    back to Design View: in earlier versions of Access doing so would mess up
    your SQL.

    Now, test it by running it (use the button with the exclamation point (!) in
    the toolbar). You will see that Access will prompt you for values for each
    of the parameters you created in the VALUES list. After you supply the last
    value, you will be prompted to confirm the insert; click Yes and, then go
    check your table to verify that the record was added.

    Now you have a query which you know will work, and you can now write the
    code to execute it in ASP. So, delete the "Set sthRecordset =" line, and
    replace the recordset addnew code with these lines of code (I will include
    the JobId parameter just in case it is not an autonumber field):

    If Request.Form("ExpireDate") = "" Then
    dExpDate = Date()+30
    Else
    dExpDate = CDate(Request.Form("ExpireDate"))
    End If
    conn_add.qInsJobListRecord max_job, _
    Request.Form("DescShort"), Request.Form("DescLong"), _
    Request.Form("TypeID"), ..., dExpDate
    > JobID = sthRecordset.Fields("JobID")
    ?
    Why not:
    JobID = max_job

    If you do convert JobID to an autonumber field, you will need to do this to
    get the id of the new record:

    Set rs=conn_add.Execute("SELECT IDENTITY",,1)
    if not rs.eof then
    JobID = rs(0).value
    else
    'problem inserting record... an earlier error should have occurred
    end if


    Make sure you add this line as well:
    conn_add.close: set conn_add = nothing

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

Similar Threads

  1. unable to display all values in recordset
    By braulio! in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: September 20th, 02:12 AM
  2. Unable to Save/Save As/close Word XP file with Acrobat 5.0 installed
    By Keith_Luk@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 4
    Last Post: April 12th, 01:33 PM
  3. Replies: 1
    Last Post: April 9th, 05:06 PM
  4. Replies: 1
    Last Post: August 12th, 03:28 PM
  5. Replies: 1
    Last Post: July 1st, 11:18 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139