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

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
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:

• INSERT
• DELETE

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

• UPDATE

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

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

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

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

Good guess, but not quite.

Single quotes are what we use to wrap pieces of text.
For example
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
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

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

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!)
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
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!

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

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

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
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!

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

The error message suggests that the problem lies her:
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, 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