Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Feb 2006
    Posts
    72

    Unanswered: Deleting/adding records online

    Hello

    I have an MS Access database and you can see a table in it here:

    http://stevehigham59.7host.com/showDetails.asp

    Would it be very difficult to include, on this web page, a facility which allows the site visitor to say, delete or add to the records. Maybe search them, or amend them?

    Many thanks.

    Steve

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hello again Steve!
    It's nice to see that you've made some good progress on your ASP

    Perhaps you should consider using table headers though
    Code:
    Name | Country
    Dave | UK
    John | USA
    etc.
    Anyway, back to the question in hand...
    It is not too difficult to create such a function, but there is a lot more too it than meets the eye

    The biggest thing to consider when doing this is something called SQL injection - which can cause you all types of horrible problems (imagine someone deleting all your information simply by entering some commands in a textbox and submitting them!!) which do happen.

    But before we leap into SQL injection, let's take a look at the basic commands we'll need:

    And if we get those two done we can move on to

    Before you even start trying to stick this on the web - have a play with the syntax within your access database. Open a new query, change to SQL view and type away!
    Once you're happy you understand the way things work post an example of each statement back up here and we'll take a look at them
    George
    Home | Blog

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

    Many thanks for your advice again. The W3 links look good and I have downloaded them. I'll mess about with them over the weekend and get back.

    Cheers

    Steve

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

    I'll be experimenting tonight with the INSERT into, but if I want this on the showFile.asp page, what about an INSERT button?

    Cheers

    Steve

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    <%
    Function Insert()
    
      '***insert your code here
    
    End Function
    %>
    
    <input type="button" value="Click me!" onclick="Insert()" />
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I have pasted in the following:

    <%
    Function Insert()

    INSERT INTO 'Enquiries' (Full Name, Sex, Country, Hobbies, Date of Birth)
    VALUES ('Penelope Rambottom', Female, 'New Zealand', Riding, '29/10/1977')
    ('Pauline Gibtips', Female, Canada, 'Pole vaulting', '12/1/1982')

    End Function
    %>

    based on the tutorial here:

    http://www.1keydata.com/sql/sqlinsert.html

    and the example they give:

    INSERT INTO Store_Information (store_name, Sales, Date)
    VALUES ('Los Angeles', 900, 'Jan-10-1999')

    But this generates the following:

    Syntax error

    /showDetails.asp, line 63

    VALUES ('Penelope Rambottom', Female, 'New Zealand', Riding, '29/10/1977')
    --------^

    I thought from the example that using more than one word requires inverted commas, as in: 'two words'. Is the tutorial wrong, do you think?

    Steve

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good guess, but not quite.

    Single quotes are what we use to wrap pieces of text.
    For example
    Code:
    INSERT INTO myTable(numericField, alphanumericField)
    VALUES(1234, 'this is some text')
    Dates are a funny one still!!
    The easiest way (imo) to "do dates" is to use the universal date convention, wrapped in single quotes.
    YYYYMMDD
    /
    YYYY-MM-DD HH:MIS.NNN
    Code:
    INSERT INTO myTable(dateField)
    VALUES('20071231')
    --or
    INSERT INTO myTable(dateField)
    VALUES('2007-12-31')
    --or
    INSERT INTO myTable(dateField)
    VALUES('2007-12-31 23:59:59.003')
    Now looking back at your ASP, I see a number of problems...

    Problem 1 - this is just text, it'll sit there and do nothing until the cows come home (actually, it won't do nothing - it will error!)
    You have to execute the query commmand against a datasource. If you post the code you're using to access the data (so your recordset / connection objects etc) then we can look into customizing this for the inserts.

    Peoblem 2 - I'm too tired to type anymore so 1 should be enough for now
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I have changed the inverted commas to what you suggested and doubled checked on column and name spellings in the DB itself.

    This is the error I get:

    Expected end of statement

    /showDetails.asp, line 62

    INSERT INTO Enquiries (Full Name, Sex, Country, Hobbies, Date of Birth)
    ------------^

    I take it that the browser is looking at this part of my Response.Write statement: ("Full Name"), because I can see a space there, though there is no space in the table name. It is FullName (no spaces).

    I have definitely spelt Enquiries correctly, too.

    The code as I have it at the moment looks like this:

    <%
    Dim DB, TBL
    Set DB = Server.CreateObject("ADODB.Connection")
    Set TBL = Server.CreateObject("ADODB.RecordSet")
    DB.Mode = adModeReadWrite

    DB.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\folder\Form.mdb;"

    TBL.Open "Enquiries" ,DB

    Response.Write("<table border='1'>")

    While NOT TBL.EOF


    Response.Write("<tr><td>FullName</td><td><p>" & TBL("Full Name") & "</p></td>")
    Response.Write("<td>Sex</td><td><p>" & TBL("Sex") & "</p></td>")
    Response.Write("<td>Country</td><td><p>" & TBL("Country") & "</p></td>")
    Response.Write("<td>Hobbies</td><td><p>" & TBL("Hobbies") & "</p></td>")
    Response.Write("<td>DateofBirth</td><td><p>" & TBL("Date of Birth") & "</p></td></tr>")

    TBL.MoveNext
    Wend
    TBL.Close

    Response.Write("<caption>Data entries</caption>")

    Response.Write("</table>")

    DB.Close

    Set DB = Nothing
    Set TBL = Nothing

    %>

    <%
    Function Insert()

    INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')
    (Pauline Gibtips, Female, Canada, Pole vaulting, '1982-01-12')

    End Function
    %>

    Can you see which part of it might be causing the error?

    Many thanks again.

    Steve

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can see the problem...
    Your function is just words - it doesn't DO anything. You don't tell the page to do anything with your insert statement, there is also a couple of syntax errors on the insert statement but if you re-read my previous post (namely the first code snippet) then you'll cotton on to one of them (the other is that you can only insert 1 set of data at a time with this style of INSERT command).

    The first thing I'd do here is build the command up as a literal string command (wrapped in double quotes!)
    Code:
    <%
    Function Insert()
      Dim commandText
    
      commandText = ""
      commandText = commandText & "INSERT INTO Enquiries"
      commandText = commandText & "(FullName, Sex, Country, Hobbies, DateofBirth)"
      commandText = commandText & " VALUES (Penelope Rambottom, Female, New Zealand"
      commandText = commandText & ", Riding, '1977-10-13')"
    
    End Function
    %>
    Note how I've concatenated the string using ampersands (&) which simply stick two pieces of text together (you've got to be careful to make sure you keep the necessary spacing though!).
    This could be equally written as
    Code:
      commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth) VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')"
    Now that we have our statement constructed we can look into executing it on our database... I'll write a follow up post on this later, gotta run!
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    'Your code from above
    Dim DB, TBL
    Set DB = Server.CreateObject("ADODB.Connection")
    DB.Mode = adModeReadWrite 
    
    DB.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\folder\Form.mdb;"
    'End of your code from above
    
    
    On Error Resume Next
    
    DB.Execute commandText
    If Err=0 then
      Response.Write("Success")
    Else 
      Response.Write("Fail")
    End If
    DB.close
    Note the use of the Execute command which allows us to run our SQL statement agains the data source we specify in our connection.

    As always, any questions just ask!
    You don't learn anything from copy and pasting
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    Thanks for your time.

    This new script:

    <%
    Function Insert()
    Dim commandText

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country,
    Hobbies, DateofBirth) VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')"

    End Function
    %>

    does not look too different from what we originally had, namely:

    <%
    Function Insert()

    INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')


    End Function
    %>


    but I know that each ' and ; are of crucial importance.

    Would I need to repeat the VALUES if I had more than one row to insert?

    For instance, if I wished to insert, say, Peter Brown, Male, UK, Snooker, 1970-04-24, and Mary Johanson, Female, Sweden, Pubs, 1980-07-01, would I need to write:

    <%
    Function Insert()
    Dim commandText

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')"

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Peter Brown, Male, UK, Snooker, '1970-04-24')"

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Mary Johanson, Female, Sweden, Pubs, '1980-07-01')"


    End Function
    %>

    or can I do this:

    Function Insert()
    Dim commandText

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)
    VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')
    (Peter Brown, Male, UK, Snooker, '1970-04-24')
    (Mary Johanson, Female, Sweden, Pubs, '1980-07-01')"

    Thanks George, you are a great help.

    Steve

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I'm afraid that we've got our wires crossed with the single quotes.
    I'll clear things up before we go any further
    Code:
    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth)"
    commandText = commandText & "VALUES ('Mary Johanson', 'Female, Sweden', 'Pubs', '1980-07-01')"
    As for the multiple inserts...
    You can only have one VALUES clause per INSERT statement.
    However this does not mean you cannot insert multiple entries, it's just a little more complicated. You can indeed use the repeated method (one statement per insert) and I advise you stick with single inserts for now until you get this whole thing working!
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I am getting completely confused now - and that's without adding your 'on error' part of the script!

    This is what I have:

    <%
    Dim DB, TBL
    Set DB = Server.CreateObject("ADODB.Connection")
    Set TBL = Server.CreateObject("ADODB.RecordSet")
    DB.Mode = adModeReadWrite

    DB.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\folder\form.mdb;"

    TBL.Open "Enquiries" ,DB

    Response.Write("<table border='1'>")

    While NOT TBL.EOF


    Response.Write("<tr><td>FullName</td><td><p>" & TBL("Full Name") & "</p></td>")
    Response.Write("<td>Sex</td><td><p>" & TBL("Sex") & "</p></td>")
    Response.Write("<td>Country</td><td><p>" & TBL("Country") & "</p></td>")
    Response.Write("<td>Hobbies</td><td><p>" & TBL("Hobbies") & "</p></td>")
    Response.Write("<td>DateofBirth</td><td><p>" & TBL("Date of Birth") & "</p></td></tr>")

    TBL.MoveNext
    Wend
    TBL.Close

    Response.Write("<caption>Data entries</caption>")

    Response.Write("</table>")

    DB.Close

    Set DB = Nothing
    Set TBL = Nothing

    %>

    <%
    Function Insert()

    Dim commandText

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth) VALUES ('Penelope Rambottom', 'Female', 'New Zealand', 'Riding', '1977-10-13')"
    End Function
    %>

    I think I have the inverted commas correct, but I get the following error:

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    I thought this might be a database problem (field names, etc), but it doesn't appear so.

    here is a screen shot of my DB:

    http://www11.brinkster.com/stevehigham/dbScreen.html

    If I try the script like this:

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth) VALUES (Penelope Rambottom, Female, New Zealand, Riding, '1977-10-13')"
    End Function


    that is, with the inverted commas removed around the different values I have (except for the date), I get the same error message.

    My head's spinning!

    Cheers

    Steve

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The error message suggests that the problem lies her:
    Code:
    Response.Write("<tr><td>FullName</td><td><p>" & TBL("Full Name") & "</p></td>")
    Response.Write("<td>Sex</td><td><p>" & TBL("Sex") & "</p></td>")
    Response.Write("<td>Country</td><td><p>" & TBL("Country") & "</p></td>")
    Response.Write("<td>Hobbies</td><td><p>" & TBL("Hobbies") & "</p></td>")
    Response.Write("<td>DateofBirth</td><td><p>" & TBL("Date of Birth") & "</p></td></tr>")
    As a further note, I suggest you put the function in the head section of your asp page - that's if memory serves

    Once again, you have created the command string, but what are you doign with it?

    Why don't you create a blank single page with just the one button, and that button performs the insert. Treate each bit separately and get each one to work before leaping to far ahead of yourself
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2006
    Posts
    72
    George, thank you for pointing out the 'requested name or ordinal' error.

    The problem was where you thought it was:

    Response.Write("<tr><td>FullName</td><td><p>" & TBL("Full Name") & "</p></td>")

    should have been:

    Response.Write("<tr><td>Full Name</td><td><p>" & TBL("FullName") & "</p></td>")

    while

    Response.Write("<td>DateofBirth</td><td><p>" & TBL("Date of Birth") & "</p></td></tr>")

    should have been:

    Response.Write("<td>Date of Birth</td><td><p>" & TBL("DateofBirth") & "</p></td></tr>")

    I can now see the records on line:

    http://stevehigham59.7host.com/showDetails.asp

    I was asking the database for records under the field headings "Full Name" and "Date of Birth", where none existed. I should have requested records under the field headings FullName and DateofBirth. The fields "Full Name" and "Date of Birth" (with spaces) correspond to how I would like the browser
    to display the records, and not how they are actually set up in the table.

    Are you suggesting that I use the following

    <%
    Function Insert()
    Dim commandText

    commandText = "INSERT INTO Enquiries (FullName, Sex, Country, Hobbies, DateofBirth) VALUES ('Penelope Rambottom', 'Female', 'New Zealand', 'Riding', '1977-10-13')"


    End Function


    On Error Resume Next

    DB.Execute commandText
    If Err=0 then
    Response.Write("Success")
    Else
    Response.Write("Fail")
    End If
    DB.close

    <input type="button" value="Click me!" onclick="Insert()" />

    %>

    in the <HEAD> tags in a separate 'insert.asp' file?

    I imagine that I would then need to point to the database again (in the insert.asp file), and open it before inserting any records and that once I have made the insertion, to close it again. When I have done that, I should be able to go to my showDetails.asp file, click refresh, and see my new entry.

    Does that sound about right?

    Steve

Posting Permissions

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