Just Skins Support Forums > Archives > Web Design and Development > Web Programming Languages > ASP > ASP Components > Execute Query in Access from an ASP Form?

Execute Query in Access from an ASP Form?

Posted: 07-03-2003, 04:35 PM
JL
Guest
Posts: n/a
 
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


Reply With Quote

Responses to "Execute Query in Access from an ASP Form?"

JL
Guest
Posts: n/a
 
Execute Query in Access from an ASP Form?
Posted: 07-03-2003, 04:35 PM
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


Reply With Quote
solex
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-03-2003, 05:06 PM
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
>
>

Reply With Quote
solex
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-03-2003, 05:06 PM
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
>
>

Reply With Quote
Bob Barrows
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-03-2003, 05:16 PM
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

Reply With Quote
Bob Barrows
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-03-2003, 05:16 PM
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

Reply With Quote
JL
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-04-2003, 04:11 AM
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
> >
> >
>
>

Reply With Quote
JL
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-04-2003, 04:11 AM
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
> >
> >
>
>

Reply With Quote
Andy Cole
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-04-2003, 11:50 AM
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
> > >
> > >
> >
> >
>
>

Reply With Quote
Andy Cole
Guest
Posts: n/a
 
Re: Execute Query in Access from an ASP Form?
Posted: 07-04-2003, 11:50 AM
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
> > >
> > >
> >
> >
>
>

Reply With Quote
 
Thread Tools Search this Thread Display Modes
Reply
Page 1 of 2 1 2 >

« Previous Thread | Next Thread »

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query problem navee Coldfusion Database Access 12 03-11-2005 01:25 AM
Query of Queries? artists_envy Coldfusion Database Access 3 02-25-2005 04:31 PM
query problem tejun Coldfusion Database Access 1 02-21-2005 10:36 PM
Viewing/Save Access Query Objects from CF mpalsf Coldfusion Database Access 2 02-16-2005 05:48 PM
Use PDf fill in form in Website and submitting form info. Alexandre_L_Eveille@adobeforums.com Adobe Acrobat Macintosh 0 08-03-2004 10:26 PM