|
 |
asp_databases thread: RE: Problem with design
Message #1 by "Elizabeta Siljanovski" <elizabetas@m...> on Sun, 9 Dec 2001 20:45:03 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0003_01C180F2.64452AA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Hi,
I have a problem trying to set the design for the application.
I have the following tables (couple more but these are the basic values
that I need):
Equipment(equipment id, equipment name, room)
part (part id, equipment id, part status)
for the user I have to bring the following:
all DISTINCT rooms
all the equipment in some room
status of equiment as "OK" ( if all parts status is ok) or button
"details" (if any part status not ok)
detail would bring them to all the parts that are "Status bad" for that
equipment
I'm not sure how to get started here.
Any help appreciated!
Thanks!
Message #2 by "Dallas Martin" <dmartin@z...> on Sun, 9 Dec 2001 22:00:50 -0500
|
|
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
$subst('Email.Unsub').
>
Message #3 by "Elizabeta Siljanovski" <elizabetas@m...> on Sun, 9 Dec 2001 23:54:38 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0001_01C1810C.DE784860
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
I don't know if this ever got sent...
Sory for the spam if it did!
I've been thinking to first let them select the room they want to view,
bring up the values and status of only those equipment/items that have a
problem, If recordset empty, just respond room ok.
But I'm having the problem of passing recordset values from page to page
and adding it as a filter to a query. I'm trying to do this:
Calling.asp:
strSQL = "SELECT distinct Equipment.Room_num FROM Equipment;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
'Response.Write RecToTable(rsItem)
Response.Write "<form action=""ViewDetails.asp"" method=""post""
id=""Form"" name=""Form"">" Response.Write "<select name=""myCombo"">"
do until rsItem.EOF
Response.Write "<option value="""
Response.write rsItem("Room_num") & """>"
Response.Write rsItem("Room_num")
Response.Write "</option>"
rsItem.MoveNext
Loop
Response.Write "</select>"
Response.Write "<INPUT type=""submit"" value=""RoomDetails"">"
Response.Write "</form>" rsItem.Close Set rsItem = Nothing %> </BODY>
receiving.asp:
</center>
<%
dim rsItem, Room
Room = Request.Form("Room_num")
set rsItem = Server.CreateObject("ADODB.Recordset")
strSQL="SELECT Part.Equip_num, Equip_type.EType_name, Part_type.Name,
Part.Status " & _ "FROM Part_type INNER JOIN ((Equip_type INNER JOIN
Equipment ON Equip_type.EType_num = Equipment.EType_num) INNER JOIN Part
ON Equipment.Equip_num = Part.Equip_num) ON Part_type.PType_id
Part.PType_id" & _
"WHERE (((Equipment.Room_num)= " & Room & " ))" & _
"ORDER BY Equip_type.EType_name;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
Response.Write RecToTable(rsItem)
rsItem.Close
Set rsItem = Nothing
%>
<P> </P>
</BODY>
but it's not working. Any better way of doing this?
Thanks!
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Sunday, December 09, 2001 10:01 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are
ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment
id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE
part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic
values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for
that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
Message #4 by "Elizabeta Siljanovski" <elizabetas@m...> on Sun, 9 Dec 2001 23:00:12 -0500
|
|
I've been thinking to first let them select the room they want to view,
Bring up the values and status of only those equipment/items that have a
problem,
If recordset empty, just respond room ok.
But I'm having the problem of passing recordset values from page to page
and adding it as a filter to a query.
I'm trying to do this:
Calling.asp:
strSQL = "SELECT distinct Equipment.Room_num FROM Equipment;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
'Response.Write RecToTable(rsItem)
Response.Write "<form action=""ViewDetails.asp"" method=""post""
id=""Form"" name=""Form"">"
Response.Write "<select name=""myCombo"">"
do until rsItem.EOF
Response.Write "<option value="""
Response.write rsItem("Room_num") & """>"
Response.Write rsItem("Room_num")
Response.Write "</option>"
rsItem.MoveNext
Loop
Response.Write "</select>"
Response.Write "<INPUT type=""submit"" value=""RoomDetails"">"
Response.Write "</form>"
rsItem.Close
Set rsItem = Nothing
%>
</BODY>
receiving.asp:
</center>
<%
dim rsItem, Room
Room = Request.Form("Room_num")
set rsItem = Server.CreateObject("ADODB.Recordset")
strSQL="SELECT Part.Equip_num, Equip_type.EType_name, Part_type.Name,
Part.Status " & _
"FROM Part_type INNER JOIN ((Equip_type INNER JOIN Equipment ON
Equip_type.EType_num = Equipment.EType_num) INNER JOIN Part ON
Equipment.Equip_num = Part.Equip_num) ON Part_type.PType_id
Part.PType_id " & _
"WHERE (((Equipment.Room_num)= " & Room & " ))" & _
"ORDER BY Equip_type.EType_name;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
Response.Write RecToTable(rsItem)
rsItem.Close
Set rsItem = Nothing
%>
<P> </P>
</BODY>
but it's not working. Any better way of doing this?
Thanks!
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Sunday, December 09, 2001 10:01 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are
ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment
id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE
part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic
values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for
that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
Message #5 by David Cameron <dcameron@i...> on Tue, 11 Dec 2001 09:05:34 +1100
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C181C6.CACF6342
Content-Type: text/plain
From your code that will never work. You are not getting the value of
MyCombo anywhere is the second asp page. Is it held in one the variables?
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Elizabeta Siljanovski [mailto:elizabetas@m...]
Sent: Monday, 10 December 2001 2:00 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I've been thinking to first let them select the room they want to view,
Bring up the values and status of only those equipment/items that have a
problem,
If recordset empty, just respond room ok.
But I'm having the problem of passing recordset values from page to page
and adding it as a filter to a query.
I'm trying to do this:
Calling.asp:
strSQL = "SELECT distinct Equipment.Room_num FROM Equipment;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
'Response.Write RecToTable(rsItem)
Response.Write "<form action=""ViewDetails.asp"" method=""post""
id=""Form"" name=""Form"">"
Response.Write "<select name=""myCombo"">"
do until rsItem.EOF
Response.Write "<option value="""
Response.write rsItem("Room_num") & """>"
Response.Write rsItem("Room_num")
Response.Write "</option>"
rsItem.MoveNext
Loop
Response.Write "</select>"
Response.Write "<INPUT type=""submit"" value=""RoomDetails"">"
Response.Write "</form>"
rsItem.Close
Set rsItem = Nothing
%>
</BODY>
receiving.asp:
</center>
<%
dim rsItem, Room
Room = Request.Form("Room_num")
set rsItem = Server.CreateObject("ADODB.Recordset")
strSQL="SELECT Part.Equip_num, Equip_type.EType_name, Part_type.Name,
Part.Status " & _
"FROM Part_type INNER JOIN ((Equip_type INNER JOIN Equipment ON
Equip_type.EType_num = Equipment.EType_num) INNER JOIN Part ON
Equipment.Equip_num = Part.Equip_num) ON Part_type.PType_id
Part.PType_id " & _
"WHERE (((Equipment.Room_num)= " & Room & " ))" & _
"ORDER BY Equip_type.EType_name;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
Response.Write RecToTable(rsItem)
rsItem.Close
Set rsItem = Nothing
%>
<P> </P>
</BODY>
but it's not working. Any better way of doing this?
Thanks!
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Sunday, December 09, 2001 10:01 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are
ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment
id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE
part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic
values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for
that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
$subst('Email.Unsub').
Message #6 by "Elizabeta Siljanovski" <elizabetas@m...> on Tue, 11 Dec 2001 12:13:21 -0500
|
|
Yes, but how do you loop through a recordset like that?
The display is not the only requirement here. Users also need to be able
to change the values in the database and I need to save all the entries
and fire an e-mail to administration as well as create a log in a
database. So again, bunch of values have to be saved page to page, and
new select/execute statements are issued to the database.
I assume the best way would be to save it into an array but this is a
fairly complicated join and I could not find any good example of filling
in such an array from a recordset let alone conditionally displaying
data and passing it to another page in a form???. Yes, I know, there is
GetRows, but the examples of using GetRows that I've seen are just not
enough for this.
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Sunday, December 09, 2001 10:01 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are
ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment
id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE
part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic
values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for
that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
Message #7 by "Elizabeta Siljanovski" <elizabetas@m...> on Tue, 11 Dec 2001 14:14:22 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0003_01C1824E.24F795B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
David, thanks for the notice.
Elizabeta
-----Original Message-----
From: David Cameron [mailto:dcameron@i...]
Sent: Monday, December 10, 2001 5:06 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
From your code that will never work. You are not getting the value of
MyCombo anywhere is the second asp page. Is it held in one the
variables?
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Elizabeta Siljanovski [mailto:elizabetas@m...]
Sent: Monday, 10 December 2001 2:00 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I've been thinking to first let them select the room they want to view,
Bring up the values and status of only those equipment/items that have a
problem,
If recordset empty, just respond room ok.
But I'm having the problem of passing recordset values from page to page
and adding it as a filter to a query.
I'm trying to do this:
Calling.asp:
strSQL = "SELECT distinct Equipment.Room_num FROM Equipment;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
'Response.Write RecToTable(rsItem)
Response.Write "<form action=""ViewDetails.asp"" method=""post""
id=""Form"" name=""Form"">"
Response.Write "<select name=""myCombo"">"
do until rsItem.EOF
Response.Write "<option value="""
Response.write rsItem("Room_num") & """>"
Response.Write rsItem("Room_num")
Response.Write "</option>"
rsItem.MoveNext
Loop
Response.Write "</select>"
Response.Write "<INPUT type=""submit"" value=""RoomDetails"">"
Response.Write "</form>"
rsItem.Close
Set rsItem = Nothing
%>
</BODY>
receiving.asp:
</center>
<%
dim rsItem, Room
Room = Request.Form("Room_num")
set rsItem = Server.CreateObject("ADODB.Recordset")
strSQL="SELECT Part.Equip_num, Equip_type.EType_name, Part_type.Name,
Part.Status " & _
"FROM Part_type INNER JOIN ((Equip_type INNER JOIN Equipment ON
Equip_type.EType_num = Equipment.EType_num) INNER JOIN Part ON
Equipment.Equip_num = Part.Equip_num) ON Part_type.PType_id =
Part.PType_id " & _
"WHERE (((Equipment.Room_num)= " & Room & " ))" & _
"ORDER BY Equip_type.EType_name;"
rsItem.Open strSQL, HomeConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText
Response.Write RecToTable(rsItem)
rsItem.Close
Set rsItem = Nothing
%>
<P> </P>
</BODY>
but it's not working. Any better way of doing this?
Thanks!
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Sunday, December 09, 2001 10:01 PM
To: ASP Databases
Subject: [asp_databases] RE: Problem with design
I am assuming you are issuing the SELECT statement directly from an ASP
page?
> all DISTINCT rooms
"SELECT distinct room FROM equipment ORDER BY room"
> all the equipment in some room
"SELECT [equipment id],[equipment name] FROM equipment WHERE room = '" &
room & "'"
>status of equipment as "OK" ( if all parts status is ok)
This SELECT statement is for ANY parent equipment id whose children are
ALL
good. If ANY child is bad,
then that parent is not to be included in the resultant recordset.
"SELECT [equipment id],[equipment name] FROM equipment WHERE NOT EXIST
(SELECT * FROM parts WHERE part.[equipment id] = equipment.[equipment
id]
AND part.[part status] = 'bad')
>detail would bring them to all the parts that are "Status bad" for that
equipment
"SELECT [equipment id],[part id], [part status] FROM part INNER JOIN
equipment ON equipment.[equipment id] = part.[equipment id] WHERE
part.[part
status] = 'bad'" AND part.[equipment id] = " & equipment_id
Dallas
----- Original Message -----
From: "Elizabeta Siljanovski" <elizabetas@m...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, December 09, 2001 8:45 PM
Subject: [asp_databases] RE: Problem with design
>
> Hi,
> I have a problem trying to set the design for the application.
> I have the following tables (couple more but these are the basic
values
> that I need):
>
> Equipment(equipment id, equipment name, room)
> part (part id, equipment id, part status)
>
> for the user I have to bring the following:
>
> all DISTINCT rooms
> all the equipment in some room
> status of equiment as "OK" ( if all parts status is ok) or button
> "details" (if any part status not ok)
> detail would bring them to all the parts that are "Status bad" for
that
> equipment
>
> I'm not sure how to get started here.
>
> Any help appreciated!
>
> Thanks!
>
>
> ---
$subst('Email.Unsub').
>
---
$subst('Email.Unsub').
---
$subst('Email.Unsub').
$subst('Email.Unsub').
|
|
|
View Cart
