Execute Query in Access from an ASP Form?

[JL]

Hi,

I have an Access database with a query that selects a set of records from
one table (work categories) and inserts them (multiple rows) in to a
timesheet table, along with the employee's name and selected week number
(both of which are variables inputted by the user).
i.e. each record from the employee's chosen work categories gets inserted in
to a new row in the timesheet table, with their name and chosen week number
inserted (these are the same for each row in this recordset instance).

I want to execute this query from an intranet based ASP file, using two text
boxes and a submit button, but can't get it to work.

The query works great from within Access (by double clicking), but I am
having problems defining the code I need to pass the parameters and execute
the query from the ASP file.

Has anyone got any ideas on what I can use here?

Many thanks

JL

 

[solex]

JL,
Post the code you have tried

 

[Bob Barrows]

Without details, I can only give a generic solution:
1. Assign the values from the Request object to variables and validate them:

dim empName, wkNumber
empName=Request.Form("txtEmpName")
If len(empName) = 0 then
response.write "Empty Employee Name"
Or
redirect to error page
end if
wkNumber = Request.Form("txtWeekNumber")
If len(empName) = 0 then
response.write "No Week Number provided"
Or
redirect to error page
end if
on error resume next
wkNumber = CInt(wkNumber)
if err <> 0 then
response.write "Week Number was not supplied numerically"
Or
redirect to error page
end if

2.Create and open a connection object, cn:

dim cn
set cn=server.createobject(adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"p:\ath\to\database.mdb"

3. Run the query (assume it's called qAddRecords, and the parameters are
defined in this order: employee name, then week number):
cn.qAddRecords empName, wkNumber

4. handle errors
5. then close and destroy the connection:
cn.close
set cn = nothing

HTH,
Bob Barrows

 

[JL]

Hi,

Thanks for the interest.

Here is the code I am using for the insert query:

INSERT INTO TimeSheets ( Eng, JobID, JobName, WeekNo, SunDate, MonDate,
TueDate, WedDate, ThuDate, FriDate, SatDate )
SELECT E.Engineer, E.JobId, E.JobName, W.WkNo, [Year2003], ([Year2003]+1),
([Year2003]+2), ([Year2003]+3), ([Year2003]+4), ([Year2003]+5),
([Year2003]+6)
FROM EngJobs AS E, WeekNos AS W
WHERE Engineer=txtEng And WkNo=txtWkNo;

Each record is for 1 week of each project chosen and looks up the relevant
dates from a table for the chosen week number.
What I am looking for is the code I would use in an ASP form (2 text boxes -
'txtEng' and 'txtWkNo' with a submit button) to get the query to run from
the browser.

Thanks

JL

 

[Andy Cole]

Hi JL

Are we talking standard HTML/ASP rather than Access ADP here? If so, then
you need some server-side routine to run your insert query.. Exactly how
this is done will depend on the Method and Action you've specified for your
HTLM Form. The query *cannot* be run directly from the client side. Might
I recommend one of the "Programmer to Programmer" books published by Wrox
press "Active Server Pages 3.0", Homer et al and Osborne's "The Complete
Reference HTML", T A Powell

HTH

Andy

Hi,

Thanks for the interest.

Here is the code I am using for the insert query:

INSERT INTO TimeSheets ( Eng, JobID, JobName, WeekNo, SunDate, MonDate,
TueDate, WedDate, ThuDate, FriDate, SatDate )
SELECT E.Engineer, E.JobId, E.JobName, W.WkNo, [Year2003], ([Year2003]+1),
([Year2003]+2), ([Year2003]+3), ([Year2003]+4), ([Year2003]+5),
([Year2003]+6)
FROM EngJobs AS E, WeekNos AS W
WHERE Engineer=txtEng And WkNo=txtWkNo;

Each record is for 1 week of each project chosen and looks up the relevant
dates from a table for the chosen week number.
What I am looking for is the code I would use in an ASP form (2 text boxes -
'txtEng' and 'txtWkNo' with a submit button) to get the query to run from
the browser.

Thanks

JL

JL,
Post the code you have tried
inserted two
text

 

[Clive Moss]

Hi,

I have an Access database with a query that selects a set of records from
one table (work categories) and inserts them (multiple rows) in to a
timesheet table, along with the employee's name and selected week number
(both of which are variables inputted by the user).
i.e. each record from the employee's chosen work categories gets inserted in
to a new row in the timesheet table, with their name and chosen week number
inserted (these are the same for each row in this recordset instance).

I want to execute this query from an intranet based ASP file, using two text
boxes and a submit button, but can't get it to work.

The query works great from within Access (by double clicking), but I am
having problems defining the code I need to pass the parameters and execute
the query from the ASP file.

Has anyone got any ideas on what I can use here?

Many thanks

JL

The semi colon at the end of the statement could cause you some grief

cm