Discussion Locked

how to add records to Access using ASP

0 helpful
10 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
i am developing a annaula leave system for my HR deprtment.
i find some examples from book with below coding:
conn.Execute "insert into HRVacatRequests (WhenRequested, EmpName, EmpEmail, " _
& "FromDate, ToDate, Purpose, Status, DepartmentID) values (" _
& "'" & CurrentDateTime & "', " _
& "'" & Request.Form("EmpName") & "', " _
& "'" & Request.Form("EmpEmail") & "', " _
& "'" & Request.Form("FromDate") & "', " _
& "'" & Request.Form("ToDate") & "', " _
& "'" & Request.Form("Purpose") & "', " _
& "'Approved', " _
& Request.Form("DepartmentID") & ")"
may I know how can i translate it into access database because when i try to run this program i got error said that the insert into is for SQL

pls assist

10 replies

by rsv

Hi,
This insert statement will work in Access also, only thing you have to do is change single quotations (') to hash (#) for the Date fields.

Hope this is helpful to you.
Good Luck...:)

by peyison

Sounds like you need to check the Provider in the connection string used to create your conn object. The connection string should look something like:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="MyDatabase.mdb;"

Hope this helps.

by hoayfern

hi,
i set this in begining of my program:
set conn=server.CreateObject ("ADODB.connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\HR\AnnualLeaveApplication.mdb;" & _
"Persist Security Info=False"
conn.Execute "insert into HRVacatRequests (WhenRequested, EmpName, EmpEmail, " _
& "FromDate, ToDate, Purpose, Status, DepartmentID) values (" _
& "#" & CurrentDateTime & "#, " _
& "'" & Request.Form("EmpName") & "', " _
& "'" & Request.Form("EmpEmail") & "', " _
& "#" & Request.Form("FromDate") & "#, " _
& "#" & Request.Form("ToDate") & "#, " _
& "'" & Request.Form("Purpose") & "', " _
& "'Approved', " _
& Request.Form("DepartmentID") & ")"

i get this error when i try to runthe program:

Error type:
Microsoft JET Database Engine (0x80040E14)
syntax error in INSERT INTO statement
/vacatrequest/html/vacation_request.asp

pls check am i have any syntax error above?

pls assist

hoay fern

by spankil

It Seems there is no Error in your Code. But can you tell me whether u r using Access 2000 or Access 97.
If Access 97 then your Provider will become
Microsoft.Jet.Oledb.3.51. Also if the TextArea (i.e. Requested Fields) Contains Single Quote (') uhave check for that and Replace "'" with "''" using Replace Function
i.e
Replace (String, "'", "''")
Try it out.

Hope this will help u.
All the Best.

Bye

by peyison

It is hard to tell just from looking at your code. Here's what I would do.

First, set your query to a string variable instead of building it in the conn.open statement. So you'd have:

sSql = "insert into HRVacatRequests (...

and

conn.execute sSql

Then comment out the line conn.execute sSql. Somewhere in the HTML body of the page, put:

<% =sSql %>

Then load your page. You shouldn't get an error because the query is not being executed, and you should have your query string displayed on your page. Copy it, open your database, create a new query (without selecting any tables) then paste the code into the SQL window. Hopefully, once you see the resolved query string, you can see where the problem is - and if you're lucky, Access will provide a better error message. It should at least point to the general location of the error in the query.

I think your connection is probably OK in the ASP script - since it knows you're trying to run an INSERT INTO statement.

Hopethis helps.

This discussion is closed to new replies.