...

View Full Version : To have two Sql in one asp page



cs168
08-26-2005, 03:32 PM
Now I have 2 sql in two different asp page can I have 2 sql in one asp page?
For example :
this first sql:-
sql="select min1, max1, from tblFiles where Pkg Like '%P%' and week between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by week DESC"
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkg%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(min1,2)%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(max1,2)%></font></Td>



and my another sql:-
sql="select min1, max1, from tblFiles where Pkg Like '%G%' and week between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by week DESC"
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkg%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(min1,2)%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(max1,2)%></font></Td>


is there a way to have a table in one page showing the two sql result? pls help

jaywhy13
08-26-2005, 03:35 PM
If by "having sql" you mean passing on a sql statement to a recordset and retrieving the results from it. Then the answer to your question is yes! You just need to set your recordsets and open then with the connection obj

cs168
08-26-2005, 03:44 PM
Tks for help, yes what you mentioned is what I want, can you pls be more specific and in details on how to do. My code
<%
SET RS=CreateObject("ADODB.Recordset")
sql="select min1, max1, from tblFiles where Pkg Like '%P%' and week between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by week DESC"
RS.Open sql,conn,1,2%>
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkg%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(min1,2)%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(max1,2)%></font></Td>



and how can I continue

SpirtOfGrandeur
08-26-2005, 03:50 PM
RS.Close
RS.Open ... (Rest of the junk here)

jaywhy13
08-26-2005, 03:53 PM
Tks for help, yes what you mentioned is what I want, can you pls be more specific and in details on how to do. My code
<%
SET RS=CreateObject("ADODB.Recordset")
sql="select min1, max1, from tblFiles where Pkg Like '%P%' and week between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by week DESC"
RS.Open sql,conn,1,2%>
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkg%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(min1,2)%></font></Td>

<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(max1,2)%></font></Td>

and how can I continue

Remember to wrap your code with CODE tags.

Uhh... to continue..
Just name your second recordset and move on...

SET anotherRS=CreateObject("ADODB.Recordset")
anotherRS.Open sql2, ConnObj, blah blah...
To access data in the recordset... I think the general format is..
recordSet("fieldName")
So to access min1 you'd use... rs("min1")

I don't quite remember coz I rarely use that method. I use getRows instead.
See this link. (http://www.learnasp.com/advice/whygetrows.asp)
Getrows copies the data in the recordset into a multidimensional array... then you can close the recordset relieving the server. And you access the data in the array based on indices.

A word of caution my friend... Each database operation creates load on the server. So if you can lessen your calls the better.
So instead of opening two recordsets.. try and see if there is a way that you can link your tables or something of the sort to get the information.

cs168
08-26-2005, 04:08 PM
Tks a lot to jaywhy13 and SpirtOfGrandeur. I am more clear and understand how can I do that.
I have anothers question, I do have some calculation and constant value in one of my asp page and it'd as below:-

count = count + 1
trange1= trange1 + range1
Range1ave= trange1/ count
Rucl = 1.652 * range1ave
Rlcl = 0.348 * range1ave'
sigma = range1ave / 3.472
sixsigma = 6 * sigma
specmean = 800 - 300 ' USl - LSL
cp= specmean / sixsigma ' get the process cp

and my question is when I first load the page it happen to give me value:-
0.00
0.00
0.00
and it will seperate my table in a blank row after I click on post?
How can I get away from this? Tks

neocool00
08-26-2005, 06:04 PM
Which calcuations are being displayed as 0.00? Where are you initially storing a value for those variables?

cs168
08-27-2005, 10:13 AM
Which calcuations are being displayed as 0.00? Where are you initially storing a value for those variables?
On my first load of the page, All my formula are givn me 0.00 value in the page, afer an action post all the value disappear & seperate my table in a few empty row.My full code:-


table width="100%" align="center" bgcolor="#0d5692">
<tr>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>PKG Type</b></font></td>
<td width="5%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Mean</b></font></td>
<td width="5%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Average<br>Moving Range</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>UCL</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>LCL</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Total<br>Record</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Range<br>Mean</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Range<br>UCL</b></font></td>
<td width="7%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>Range<br>LCL</b></font></td>
<td width="5%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>CPK</b></font></td>
<td width="5%" align="center" bgcolor="#ffffff"><font face="verdana" size="2"><b>CP</b></font></td>
</tr>

<%
SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where pkgtype Not Like '%G%' And workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
Pkgid= "All Packages"
RS.Open sql,conn,1,2
prioravg1 = -1
tavg1=0
tdiffavg1=0
trange1=0
count=0%>
<%
Do until rs.eof
avg1 = RS("avg1")
range1= rs("range1")
diffavg1 = 0
If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1

count = count + 1
trange1= trange1 + range1
Range1ave= trange1/ count ' Get the range Average
Rucl = 1.652 * range1ave ' get the range chart UCL
Rlcl = 0.348 * range1ave' get the range chart LCL
sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472
sixsigma = 6 * sigma
specmean = 800 - 300 ' USl - LSL
cp= specmean / sixsigma ' get the process cp


tavg1 = tavg1 + avg1
tdiffavg1 = tdiffavg1 + diffavg1
atavg1= tavg1 / count ' get the average of avg1
if rs.recordcount > 1 then Rcount = rs.recordcount - 1
if rs.recordcount = 1 then Rcount = 1
atdiffavg1= tdiffavg1 / Rcount ' /////////' get the average of moving range

UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
LCL = atavg1 - (2.66 * atdiffavg1) ' get the LCL

'Below code is to get the CPK
USL = 800 ' upper spec limit
LSL = 300 ' lower spec limit
tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
UCpu = USL - atavg1 ' USL = 800 - atavg1
Cpu = UCpu / tsigma ' get the cpu
UCpl = atavg1 - LSL 'atavg1 - LSL = 300
Cpl = UCpl / tsigma ' get cpl0u
if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk
if Cpl >= Cpu then Cpk = Cpu

prioravg1=avg1
rs.movenext
Loop
%>
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkgid%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atavg1,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atdiffavg1,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(UCL,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(LCL,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=count%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Range1ave,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rucl,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rlcl,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Formatnumber(cpk,2)%></font></Td><br>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(cp,2)%></font></Td><br>
</tr>

miranda
08-27-2005, 11:33 AM
the code you are showing will have 2 rows displayed. Is that what you want to happen?

cs168
08-27-2005, 01:11 PM
Yes, actualy the first row is my Table header.
Ok to be more clear, In an asp page where should I insert my constant value & also formula that it will not affect the final display or layout of a page. Like my code above the formula that I insert at or where I put is disturbing my page layout.
I dun understand why there are some empty row to bring down my table when displaying the results table.
I put my formula right after the sql statement & before the output table as shown above, please help to settle this...Tq

miranda
08-27-2005, 05:37 PM
Try this, notice where these two lines are compared to what you had

rs.movenext
loop

also you do not use the <br> tag after a </td> tag



<style type="text/css">
td.header{
font-family: Verdana;
font-size: x-small;
font-weight: bold;
text-align: center;
background-color: #fff;
}
td.display{
font-family: Verdana;
font-size: x-small;
text-align: center;
background-color: #f1f1f1;
}
table{
background-color: #0D5692;
}
</style>
<table width="100%" align="center">
<tr>
<td width="7%" class="header">PKG Type</td>
<td width="5%" class="header">Mean</td>
<td width="5%" class="header">Average<br>Moving Range</td>
<td width="7%" class="header">UCL</td>
<td width="7%" class="header">LCL</td>
<td width="7%" class="header">Total<br>Record</td>
<td width="7%" class="header">Range<br>Mean</td>
<td width="7%" class="header">Range<br>UCL</td>
<td width="7%" class="header">Range<br>LCL</td>
<td width="5%" class="header">CPK</td>
<td width="5%" class="header">CP</td>
</tr>

<%
sql="SELECT Avg1, Range1, PkgType FROM tblFiles WHERE pkgtype NOT LIKE '%G%' AND workweek between " & tBegin & " AND " & tEnd & " AND Year = " & tYear & " ORDER BY workweek DESC"
Pkgid= "All Packages"
RS = Conn.Execute(sql)
prioravg1 = -1
tavg1=0
tdiffavg1=0
trange1=0
count=0
Do until rs.eof
avg1 = RS("avg1")
range1= rs("range1")
diffavg1 = 0
If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1

count = count + 1
trange1= trange1 + range1
Range1ave= trange1/ count ' Get the range Average
Rucl = 1.652 * range1ave ' get the range chart UCL
Rlcl = 0.348 * range1ave' get the range chart LCL
sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472
sixsigma = 6 * sigma
specmean = 800 - 300 ' USl - LSL
cp= specmean / sixsigma ' get the process cp


tavg1 = tavg1 + avg1
tdiffavg1 = tdiffavg1 + diffavg1
atavg1= tavg1 / count ' get the average of avg1
if rs.recordcount > 1 then Rcount = rs.recordcount - 1
if rs.recordcount = 1 then Rcount = 1
atdiffavg1= tdiffavg1 / Rcount ' /////////' get the average of moving range

UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
LCL = atavg1 - (2.66 * atdiffavg1) ' get the LCL

'Below code is to get the CPK
USL = 800 ' upper spec limit
LSL = 300 ' lower spec limit
tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
UCpu = USL - atavg1 ' USL = 800 - atavg1
Cpu = UCpu / tsigma ' get the cpu
UCpl = atavg1 - LSL 'atavg1 - LSL = 300
Cpl = UCpl / tsigma ' get cpl0u
if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk
if Cpl >= Cpu then Cpk = Cpu

prioravg1=avg1

%>
<tr>
<Td width="7%" class="display"><%=Pkgid%></td>
<Td width="7%" class="display"><%=FormatNumber(atavg1,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(atdiffavg1,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(UCL,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(LCL,2)%></td>
<Td width="7%" class="display"><%=count%></td>
<Td width="7%" class="display"><%=FormatNumber(Range1ave,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(Rucl,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(Rlcl,2)%></td>
<Td width="7%" class="display"><%=Formatnumber(cpk,2)%></td>
<Td width="7%" class="display"><%=FormatNumber(cp,2)%></td>
</tr>
<%
rs.movenext
Loop
%>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum