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

[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
"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> 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
"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> 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
>
>

[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 wrote:

> 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

[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 wrote:

> 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

[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




"solex" <solex@nowhere.com> wrote in message
news:u0Smi0XQDHA.1712@TK2MSFTNGP12.phx.gbl...

> JL,
> Post the code you have tried
> "JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
> news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> > 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
> >
> >

>
>

[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




"solex" <solex@nowhere.com> wrote in message
news:u0Smi0XQDHA.1712@TK2MSFTNGP12.phx.gbl...

> JL,
> Post the code you have tried
> "JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
> news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> > 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
> >
> >

>
>

[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

"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:Pe6Na.1115$PK2.772@newsfep1-gui.server.ntli.net...

> 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
>
>
>
>
> "solex" <solex@nowhere.com> wrote in message
> news:u0Smi0XQDHA.1712@TK2MSFTNGP12.phx.gbl...

> > JL,
> > Post the code you have tried
> > "JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
> > news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> > > 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
> > >
> > >

> >
> >

>
>

[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

"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:Pe6Na.1115$PK2.772@newsfep1-gui.server.ntli.net...

> 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
>
>
>
>
> "solex" <solex@nowhere.com> wrote in message
> news:u0Smi0XQDHA.1712@TK2MSFTNGP12.phx.gbl...

> > JL,
> > Post the code you have tried
> > "JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
> > news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> > > 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
> > >
> > >

> >
> >

>
>

[Clive Moss]

"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> 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

[Clive Moss]

"JL" <design4online@REMOVETHISBIThotmail.com> wrote in message
news:l3YMa.503$PK2.151@newsfep1-gui.server.ntli.net...

> 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