p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

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>&nbsp;</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').






  Return to Index