General discussion

Locked

how to add records to Access using ASP

By hoayfern ·
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

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

how to add records to Access using ASP

by RSV In reply to how to add records to Acc ...

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...:)

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

The question was auto-closed by TechRepublic

Collapse -

how to add records to Access using ASP

by Peyison In reply to how to add records to Acc ...

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.

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

The question was auto-closed by TechRepublic

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

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

Collapse -

how to add records to Access using ASP

by spankil In reply to how to add records to Acc ...

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

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

The question was auto-closed by TechRepublic

Collapse -

how to add records to Access using ASP

by Peyison In reply to how to add records to Acc ...

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.

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

The question was auto-closed by TechRepublic

Collapse -

how to add records to Access using ASP

by hoayfern In reply to how to add records to Acc ...

This question was auto closed due to inactivity

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums