More webpage coding help please!!

degeneration · Post by **degeneration** » 20 Nov 2006, 19:44

Hey all. Got a lot of my info on my webpage on a database now and am trying to get myself a form together so that I can add records to it instead of uploading the entire database again everytime something is changed. Copied some forms for this that I've seen online but getting the following error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/database/add_to_database2.asp, line 82, column 12
INSERT INTO dvds (filename, title, origin, reviewed, format, rating, recent)
-----------^

I've got hte database at the path given, and the table I'm adding to is called dvds, and the columns in it are filename, title, origin, reviewed, format, rating, recent.

The error refers to the INSERT INTO line...
--------------------

<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
Dim strConnString

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Web-page\database\films.mdb"

Dim cnnFormToDB ' CONN object

Dim strTextFieldA
Dim strTextFieldB
Dim strTextFieldC
Dim strTextFieldD
Dim strTextFieldE ' text field
Dim intIntegerFieldA
Dim intIntegerFieldB ' integer field

If Request.Form("action") <> "Save Form Data" Then
' Show the form
%>
<form action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post">
<input type="hidden" name="action" value="Save Form Data" />
<table width="80%" border="0">
<tr>
<td>filename :</td>
<td><input type="text" name="filename" size="20"></td>
</tr>
<tr>
<td>Film Title :</td>
<td> <input type="text" name="title" size="20"></td>
</tr>
<tr>
<td>Origin :</td>
<td><input type="text" name="origin" size="20"></td>
</tr>
<tr>
<td>Reviewed :</td>
<td><input type="text" name="reviewed" size="20"></td>
</tr>
<tr>
<td>Review type :</td>
<td><input type="text" name="format" size="20"></td>
</tr>
<tr>
<td>Rating :</td>
<td><input type="text" name="rating" size="20"></td>
</tr>
<tr>
<td>Date (yyyymmdd) :</td>
<td><input type="text" name="recent" size="20"></td>
</tr> <td> </td>
<td>
<input type="reset" value="Clear" />
<input type="submit" value="Save" />
</td>
</tr>
</table>
</form>
<%
Else

strTextFieldA = Request.Form("filename")
strTextFieldB = Request.Form("title")
strTextFieldC = Request.Form("origin")
strTextFieldD = Request.Form("reviewed")
strTextFieldE = Request.Form("format")
intIntegerFieldA = Request.Form("rating")
intIntegerFieldB = Request.Form("recent")

Set cnnFormToDB = Server.CreateObject("ADODB.Connection")
cnnFormToDB.Open strConnString

INSERT INTO dvds (filename, title, origin, reviewed, format, rating, recent)
VALUES ('"&strTextFieldA&"','"&strTextFieldB&"','"&strTextFieldC&"','"&strTextFieldD&"','"&strTextFieldE&"',intIntegerFieldA,intIntegerFieldB)

cnnFormToDB.Close
Set cnnFormToDB = Nothing

%>
<h2>Thanks for submitting your information to us!</h2>

<%
End If

%>

</body>
</html>
---------------------

If you can help, it would be appreciated!!!

slasher13 · Post by **slasher13** » 20 Nov 2006, 19:59

Hi there,

The INSERT INTO looks fine, unless you spelt the table as DVD rather than dvds, checked out my fav site -> http://www.w3schools.com/sql/sql_insert.asp

Could it be the values part which is causing you problems rather than the sql insert?

degeneration · Post by **degeneration** » 20 Nov 2006, 20:07

I've honestly no idea... I've copied things identically and also tried a prog that automatically generates the code for you, and the same thing happens - expected end of statement...

I'd also checked that I'd called the table dvds and not dvd!!!

slasher13 · Post by **slasher13** » 20 Nov 2006, 21:15

Ok no worries, I know PHP/MySQL and SQL with access, but little on asp. What site did you check that generated the code for you, please could you show the link.. I will check it for you

degeneration · Post by **degeneration** » 20 Nov 2006, 23:21

Slasher - cheers for the offer of help, but I've got it fixed now!!

The prob was indeed with the INSERT INTO command. It is an sql command, NOT an ASP one, so it wasn't recognised.

I had to feed the sql command into an asp string, then execute the command on the database (or something like that). And now I've got it working.

Still doesn't explain why the auto generated ones that I used earlier didn't work, but I don't care now as I have a working one!

Thanks for the help!

slasher13 · Post by **slasher13** » 21 Nov 2006, 08:33

Hey Degeneration, excellent news

The prob was indeed with the INSERT INTO command. It is an sql command, NOT an ASP one, so it wasn't recognised.

I had to feed the sql command into an asp string, then execute the command on the database (or something like that). And now I've got it working.

Ahh i see, had little experience with asp...

degeneration · Post by **degeneration** » 21 Nov 2006, 17:03

AAAAARRGGGHHH!!!

I don't understand this!!!!

It worked yesterday as I added a record to the database using hte form. Went back to it today to develop it a bit more, and it didn't work BEFORE I CHANGED ANYTHING!!!

How can it go from working to not working without anything changing!?!?!

Apparently there is now a syntax error in the INSERT INTO statement... which I don't get as it wasn't there yesterday!!!

Bloody computers...

Mordib · Post by **Mordib** » 21 Nov 2006, 17:06

one thing you can try... put a simple number up everytime you change something, right at the start before any of the code so it always apears on the page no matter what else mucks up. Now yu can tell whether the error is live or not.

degeneration · Post by **degeneration** » 21 Nov 2006, 22:18

Fortunately there is no need, I've got it sorted again.

Obviously I had changed something, and that was the name of one of the columns in my database table. It appears this form to database thing does not work with a column called 'date', as 'date' is a protected name or something like that.

I changed the column name to something else and it worked absolutely fine.

You'd have thought that having a column called date would be a common thing in a database so this problem wouldn't happen - apparently not!!