Help agian [Archive] - Webmaster Forums

View Full Version : Help agian

Freon22

01-21-2003, 09:28 AM

You guys saved me before and I am hoping that you will again. Here is my problem. I am trying to input infor, into a data base (access 2000) and when I run the page I get this. I have reworked this page and reworked it but I still get the same thing. I sure am hoping you guys will be able to see were I have mess up. And again thanks for looking this over.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/project/addfreon.asp, line 36

Here is a copy of my page.

<%@ Language=VBScript %>
<% Option Explicit %>




<html>
<head>
<title>Authorised User</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="currier.css">
</head>
<body>

<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString= "Driver={Microsoft AccessDriver (*.mdb)};" & _
"DBQ=" & Server.MapPath("db/dbfreon.mdb")
objConn.Open

'Open Recordset-->
Dim objRS
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "tblpurchase", objConn, , adLockOptimistic, adCmdTable

'Add new record to database
objRS.AddNew

objRS("company") = Request.Form("Company")
objRS("date") = Request.Form("Date")
objRS("purchased") = Request.Form("Purchased")
objRS("type") = Request.Form("Type")
objRS("invoice") = Request.Form("Invoice")
objRS("notes") = Request.Form("Notes")
objRS.Update <----This is line 36
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
<p><p>
<center><H1>You have added the new Freon record successfully.</H1></center>
<META HTTP-EQUIV="refresh" content="3;URL=freon.asp">
</body>
</html>

Dynasty

01-21-2003, 11:07 AM

It is possible some of the values that you are submitting to the database are not of the right type, or contain characters that your database can't have in particular fields. My guess would be the date entry that may be wrong.

You probably have to format each of the form values before trying to insert them into the database, but this is just a rough guess.

Freon22

01-21-2003, 01:22 PM

Thanks, Dynasty
It must be one of those MS mess ups. But I changed the date field in the database from date to orderdate then ajusted my code and it works great. I know I will not name another field date again. I have been trying to make that page work for the last two days I cann't thank you enough for putting me on the right track.
Thanks
Freon22

MikeParent

01-22-2003, 04:33 AM

Originally posted by Freon22
It must be one of those MS mess ups. But I changed the date field in the database from date to orderdate then ajusted my code and it works great.

Actually this is not a program mess up, except that it allowed you to use Date as a field name in the first place!

Date, Time and Now are pretty universal SQL functions that insert the current date, time or date and time (respectively). Therefore a db may have emotional problems with using those as field names or values. I would also use more descriptive names. Even if it didnt fix a bug, OrderDate is a much better field anme than simply date.

One practice you may want to get into is using polish notation for variables and db entities. It involves using a prefix in front of the variable/entity name to denote its type: for example the order date would look like:

datOrderDate (date)
tblOrders (table)
intNumOrders (integer)
strOrderItemDescription (string)

etc.

Makes things a lot easier if you can get into the habit of doing this.

Freon22

01-22-2003, 07:19 AM

Thanks, Mike
Thats a good ideal. I should have been doing that all along. Looks like it will make the code easer to read, would be able to tell if the field holds a string, integer or what ever without having to go to the database to see. Thanks will start doing it that way.
Freon22

MikeParent

01-22-2003, 02:04 PM

:-)

Its weird at first, but once you get in the habit it becomes second nature and it makes debugging a lot easier.

Some good examples:

obj = object (sorta generic - collections and things)
cn= database connection
rs = recordset
tbl = table
qry = query
sp or sproc = stored procedure
arr = array
str = string
int = integer
lng = long integer (such as autonumbers)
dat = date/time
bln = boolean (true or false)