Add Records to a Database

[xxLewisxx]

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

 

[mattygriff]

Provide details of your code and we'll try to sort out any problems. As for source code etc, why not check out the FAQs - there should be something there to help.

 

[xxLewisxx]

Hi,

Sorry do yo mean what code I've got so far or more details of what I need?

Thanks for replying

Lx

 

[xxLewisxx]

I don't really have much code to go off so far but I've been told I need an inc page which links to an asp page. Is this right? What's the reason for this?

Cheers,

Lx

 

[xxLewisxx]

Hi everyone.

Below is the code I've got so far. It executes the asp page and retuns a message saying the data has been added to the database but the database (Access) hasn't actually been updated.

Form user fills in:

<form method="GET" action="addnew.asp">
Objective ID:<input type="text" name="ObjectiveID" ><br>
Category Number:<input type="text" name="CategoryNo" ><br>
Student ID:<input type="text" name="StudentID" ><br>
Student Surname:<input type="text" name="StudentSurname" ><br>
Review Number:<input type="text" name="RevNo" ><br>
Goal:<input type="text" name="Goal" ><br>
Action:<input type="text" name="Action" ><br>
Outcome:<input type="text" name="Outcome" ><br>
<input type="submit" value="submit" name="submit"></p>

</p>

</form>

Asp page it links to:

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

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

Call oConn.Open(Server.Mappath("FinalProject.mdb"))
sSQL = "SELECT * FROM Tblcareplan"

set oRS = oConn.Execute(sSQL)

If Request("submit") = "submit" Then
sSQL = "INSERT INTO Tblcareplan (ObjectiveID,CategoryNo,StudentID, StudentSurname,RevNo,Goal,Action,Outcome) VALUES('" & Request("ObjectiveID") & "','" & Request("CategoryNo") & "', '" & Request("StudentID") & "','" & Request("StudentSurname") & "','"& Request("RevNo") & "','"& Request("Goal") & "','"& Request("Action") & " ','"& Request("Outcome") & " ')"
%><p>Details added into the database - thanks.<br><a href= "staffhome.html">Home</a><%

End If
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Does anybody have any suggestions?

Cheers for the help!

Lx

 

[FesterSXS]

1) Remove the SELECT statement and the Set oRS statement.

2) Then put this below your INSERT statement

oConn.Execute sSQL

Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

Hi Tony,

I tried that (see code below) but it returns the error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/dbstudents/lcbooth/FinalProject/Deliverable/addnew.asp, line 31


This is how I've done it:

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

Have I put it in the wrong place?

Thanks loads for the help,

Lucy

 

[FesterSXS]

no that looks fine.

What data types are your FORM items? From the way you have formatted your INSERT statement, they are all strings as you have single quotes surrounding them. If any of these are numbers then you don't need the quotes. That could be the error maybe.


Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

Tony,

I have taken the single quotes off the fields which are numbers but it is still returning the same error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/dbstudents/lcbooth/FinalProject/Deliverable/addnew.asp, line 31

The SQL statement now looks like this:

sSQL = "INSERT INTO Tblcareplan (ObjectiveID,CategoryNo,StudentID, StudentSurname,RevNo,Goal,Action,Outcome) VALUES(" & Request("ObjectiveID") & "," & Request("CategoryNo") & "," & Request("StudentID") & ",'" & Request("StudentSurname") & "',"& Request("RevNo") & ",'"& Request("Goal") & "','"& Request("Action") & "','"& Request("Outcome") & "')"

Can you see anything else that could be wrong with it?

Also, what should the form method be on my input form? I've got as get above but shoiuld this not be post?

Thanks again for the help

Lucy

 

[FesterSXS]

Try forcing each number to an Int before INSERTing them.

...,"& CInt(Request("RevNo")) & ",...

You can use either. If you use POST then you retrieve the values using Request.Form("Action"). If you use GET then you retrieve them using Request.QueryString("Action").


Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

Hi,

I've tried that and it's STILL returning the same error!!

If these are referred to as Int do they need to be changed anywhere else? They are set as text in the DB and text in the form (<input type="text" name="CategoryNo" >). Could this be the problem?

Sorry to keep bugging you!!

Lucy

 

[FesterSXS]

If they are text in the db then you need to put the quotes back in. Either that or change the data type of the fields in the DB from varchar to number. <INPUT TYPE="TEXT"> can stay as it is.

Have you ensured the permissions on the database and its folder are correct? IUSR account needs write access.




Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

Tony,

I have put the quote back onto the fields and have checked the database. There is no security on the database and everything is set to full access. I am also able to update records whilst in the database.

I'm runnig out of ideas!!

Lucy

 

[FesterSXS]

maybe one or more of your fieldnames is a reserved word. Just in case, surround each field name with square brackets in the SQL string.

INSERT INTO Tblcareplan ([ObjectiveID],[CategoryNo],[StudentID],[StudentSurname],[RevNo],[Goal],[Action],[Outcome].......

Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

Tony,

I tried that and still the same error message!!

Is there any other way of writing an SQL statement like this? i.e. rather than all field names then all requested field names?

Maybe it would be better to try and start again with the SQL statement!!

 

[FesterSXS]

Switch your Form from GET to POST, and then try this...

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

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

Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

We're a bit closer I think!!

This returns the page and puts all the values I entered in the address bar at the top but doesn't show the message to say it has added the record to the database (it does show the link back though) and it doesn't add the data in either!!

Thanks,

Lucy.

 

[xxLewisxx]

Also, I changed the requests to request.form too. This is right isn't it?

 

[FesterSXS]

did you switch the form method to POST? This will mean that all you Requests should be Request.Form

It will also mean your FORM data is not sent via the address bar in the QueryString. It will be sent invisibly via the FORM collection.

Tony

http://www.SpiraxSarco.com

 

[xxLewisxx]

That gives me this error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/dbstudents/lcbooth/FinalProject/Deliverable/addnew.asp, line 47


Line 47 of the code is:

oConn.Execute sSQL