Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    72

    Unanswered: Adding new records to MS Access

    Hello

    I have a MS Access database and a Web page, myData.asp, that displays the records in that database.

    If possible, I would like - either on the same myData.asp page or on a separate Web page - an 'Insert' or 'Add' button with a number of blank fields for me to complete that, on clicking 'Submit', would add/insert the latest data.

    The fields to be added to include Name, Address, City, etc.

    I have the basic 'insert' code, as in:

    Code:
    <% 
    'declare your variables
    dim connection, sSQL, sConnString
    
    sSQL="INSERT INTO tblFriends (FirstName, Address) VALUES ('Michael', 'Wallgate Hall')" 
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("Friends.mdb") 
    
    Set connection = Server.CreateObject("ADODB.Connection")
    
    connection.Open(sConnString)
    
    connection.execute(sSQL)
    
    response.write "The data was inserted successfully."
    
    Connection.Close
    Set Connection = Nothing
    %>
    but this is applicable only when I know the values ('Michael', 'Wallgate Hall' in this example).

    How would I add/insert other records to myData.asp (and therefore its corresponding MS Access database) using empty fields that I would need to complete and then click on 'Submit' when I do not know the field variables (Name, Address, etc) beforehand?

    Many thanks for any advice.

    Regards

    Q

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    HTML
    Code:
    <form action="myData.asp" method="post">
      <input type="text" name="FirstName" />
      <input type="text" name="Address" />
      <input type="submit" />
    </form>
    ASP (myData.asp)
    Code:
    <% 
    'declare your variables
    dim connection, sSQL, sConnString
    
    Dim friendsFirstName, friendsAddress
    
    friendsFirstName = Request.Form("FirstName")
    friendsAddress = Request.Form("Address")
    
    '''' HUGELY IMPORTANT > Read up and implement something to stop SQL injection
    ' e.g. http://stackoverflow.com/questions/149848/classic-asp-sql-injection-protection
    
    sSQL="INSERT INTO tblFriends (FirstName, Address) VALUES ('" & friendsFirstName & "', '" & friendsAddress & "')" 
    
    ...
    %>
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2006
    Posts
    72
    Hello Gvee

    Many thanks for your reply and code help .

    I will read up on the sql-injection as you suggested - I think there's a MS Analyser tool also and I'll post back in the next day or so when I have digested it!

    Much appreciated.

    Q

  4. #4
    Join Date
    Feb 2006
    Posts
    72
    Hello Gvee

    I think I have managed to consider the SQL injection you mentioned (precaution against hacking, isn't it?), and I have pasted the code below.

    For some reason the 'insert' doesn't seem to be working - maybe I have just got the code in the wrong order!?

    The 'test' page is here:

    Displaying all records from a database table

    Code:
    <%@ Language="VBScript" %>
     <% Option Explicit %>
    
    <!DOCTYPE html>
    
     <head>
     <title>Displaying all records from a database table</title>
    <link rel="stylesheet" type="text/css" href="css/styleMDB.css" />
     </head>
     <body> 
    <% 
    
     Dim Connection, Recordset, sSQL, sConnString, my_firstName, my_lastName, my_wolfID, my_telNo, my_stAddress, my_city, my_postCode, my_items, my_received, my_dispatched
    
    
    '''' HUGELY IMPORTANT > Read up and implement something to stop SQL injection
    ' e.g. http://stackoverflow.com/questions/149848/classic-asp-sql-injection-protection
    
    my_firstName = Replace( Request.Form("firstName"), "'", "''" )
    my_lastName = Replace( Request.Form("lastName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_stAddress = Replace( Request.Form("stAddress"), "'", "''" )
    my_city = Replace( Request.Form("city"), "'", "''" )
    my_postCode = Replace( Request.Form("postCode"), "'", "''" )
    my_items = Replace( Request.Form("items"), "'", "''" )
    my_received = Replace( Request.Form("received"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    
    
    sSQL="INSERT INTO tblWolf (firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched) VALUES ('" & my_firstName & "', '" & my_lastName & "', & my_wolfID & , & my_telNo & , '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "', '" & my_items & "', '" & my_received & "', '" & my_dispatched & "')" 
    
    'declare SQL statement that will query the database - use "select * from ..." to display all the records
    
    
    sSQL="SELECT TOP 30 ID, firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched FROM tblWolf"
    
    'define connection string, specify database driver and database location
    
     sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    
    'create an ADO connection and recordset
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    'Open the connection to the database
     connection.Open sConnString
    
    'Open the recordset object, execute the SQL statement
     recordset.Open sSQL,connection
    
    'HTML table to house and format results
    
    response.write "<table width='100%' border='1'>"
    %>
    
    <tr class="headerRow">
                    <td>ID</td>
    		<td >First Name</td>
    		<td >Last Name</td>
    		<td >Wolf ID</td>
    		<td >Tel No</td>
                              <td >Address</td>
    		<td >City</td>
    		<td >Postcode</td>
    		<td >Items</td>
    		<td >Received</td>
    		<td >Dispatched</td></font>
        </tr>
    
    <%
    'Determine whether there are any records
     If Recordset.EOF Then
     Response.Write "<tr><td>No records returned.</td></tr>"
     Else
    'Loop records through the fields and format in table rows & cells
    
    Do While Not recordset.EOF
    
    Response.Write "<td>" & recordset("ID") & "</td>"
    Response.Write "<td>" & recordset("firstName") & "</td>"
    Response.Write "<td>" & recordset("lastName") & "</td>"
    Response.Write "<td>" & recordset("wolfID") & "</td>"
    Response.Write "<td>" & recordset("telNo") & "</td>"
    Response.Write "<td>" & recordset("stAddress") & "</td>"
    Response.Write "<td>" & recordset("city") & "</td>"
    Response.Write "<td>" & recordset("postCode") & "</td>"
    Response.Write "<td>" & recordset("items") & "</td>"
    Response.Write "<td>" & recordset("received") & "</td>"
    Response.Write "<td>" & recordset("dispatched") & "</td></tr>" 
    'move on to the next record
     Recordset.MoveNext
     Loop
    'Close the HTML table
     response.write "</table>"
     End If
    
    'close the connection and recordset objects and free up resources
     Recordset.Close
     Connection.Close
     Set Recordset = Nothing
     Set Connection = Nothing
     %>
    
    <form action="display_records2.asp" method="post">
      <input type="text" name="First Name" />
      <input type="text" name="Last Name" />
     <input type="text" name="Wolf ID" />
     <input type="text" name="Tel No" />
     <input type="text" name="Address" />
     <input type="text" name="City" />
     <input type="text" name="Postcode" />
     <input type="text" name="Items" />
     <input type="text" name="Received" />
     <input type="text" name="Dispatched" />
      <input type="submit" />
    </form>
    
     </body>
     </html>
    If you don't mind passing a quick eye over the above, I would be grateful.

    Thanks again.

    Q

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You seem to have gotten your quotes in a twist
    Code:
    sSQL="INSERT INTO tblWolf (firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched)
    VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ",'" & my_telNo & "', '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "', '" & my_items & "', '" & my_received & "', '" & my_dispatched & "')"
    If you're having issues you need to print the value of your variables to screen and see what they look like.
    Take the results that are shown and run that directly on your database and see if it gives you a nicer error message
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2006
    Posts
    72
    Hello

    I posted the basis of this question in early July and am still wrestling with it.

    I get this connection error related to an MS Access database

    Microsoft JET Database Engine error '80004005'

    Field 'tblWolf.firstName' cannot be a zero-length string.

    /display_records2.asp, line 52

    Line 52 is this: recordset.Open sSQL,connection

    This is the relevant ASP code:

    Code:
    <% 
    
     Dim Connection, Recordset, sSQL, sConnString, my_firstName, my_lastName, my_wolfID, my_telNo, my_stAddress, my_city, my_postCode, my_items, my_received, my_dispatched
     
    my_firstName = Replace( Request.Form("firstName"), "'", "''" )
    my_lastName = Replace( Request.Form("lastName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_stAddress = Replace( Request.Form("stAddress"), "'", "''" )
    my_city = Replace( Request.Form("city"), "'", "''" )
    my_postCode = Replace( Request.Form("postCode"), "'", "''" )
    my_items = Replace( Request.Form("items"), "'", "''" )
    my_received = CDATE( Request.Form("received") )
    my_dispatched = CDATE( Request.Form("dispatched") )
    
    
    sSQL="INSERT INTO tblWolf " _
         & "(firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched)" _
         & "  VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ", " _
         & my_telNo & ", '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "'," _
         & " '" & my_items & "', #" & my_received & "#, #" & my_dispatched & "#)"
    
    
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
     connection.Open sConnString
    
    'Open the recordset object, execute the SQL statement
     recordset.Open sSQL,connection
    
    'connection.Execute sSQL
    
    'HTML table to house and format results
    
     response.write "<table width='100%' border='1'>"
    %>
    If this code is correct and I am still unable to add records, is the problem likely to lie with the DB itself?

    I am attaching a screenshot of the table properties.

    Many thanks for your time.

    Q
    Attached Thumbnails Attached Thumbnails MDB.jpg  

Posting Permissions

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