|
 |
asp_databases thread: sql
Message #1 by arshad siddiqui <ash_arshad@y...> on Sun, 9 Dec 2001 23:54:24 -0800 (PST)
|
|
--- Elizabeta Siljanovski <elizabetas@m...>
wrote:
> 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').
>
>
> $subst('Email.Unsub').
>
__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com
Message #2 by arshad siddiqui <ash_arshad@y...> on Sun, 9 Dec 2001 23:59:15 -0800 (PST)
|
|
Hi
I have aproblem in SQL
I have five columns ina table namely
img_id,img_type,img_name,img_size,img_root and
img_cat.
first is auto number and the rest are text.
I want to select only those values that are unique i.e
I don't want to count duplicate values.
select * from images where img_name IS UNIQUE and
img_type='siemens' and img_cat not like '%ss'
The error is:
Microsoft OLE DB Provider for ODBC Drivers error
'80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid use
of Is operator in query expression 'img_name IS UNIQUE
and img_type='siemens' and img_cat not like '%ss''.
/citymobilenew/1024x768/siemens.asp, line 22
Any clue where I am wrong
Please help
Thanx
--Arshad--
__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com
Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Mon, 10 Dec 2001 17:59:21 +0100
|
|
apparently IS UNIQUE isn't valid.
SELECT DISTINCT img_name FROM images WHERE img_type = 'siemens' AND img_cat
NOT LIKE '%ss'
-Kim
-----Original Message-----
From: arshad siddiqui [mailto:ash_arshad@y...]
Sent: 10. december 2001 08:59
To: ASP Databases
Subject: [asp_databases] sql
Hi
I have aproblem in SQL
I have five columns ina table namely
img_id,img_type,img_name,img_size,img_root and
img_cat.
first is auto number and the rest are text.
I want to select only those values that are unique i.e
I don't want to count duplicate values.
select * from images where img_name IS UNIQUE and
img_type='siemens' and img_cat not like '%ss'
The error is:
Microsoft OLE DB Provider for ODBC Drivers error
'80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid use
of Is operator in query expression 'img_name IS UNIQUE
and img_type='siemens' and img_cat not like '%ss''.
/citymobilenew/1024x768/siemens.asp, line 22
Any clue where I am wrong
Please help
Thanx
--Arshad--
__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com
$subst('Email.Unsub').
Message #4 by "Philip Sayers" <philipsayers@m...> on Mon, 10 Dec 2001 17:51:24 -0500
|
|
I think the keyword you are looking for is DISTINCT rather than UNIQUE
SELECT DISTINT FROM......
Check the syntax in the access help file
-----Original Message-----
From: arshad siddiqui [mailto:ash_arshad@y...]
Sent: Monday, December 10, 2001 2:59 AM
To: ASP Databases
Subject: [asp_databases] sql
Hi
I have aproblem in SQL
I have five columns ina table namely
img_id,img_type,img_name,img_size,img_root and
img_cat.
first is auto number and the rest are text.
I want to select only those values that are unique i.e
I don't want to count duplicate values.
select * from images where img_name IS UNIQUE and
img_type='siemens' and img_cat not like '%ss'
The error is:
Microsoft OLE DB Provider for ODBC Drivers error
'80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid use
of Is operator in query expression 'img_name IS UNIQUE
and img_type='siemens' and img_cat not like '%ss''.
/citymobilenew/1024x768/siemens.asp, line 22
Any clue where I am wrong
Please help
Thanx
--Arshad--
__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com
$subst('Email.Unsub').
|
|
|
View Cart
