Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add Records to a Database

Status
Not open for further replies.
xxLewisxx

xxLewisxx

Technical User
Apr 7, 2004
23
0
0
GB
Hi all,

I'm new to the site but wondered if you could help me with a problem.

I want to update an Access Database from a web front end but am not sure how to go about this. I'd preferably want to use ASP if that's possible.

The web should have input boxes the user can fill in and then a submit button to add it to the database but I can't seem to get this working (I'm fairly new to ASP)

Can any of you steer me in the right driection/offer any sample code I can alter?

Any help greatly appreciated!!

Lx
 
Sort by date Sort by votes
double check the permissions for the database AND the folder it is sat in. The IUSR account must have write permission in order for you to update the database successfully.

Tony
reddot.gif WIDTH=500 HEIGHT=2 VSPACE=3

 
Upvote 0 Downvote
Tony,

I looked at this and have moved the file and database into a different folder (think there could have been an issue with thr server permissions) but am now getting the following error:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required
/dbstudents/lcbooth/writeable/addnew.asp, line 59

Line 59 is:

oConn.Close

Do you have any ideas what the problem could be this time!!!

I've put in the full asp code below for information:

<html>

<link rel="stylesheet" type="text/css" href="stylesheet.css">

<head>

<SCRIPT TYPE="text/javascript">
<!--
function targetopener(mylink, closeme, closeonly)
{
if (! (window.focus && window.opener))return true;
window.opener.focus();
if (! closeonly)window.opener.location.href=mylink.href;
if (closeme)window.close();
return false;
}
//-->
</SCRIPT>


<title>AddNew</title>
</head>
<body>

<center><p>Careplan</p>

<%
strConnect = "DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};DBQ=" & Server.Mappath("FinalProject.mdb") & ";"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect


name=Request.Form("ObjectiveID")
name=Request.Form("CategoryNo")
name=Request.Form("StudentID")
name=Request.Form("StudentSurname")
name=Request.Form("RevNo")
name=Request.Form("Goal")
name=Request.Form("Action")
name=Request.Form("Outcome")



If Request.Form("submit") = "submit" Then
sSQL = "INSERT INTO Tblcareplan (ObjectiveID,CategoryNo,StudentID, StudentSurname,RevNo,Goal,Action,Outcome) VALUES (" & Request.Form("ObjectiveID") & "," & Request.Form("CategoryNo") & "," & Request.Form("StudentID") & ",'" & Request.Form("StudentSurname") & "',"& Request.Form("RevNo") & ",'"& Request.Form("Goal") & "','"& Request.Form("Action") & "','"& Request.Form("Outcome") & "');"

oConn.Execute sSQL
%><p>Details added into the database - thanks.<br><a href= "staffhome.html">Home</a>
<%
End If

oConn.Close
Set oConn = Nothing
%>

<p>Details added into the database - thanks.</p><br><a href= "staffhome.html">Home</a>
<%

oConn.Close
Set oConn = Nothing
%>


<p class=subheading><a href="staffhome.html" onClick="return targetopener(this)">Back to Staff Homepage</a></p>



</body>
</html>

Thanks tons for your patience!!!

Lucyx


 
Upvote 0 Downvote
Hi everyone,

I've been playing around with this again to see if I can get it working but it keeps returning the following error:

Error Type:
Microsoft VBScript compilation (0x800A03EE)
Expected ')'
lcbooth/writeable/addnew.asp, line 44, column 135

Line 44 is my sql statement (I've attached my code below) but I can't see anything wrong!!

Does anybody have any ideas/suggestions? I've been trying to get this working for 2 days now!!

<%@ Language=VBScript %>
<html>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
<head>
<SCRIPT TYPE="text/javascript">
<!--
function targetopener(mylink, closeme, closeonly)
{
if (! (window.focus && window.opener))return true;
window.opener.focus();
if (! closeonly)window.opener.location.href=mylink.href;
if (closeme)window.close();
return false;
}
//-->
</SCRIPT>

<title>AddNew</title>
</head>
<body>
<center><p>Careplan</p>
<%
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"

name=Request.Form("ObjectiveID")
name=Request.Form("CategoryNo")
name=Request.Form("StudentID")
name=Request.Form("StudentSurname")
name=Request.Form("RevNo")
name=Request.Form("Goal")
name=Request.Form("Action")
name=Request.Form("Outcome")
Call oConn.Open(Server.Mappath("FinalProject.mdb"))
If Request.Form("submit") = "submit" Then
sSQL = "INSERT INTO Tblcareplan (ObjectiveID,CategoryNo,StudentID,StudentSurname,R evNo,Goal,Action,Outcome) VALUES "(" & Request.Form("ObjectiveID") & "," & Request.Form("CategoryNo") & "," & Request.Form("StudentID") & ",'" & Request.Form("StudentSurname") & "',"& Request.Form("RevNo") & ",'"& Request.Form("Goal") & "','"& Request.Form("Action") & "','"& Request.Form("Outcome") &"')"
oConn.Execute sSQL
%><p>Details added into the database - thanks.<br><a href= "staffhome.html">Home</a>
<%
End If
oConn.Close
Set oConn = Nothing
%>
<p>Details added into the database - thanks.</p><br><a href= "staffhome.html">Home</a>
<%
oConn.Close
Set oConn = Nothing
%>

<p class=subheading><a href="staffhome.html" onClick="return targetopener(this)">Back to Staff Homepage</a></p>

</body>
</html>

Thanks in advance...
 
Upvote 0 Downvote
Lewis,

At this point I think you need to start fresh. There are way too many places for the error to be (permission problem, data type mismatch, syntax error, etc).

This is what I suggest, and I think it will save you time in the long run.

1) Make a NEW database (keep the old one though) and just have ONE table and ONE field in the table. call the table 'customer' and name the field 'nane' and have the field type be 'text'

2) Have an ASP page that just links to the database. Just open a connection and close it. Don't even use any SQL. If you get an error already, you probably got a permissions problem.

3) If you get this working, then try to do an INSERT. It should be much easier to debug since you only have 1 field.
If you get a problem now, its probably a syntax one and it should be easy to figure out.

4) Now that you got to this step, it means you have everything working. Now you can go back to your old database. You should compare the working code to your old code to see where you went wrong.

Let us know how it works out.
 
Upvote 0 Downvote
Just out of curiousity, what is the point of these lines in your code?

name=Request.QueryString("ObjectiveID")
name=Request.QueryString("CategoryNo")
name=Request.QueryString("StudentID")
name=Request.QueryString("StudentSurname")
name=Request.QueryString("RevNo")
name=Request.QueryString("Goal")
name=Request.QueryString("Action")
name=Request.QueryString("Outcome")

You don't use them anywhere in your code. Even in your insert, you were grabbing the values right out of the form.
 
Upvote 0 Downvote
lovejaeeun,

I have taken your advice and started a new database (which I can connect to and retrieve data) with 1 table. I then re-wrote the code (as below) and ran it as usual but it still comes up with the same error:

Error Type:
Microsoft VBScript compilation (0x800A03EE)
Expected ')'
/dbstudents/lcbooth/writeable/addtest.asp, line 36, column 64

I've looked at column 64 (the word form) which is where the * is in the code below (sql statement). This was the same place it was with the old code but I can't figure what the problem could be with it! It looks as though it's expecting a bracket but there's definately not one missing (that I can see)

<%@ Language=VBScript %>
<html>

<link rel="stylesheet" type="text/css" href="stylesheet.css">

<head>

<SCRIPT TYPE="text/javascript">
<!--
function targetopener(mylink, closeme, closeonly)
{
if (! (window.focus && window.opener))return true;
window.opener.focus();
if (! closeonly)window.opener.location.href=mylink.href;
if (closeme)window.close();
return false;
}
//-->
</SCRIPT>


<title>AddNew</title>
</head>
<body>

<center><p>Careplan</p>

<%
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"


Call oConn.Open(Server.Mappath("project.mdb"))

If Request.Form("submit") = "submit" Then
sSQL = "INSERT INTO customer (cname) VALUES "(" & Request.Fo*rm("cname") & ")"

oConn.Execute sSQL

response.write sSQL

%><p>Details added into the database - thanks.<br><a href= "staffhome.html">Home</a>
<%
End If

oConn.Close
Set oConn = Nothing
%>

</body>
</html>

Any other ideas?!

Thanks for the help

Lucy
 
Upvote 0 Downvote
You have one too many double quotes (") and are missing two single quotes ('). Use the string below:

sSQL = "INSERT INTO customer (cname) VALUES ('" & Request.Form("cname") & "')"


 
Upvote 0 Downvote
Another suggestion (for when you get this working).

Use the replace function to replace any single quotes:
replace(request.form("cname"),"'","''")

If you don't do this, and someone puts a (') in your form, you will get an error. Also, it opens up the chance for SQL injection.

You might also want to use the trim() function because (from what I hear) some browsers append blank spaces to the end of form contents:

replace(trim(request.form("cname")),"'","''")

If anyone else has any comments/experience about this, please let me know.
 
Upvote 0 Downvote
lovejaeeun,

I tried that and it is bringing up the the following error:

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/dbstudents/lcbooth/writeable/addtest.asp, line 39

I've checked the permissions on the database/folder and they're full access. Is that what this is referring to?

Lucy.
 
Upvote 0 Downvote
Try this, copy and paste it:

<html>
<head><link rel="stylesheet" type="text/css" href="stylesheet.css">
<title>AddNew</title>
</head>

<body>
<%
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open(Server.Mappath("project.mdb"))

If Request.Form("submit") = "submit" Then
sSQL = "INSERT INTO customer (cname) VALUES ('" & Request.Form("cname") & "')"

oConn.Execute sSQL

response.write sSQL
%>
<p>Details added into the database - thanks.<br><a href="staffhome.html">Home</a>
<%
End If

oConn.Close
Set oConn = Nothing
%>

</body>
</html>
 
Upvote 0 Downvote
lovejaeeun,

I tried that and it gives me the returned page but it doesn't update the database or display the sql statement.

I ran the sql query in access and it works but when i out in the name value (booth) it brings up an input box asking me to enter a perameter value with the value in the sql statement at the top of the box. It then enters the value I input into the table.

Cheers,

Lucy
 
Upvote 0 Downvote
Then I would guess that your submit button is not named or it is named something other than "submit".

Take out the lines:

If Request.Form("submit") = "submit" Then



End If

It should now work.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
77
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
99
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
79
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
92
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
216
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top