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 4 of 4

Thread: SQL Query help

  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Query help

    I know Im giving this forum a workout at the moment thanks for your help

    I am trying to get the total records based on this statement, it works because Im not drawing out much info, however I do need to use the last part, the filter on the month, thats not working, as a matter of fact its just ignoring it, as if its not there. any ideas

    SQL= "SELECT count(*) as id From NMContact WHERE Age ='"&age&"' AND PolicyId ="&PolicyId&" AND Month ='"&months&"'"

    have included below code in its entirity

    do while i <= loopcount
    ' Now loop through the Policies
    x = 1
    do While x <= policyidmax
    age = ageis(i)
    amount = 0
    policyid = x
    SQL1= "SELECT * FROM Health WHERE IDH ="& policyId
    rs1.open sql1,con
    SQL= "SELECT count(*) as id From NMContact WHERE Age ='"&age&"' AND PolicyId ="&PolicyId&" AND Month ='"&months&"'"
    rs2.open SQL,con
    ' if user does not want the zero entries
    if rs2("id") = 0 and noz = 1 Then
    x = x + 1
    counter = counter + 1
    rs1.close
    rs2.close
    exit do
    end if
    amount = rs2("id")
    %>
    <%If (counter Mod 2) Then%>
    <tr bgcolor="<%=bgcolour2%>">
    <%ELSE%>
    <tr bgcolor="<%=bgcolour1%>">
    <%End If%>
    <td width="33%" height="25"><div align="center"><%=age%><BR>
    </div></td>
    <td width="33%"><div align="center"><%=rs1("HealthType")%><BR>
    </div></td>

    <td width="26%"><div align="center"><%=amount%><BR>
    </div></td>
    <td width="8%"><a href="print.asp?mode=1&msg=1&loop=1&no=<%=noz%>" class="hlink">Print</a> </td>
    </tr>
    <%
    x = x + 1
    counter = counter + 1
    rs1.close
    rs2.close
    loop
    i = i + 1
    ' rs.close
    'rs1.close
    loop
    end if
    %>

  • #2
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    *
    Last edited by A1ien51; 11-08-2003 at 04:59 PM.
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #3
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you sure you get a value for the months var? Test this first... get the value then try a response.write.. If the others var are used I don't see why this one wouldn't...

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Yes this is the output sql

    This is what the SQL is as an output
    there is a field called month and Novemver is in it

    SELECT count(*) as id From NMContact WHERE Age ='-15' AND PolicyId =1 AND Month ='November'


  •  

    Posting Permissions

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