Passing parameters in form SQL 2000 DB

[Cheryl]

I need to know how to pass parameters from several drop down lists on a form
to be able to search for particular records. We have frontpage 2003 and
connecting to SQL 2000 on Windows Small Business Server.

Thanks in advance

 

[Jens Peter Karlsen[FP MVP]]

When you submit the page, you use ASP to get the values from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery
option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"& option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

 

[Cheryl]

um... Where in the drop down does option1=Form(fieldname).value go? The sql
statement does not like the '"& option1 &"' statement.. I get the idea..
just not quite sure where some of it goes.... This a sample of the one of the
select statements:


<Select NAME="FullName" SIZE="1">

<% do while not objRecordset.EOF %>
<% If objRecordset("Status")="C" then %>
<option><%=objRecordset("FullName") %></option>
<%
End If
objRecordset.MoveNext
loop

objRecordset.Close
Set objRecordset=Nothing
objConn.Close
Set objConn=Nothing
%>
</Select>

When you submit the page, you use ASP to get the values from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery
option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"& option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 24. november 2004 15:49
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Passing parameters in form SQL 2000 DB


I need to know how to pass parameters from several drop down
lists on a form to be able to search for particular records.
We have frontpage 2003 and connecting to SQL 2000 on Windows
Small Business Server.

Thanks in advance

 

[Cheryl]

Ok... I was able to pass the parameter over.. my next problem is:

Can I use: SELECT DatePart(yyyy, DateWorked) as YearPart

which is part of the sql to compare with the variable sent from the dropdown?

YearPart = '"&VarYear&"' ) ..

The date in the SQL database is stored as one field but I needed to break
apart the date to search for information by Name, Year and Week Number so
that only the selected items show up...


strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart, DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as WeekNumberPart, *
FROM Employees, TimeSheets where (FullName = '"&VarName&"' and YearPart =
'"&VarYear&"' ) .....

When you submit the page, you use ASP to get the values from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery
option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"& option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 24. november 2004 15:49
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Passing parameters in form SQL 2000 DB


I need to know how to pass parameters from several drop down
lists on a form to be able to search for particular records.
We have frontpage 2003 and connecting to SQL 2000 on Windows
Small Business Server.

Thanks in advance

 

[Jens Peter Karlsen[FP MVP]]

Have a look at this page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref
/ts_da-db_2mic.asp

It describes datepart and gives examples.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 25. november 2004 14:07
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Re: Passing parameters in form SQL 2000 DB


Ok... I was able to pass the parameter over.. my next problem is:

Can I use: SELECT DatePart(yyyy, DateWorked) as YearPart

which is part of the sql to compare with the variable sent
from the dropdown?

YearPart = '"&VarYear&"' ) ..

The date in the SQL database is stored as one field but I
needed to break apart the date to search for information by
Name, Year and Week Number so that only the selected items show up...


strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart,
DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as
DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as
WeekNumberPart, * FROM Employees, TimeSheets where (FullName
= '"&VarName&"' and YearPart = '"&VarYear&"' ) .....

When you submit the page, you use ASP to get the values from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"& option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 24. november 2004 15:49
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Passing parameters in form SQL 2000 DB


I need to know how to pass parameters from several drop down lists
on a form to be able to search for particular records.
We have frontpage 2003 and connecting to SQL 2000 on Windows Small
Business Server.

Thanks in advance

 

[Cheryl]

Thank you for that information... However, my problem is not how to break
apart a date any longer.. because I can do that.. finally.... but that I want
to know how to use a date to compare to my passed parameters where if they
are equal.. For example.. I have a drop down that consists of employee name,
year and week number. The year and week number were obtained using datepart
since the date is store one field.

can again break apart the date in the sql for the connection. The sql
statement either needs to retrieve only the records that I require based on
the passed parameters now stored in variables. What I wanted to know is that
in the SQL statement where I do break apart the date... and can retrieve
based on the name

Statement below:

strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart, DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as WeekNumberPart, *
FROM Employees, TimeSheets where (FullName = '"&VarName&"')

Can I use "YearPart" as part of the where statement in the same SQL ie.

SELECT DatePart(yyyy, DateWorked) as YearPart .....


where (FullName = '"&VarName&"' and YearPart='"&VarYear&"' ) ... etc

I also tried to Just compare the fields based on the YearPart and varYear as
well instead of part of the SQL but it doesn't find a match... But if I use
response.write varYear and YearPart.. they both contain matching data so it
should find it.

strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart, DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as WeekNumberPart, *
FROM Employees, TimeSheets where (FullName = '"&VarName&"') ORDER BY
DateWorked ASC, JobNumberID ASC, PartNumber ASC, Other ASC, FullName ASC"

objRecordset4.Open strSQL4, objConn4
objRecordset4.movefirst %>

<% do while not objRecordset4.EOF %>
<% if objRecordset4("YearPart") = varYear then %>

So I can't make either way work... It extracts the all of the data for the
employee if I don't try and compare... but finds nothing if I try to compare
YearPart and varYear

Have a look at this page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref
/ts_da-db_2mic.asp

It describes datepart and gives examples.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 25. november 2004 14:07
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Re: Passing parameters in form SQL 2000 DB


Ok... I was able to pass the parameter over.. my next problem is:

Can I use: SELECT DatePart(yyyy, DateWorked) as YearPart

which is part of the sql to compare with the variable sent
from the dropdown?

YearPart = '"&VarYear&"' ) ..

The date in the SQL database is stored as one field but I
needed to break apart the date to search for information by
Name, Year and Week Number so that only the selected items show up...


strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart,
DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as
DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as
WeekNumberPart, * FROM Employees, TimeSheets where (FullName
= '"&VarName&"' and YearPart = '"&VarYear&"' ) .....

When you submit the page, you use ASP to get the values from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"& option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 24. november 2004 15:49
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Passing parameters in form SQL 2000 DB


I need to know how to pass parameters from several drop down lists
on a form to be able to search for particular records.
We have frontpage 2003 and connecting to SQL 2000 on Windows Small
Business Server.

Thanks in advance

 

[Jens Peter Karlsen[FP MVP]]

Your problem probably is that you compare strings with integers. That
wont match but will look the same when you write it out. Make sure all
is integer by using the CInt function.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 26. november 2004 12:49
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Re: Passing parameters in form SQL 2000 DB


Thank you for that information... However, my problem is not
how to break apart a date any longer.. because I can do
that.. finally.... but that I want to know how to use a date
to compare to my passed parameters where if they are equal..
For example.. I have a drop down that consists of employee
name, year and week number. The year and week number were
obtained using datepart since the date is store one field.

can again break apart the date in the sql for the
connection. The sql statement either needs to retrieve only
the records that I require based on the passed parameters now
stored in variables. What I wanted to know is that in the SQL
statement where I do break apart the date... and can retrieve
based on the name

Statement below:

strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart,
DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as
DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as
WeekNumberPart, * FROM Employees, TimeSheets where (FullName
= '"&VarName&"')

Can I use "YearPart" as part of the where statement in the
same SQL ie.

SELECT DatePart(yyyy, DateWorked) as YearPart .....


where (FullName = '"&VarName&"' and YearPart='"&VarYear&"' ) ... etc

I also tried to Just compare the fields based on the YearPart
and varYear as well instead of part of the SQL but it doesn't
find a match... But if I use response.write varYear and
YearPart.. they both contain matching data so it should find it.

strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart,
DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as
DayPart, DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as
WeekNumberPart, * FROM Employees, TimeSheets where (FullName
= '"&VarName&"') ORDER BY DateWorked ASC, JobNumberID ASC,
PartNumber ASC, Other ASC, FullName ASC"

objRecordset4.Open strSQL4, objConn4
objRecordset4.movefirst %>

<% do while not objRecordset4.EOF %>
<% if objRecordset4("YearPart") =
varYear then %>

So I can't make either way work... It extracts the all of the
data for the employee if I don't try and compare... but finds
nothing if I try to compare YearPart and varYear

Have a look at this page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
ef
/ts_da-db_2mic.asp

It describes datepart and gives examples.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 25. november 2004 14:07
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Re: Passing parameters in form SQL 2000 DB


Ok... I was able to pass the parameter over.. my next problem is:

Can I use: SELECT DatePart(yyyy, DateWorked) as YearPart

which is part of the sql to compare with the variable sent from the
dropdown?

YearPart = '"&VarYear&"' ) ..

The date in the SQL database is stored as one field but I needed to
break apart the date to search for information by Name, Year and
Week Number so that only the selected items show up...


strSQL4 = "SELECT DatePart(yyyy, DateWorked) as YearPart,
DatePart(month,
DateWorked) as MonthPart, DatePart(dd, DateWorked) as DayPart,
DatePart(dw,
DateWorked) as WeekDayPart, DatePart(ww, DateWorked) as
WeekNumberPart, * FROM Employees, TimeSheets where (FullName =
'"&VarName&"' and YearPart = '"&VarYear&"' ) .....

:

When you submit the page, you use ASP to get the values
from the Form
collection and store it in variables that you use to form the Query.
Something like:
Dim option1, option2, option3, strQuery
option1=Form(fieldname).value
....
....
and your query
strQuery="SELECT * from someTable WHERE tablefield='"&
option1 &"' AND
.....

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Cheryl [mailto:[email protected]]
Posted At: 24. november 2004 15:49 Posted To:
microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: Passing parameters in form SQL 2000 DB


I need to know how to pass parameters from several drop
down lists
on a form to be able to search for particular records.
We have frontpage 2003 and connecting to SQL 2000 on
Windows Small
Business Server.

Thanks in advance

 

[Tom]

Wow, their suggestions are a little overkill... Here, this works a little
better.

Page One:

Form with your options for query. Remember, you could query the options from
your database as well...

This form has Dropdown1, dropdown2, etc... and the form's action is set to
the second asp page.

The second asp page

Start the database results wizard, select Custom query.

select * from databasename
where field1=':ropdown1::' and field2=':ropdown2

That should get you started

 

[Jens Peter Karlsen[FP MVP]]

It seems that you skipped the message where she writes "Ok... I was able
to pass the parameter over.. my next problem is:"
It is that next question we are now working on. If you have input on
that, you are welcome to write suggestions on how to solve it.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Tom [mailto:[email protected]]
Posted At: 28. november 2004 07:29
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: RE: Passing parameters in form SQL 2000 DB


Wow, their suggestions are a little overkill... Here, this
works a little better.

Page One:

Form with your options for query. Remember, you could query
the options from your database as well...

This form has Dropdown1, dropdown2, etc... and the form's
action is set to the second asp page.

The second asp page

Start the database results wizard, select Custom query.

select * from databasename
where field1=':ropdown1::' and field2=':ropdown2

That should get you started

I need to know how to pass parameters from several drop down lists on
a form to be able to search for particular records. We have frontpage
2003 and connecting to SQL 2000 on Windows Small Business Server.

Thanks in advance

 

[Cheryl]

Ty all... I figured out late Friday that your recommendation was exactly my
problem.. and CINT was exactly what I used... Today is the first I have been
back online...

I did not know that I could use the database results wizard for an SQL
database. Otherwise I would have done it that way.. since I know how to do it
using the wizard.. I was coding the 'long' way...

Thanks again all... You all are such a great help when I get stuck... and
can't find the answers which I try to search for first before having to
bother on the forums..

It seems that you skipped the message where she writes "Ok... I was able
to pass the parameter over.. my next problem is:"
It is that next question we are now working on. If you have input on
that, you are welcome to write suggestions on how to solve it.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Tom [mailto:[email protected]]
Posted At: 28. november 2004 07:29
Posted To: microsoft.public.frontpage.programming
Conversation: Passing parameters in form SQL 2000 DB
Subject: RE: Passing parameters in form SQL 2000 DB


Wow, their suggestions are a little overkill... Here, this
works a little better.

Page One:

Form with your options for query. Remember, you could query
the options from your database as well...

This form has Dropdown1, dropdown2, etc... and the form's
action is set to the second asp page.

The second asp page

Start the database results wizard, select Custom query.

select * from databasename
where field1=':ropdown1::' and field2=':ropdown2

That should get you started

I need to know how to pass parameters from several drop down lists on
a form to be able to search for particular records. We have frontpage
2003 and connecting to SQL 2000 on Windows Small Business Server.

Thanks in advance

 

[david]

Hi Tom,
Your response on 11/27/04 to Cheryl's posting might be the answer to my
problem as well, except I can't seem to get it to work.
I am using ACCESS and Frontpage 2003. I am not sure if this would make a
difference - ACCESS vs MS-SQL2000.
Anyway, here is what I am trying to accomplish, using DRW:
1. present to site visitor a number of dropdown boxes on page 1 (and if
possible have the dropdowns arranged in 2 or 3 columns in the browser, and if
possible add some check boxes as well);
2. use the site visitor's response from page 1 to select records from an
ACCESS database Query (or Table), and present the results to the site visitor.

Here's what I have done:
In page 1, created several dropdowns, invoking DRW anew with each dropdown
created. (so I ended up having each dropdown residing in a separate form,
and there is no submit button.)
In page 2, created a database result region with DRW, and selected "Custom
query" to enter an sql statement similar to the following:
SELECT * FROM QueryA WHERE Field1 < ::Field1:: AND Field2 <::Field2::
........

And, here is where I am stuck.
I don't know how to pass the paramenters from the dropdowns in page 1 to the
query in page 2.

In page1, the DRW would let me pass parameters to page2 outside of the
database results restion, but I can't find a way to pass them into the
database wizard in page 2.
Yet your previous email (on 11/27/04) seems to suggest that this can be done.

Any help on this would be appreciated.

David

 

[Jörgen Lundberg]

Hi David,
I'm not an expert on this but I have just made a similar task.
Look at http://spiderwebwoman.com/tutorials/doubledropdown.htm and I think
you get the idea.

My problem today is that I want to have the dropdowns and the data result on
the same page so I can se the result and easily change my criteria for a new
search. I don't think thats possible with the wizzard. Any workarounds??

Regards Jörgen

"david" skrev: