p2p.wrox.com: Archives - asp

asp_cdo thread: Sql queries

Message #1 by arshad siddiqui <ash_arshad@y...> on Mon, 19 Nov 2001 23:29:58 -0800 (PST)

Hi there,

Suppose I am searching a database for some records.For

example

sqltest="select distinct CarrierReference from RECEIPT

order by CarrierReference"

after that based on this search I have to search the

database and display the records for each

CarrierReference.

sql7="select

Lottable01,QtyReceived,UOM,B_GrossWgt,B_NetWgt,SkuGroup,Sku,DateReceived,ToLoc

from dbo_RECEIPT,dbo_RECEIPTDETAIL where

dbo_RECEIPT.ReceiptKey=dbo_RECEIPTDETAIL.ReceiptKey

and  dbo_RECEIPTDETAIL.StorerKey='"& user &"' and

QtyReceived>0  and CarrierReference='"& carr &"'"



I am using this code:

 <% set rsstest=cn.Execute (sqltest)

     do while not rsstest.eof 

     carr=rsstest(0)

     %>

     <tr><td colspan=9 bgcolor="#10337a"><font

color="white"><b><%=carr%></b></td></tr>

<% set rss7=cn.Execute (sql7)

do while not rss7.EOF %>

<tr>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("Lottable01")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("QtyReceived")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("UOM")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=Round(rss7("B_GrossWgt"),3)%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=Round(rss7("B_NetWgt"),3)%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("SkuGroup")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("Sku")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("DateReceived")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("ToLoc")%></font></td>

         

          

         

          <%

        'rowCount = rowCount + 1 

        rss7.MoveNext

  

        loop

        rsstest.MoveNext

        loop

        

        %>



But using this code only its displaying the

CarrierReference.

Please help

its urgent

Thanx

--Arshad--



__________________________________________________

Do You Yahoo!?

Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.

http://geocities.yahoo.com/ps/info1

Message #2 by IVKishore <ivkishore@p...> on Tue, 20 Nov 2001 14:10:39 +0530


When you sre trying to obtain or retrieve results from a database using data

from 2 or more tables bearing a relationship among 1 or 2 fields among them,

you do have to mention the columns you would want to retrieve along with

their alias names like a.empno , b.deptno etc.



	Eg : Select e.empno , d.deptno from emp e , dept d where

e.deptno=d.deptno



So try using the aliases for the tables in your query and write the

relationships among them accordingly. I hope this suggestion would prove

helpful and you may come back with more doubts.





Regards,

Kishore.





-----Original Message-----

From: arshad siddiqui [mailto:ash_arshad@y...]

Sent: Tuesday, November 20, 2001 1:00 PM

To: ASP CDO

Subject: [asp_cdo] Sql queries





Hi there,

Suppose I am searching a database for some records.For

example

sqltest="select distinct CarrierReference from RECEIPT

order by CarrierReference"

after that based on this search I have to search the

database and display the records for each

CarrierReference.

sql7="select

Lottable01,QtyReceived,UOM,B_GrossWgt,B_NetWgt,SkuGroup,Sku,DateReceived,ToL

oc

from dbo_RECEIPT,dbo_RECEIPTDETAIL where

dbo_RECEIPT.ReceiptKey=dbo_RECEIPTDETAIL.ReceiptKey

and  dbo_RECEIPTDETAIL.StorerKey='"& user &"' and

QtyReceived>0  and CarrierReference='"& carr &"'"



I am using this code:

 <% set rsstest=cn.Execute (sqltest)

     do while not rsstest.eof 

     carr=rsstest(0)

     %>

     <tr><td colspan=9 bgcolor="#10337a"><font

color="white"><b><%=carr%></b></td></tr>

<% set rss7=cn.Execute (sql7)

do while not rss7.EOF %>

<tr>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("Lottable01")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("QtyReceived")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("UOM")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=Round(rss7("B_GrossWgt"),3)%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=Round(rss7("B_NetWgt"),3)%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("SkuGroup")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("Sku")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("DateReceived")%></font></td>

          <td width="7%" bgcolor="white"

align="middle"><font size="1" face="Arial"

color="#373737"><%=rss7("ToLoc")%></font></td>

         

          

         

          <%

        'rowCount = rowCount + 1 

        rss7.MoveNext

  

        loop

        rsstest.MoveNext

        loop

        

        %>



But using this code only its displaying the

CarrierReference.

Please help

its urgent

Thanx

--Arshad--



__________________________________________________

Do You Yahoo!?

Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.

http://geocities.yahoo.com/ps/info1

Message #3 by arshad siddiqui <ash_arshad@y...> on Tue, 20 Nov 2001 00:43:46 -0800 (PST)

Hi

Actually if I use CarrierReference='507' instead of

CarriereReference='"& carr &"' in query sql7,its

working.but I want to display the results of sql7

based on tne result of first query.

thanx

Bye





__________________________________________________

Do You Yahoo!?

Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.

http://geocities.yahoo.com/ps/info1

Message #4 by "Martin Grundy" <martin.grundy@s...> on Tue, 20 Nov 2001 09:34:19

> Hi there,

> Suppose I am searching a database for some records.For

> example

> sqltest="select distinct CarrierReference from RECEIPT

> order by CarrierReference"

> after that based on this search I have to search the

> database and display the records for each

> CarrierReference.

> sql7="select

> 

Lottable01,QtyReceived,UOM,B_GrossWgt,B_NetWgt,SkuGroup,Sku,DateReceived,To

Loc

> from dbo_RECEIPT,dbo_RECEIPTDETAIL where

> dbo_RECEIPT.ReceiptKey=dbo_RECEIPTDETAIL.ReceiptKey

> and  dbo_RECEIPTDETAIL.StorerKey='"& user &"' and

> QtyReceived>0  and CarrierReference='"& carr &"'"

> 

> I am using this code:

>  <% set rsstest=cn.Execute (sqltest)

>      do while not rsstest.eof 

>      carr=rsstest(0)

>      %>

>      <tr><td colspan=9 bgcolor="#10337a"><font

> color="white"><b><%=carr%></b></td></tr>

> <% set rss7=cn.Execute (sql7)

> do while not rss7.EOF %>

> <tr>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("Lottable01")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("QtyReceived")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("UOM")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=Round(rss7("B_GrossWgt"),3)%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=Round(rss7("B_NetWgt"),3)%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("SkuGroup")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("Sku")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("DateReceived")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("ToLoc")%></font></td>

>          

>           

>          

>           <%

>         'rowCount = rowCount + 1 

>         rss7.MoveNext

>   

>         loop

>         rsstest.MoveNext

>         loop

>         

>         %>

> 

> But using this code only its displaying the

> CarrierReference.

> Please help

> its urgent

> Thanx

> --Arshad--

> 

> __________________________________________________

> Do You Yahoo!?

> Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.

> http://geocities.yahoo.com/ps/info1

Message #5 by "Martin Grundy" <martin.grundy@s...> on Tue, 20 Nov 2001 09:36:30

At the time you set up sql7, carr is blank and so the sql statement is 

looking for a blank carr. You need to set up sql7 every time, just before 

your second read.





> Hi there,

> Suppose I am searching a database for some records.For

> example

> sqltest="select distinct CarrierReference from RECEIPT

> order by CarrierReference"

> after that based on this search I have to search the

> database and display the records for each

> CarrierReference.

> sql7="select

> 

Lottable01,QtyReceived,UOM,B_GrossWgt,B_NetWgt,SkuGroup,Sku,DateReceived,To

Loc

> from dbo_RECEIPT,dbo_RECEIPTDETAIL where

> dbo_RECEIPT.ReceiptKey=dbo_RECEIPTDETAIL.ReceiptKey

> and  dbo_RECEIPTDETAIL.StorerKey='"& user &"' and

> QtyReceived>0  and CarrierReference='"& carr &"'"

> 

> I am using this code:

>  <% set rsstest=cn.Execute (sqltest)

>      do while not rsstest.eof 

>      carr=rsstest(0)

>      %>

>      <tr><td colspan=9 bgcolor="#10337a"><font

> color="white"><b><%=carr%></b></td></tr>

> <% set rss7=cn.Execute (sql7)

> do while not rss7.EOF %>

> <tr>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("Lottable01")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("QtyReceived")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("UOM")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=Round(rss7("B_GrossWgt"),3)%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=Round(rss7("B_NetWgt"),3)%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("SkuGroup")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("Sku")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("DateReceived")%></font></td>

>           <td width="7%" bgcolor="white"

> align="middle"><font size="1" face="Arial"

> color="#373737"><%=rss7("ToLoc")%></font></td>

>          

>           

>          

>           <%

>         'rowCount = rowCount + 1 

>         rss7.MoveNext

>   

>         loop

>         rsstest.MoveNext

>         loop

>         

>         %>

> 

> But using this code only its displaying the

> CarrierReference.

> Please help

> its urgent

> Thanx

> --Arshad--

> 

> __________________________________________________

> Do You Yahoo!?

> Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.

> http://geocities.yahoo.com/ps/info1

Message #6 by "Martin Grundy" <martin.grundy@s...> on Tue, 20 Nov 2001 10:09:16

Specifically:



<% sqltest="select distinct CarrierReference from RECEIPT order by 

CarrierReference" %>

<% set rsstest=cn.Execute (sqltest)

do while not rsstest.eof

	carr=rsstest(0) %> 

<tr>

	<td colspan=9 bgcolor="#10337a">

		<font color="white"><b><%=carr%></b>

	</td>

</tr> 

<%

'

' Set sql7 every time ...

'

sql7="select 

Lottable01,QtyReceived,UOM,B_GrossWgt,B_NetWgt,SkuGroup,Sku,DateReceived,To

Loc from dbo_RECEIPT,dbo_RECEIPTDETAIL where 

dbo_RECEIPT.ReceiptKey=dbo_RECEIPTDETAIL.ReceiptKey and 

dbo_RECEIPTDETAIL.StorerKey='"& user &"' and QtyReceived>0 and 

CarrierReference='"& carr &"'"

'

' Inner read loop ...

'

set rss7=cn.Execute (sql7) do while not rss7.EOF %> 

<tr>

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7

("Lottable01")%></font>

	</td>

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7

("QtyReceived")%></font>

	</td>

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7("UOM")%

></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=Round(rss7

("B_GrossWgt"),3)%></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=Round(rss7

("B_NetWgt"),3)%></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7

("SkuGroup")%></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7("Sku")%

></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7

("DateReceived")%></font>

	</td> 

	<td width="7%" bgcolor="white" align="middle">

		<font size="1" face="Arial" color="#373737"><%=rss7

("ToLoc")%></font>

	</td> 

<% 

	rowCount = rowCount + 1

	rss7.MoveNext

	loop

rsstest.MoveNext loop %>

Return to Index

P2P Archives