Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    To have two Sql in one asp page

    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

  • #2
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RS.Close
    RS.Open ... (Rest of the junk here)
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #5
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by cs168
    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...
    Code:
    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.
    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.
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #6
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Which calcuations are being displayed as 0.00? Where are you initially storing a value for those variables?

  • #8
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    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:-
    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>

  • #9
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    the code you are showing will have 2 rows displayed. Is that what you want to happen?

  • #10
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #11
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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
    Code:
    <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
    %>
    Last edited by miranda; 08-27-2005 at 05:47 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •