#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    2
    Rep Power
    0

    Question VBScript - Database - Recordset] ASP - Help with Count, and multiple queries to SQL 2


    Need some help, newbie here with ASP. I am trying to query our Orion database in SQL2005. My connection string is working correctly and the SQL queries work from within the SQL editor in SQL 2005.

    All I am trying to do is run two queries which display the number of nodes down and the number of nodes on their backup, but when I run this it always displays 1 as the count which is incorrect. Anyone see what im doing wrong here? Thanks for any help.


    ASP Code:
     
     <%
        DIM objConn
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "DRIVER={SQL Server};SERVER=servername;UID=username;PWD=password;DATABASE=databasename"
     
        DIM mySQL
        mySQL = "Select count (*) AS StoresOnDBU --Interfaces.StatusLED AS Status_Icon, Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.Caption AS NodeName, Interfaces.InterfaceName AS Interface_Name, Nodes.State AS State, Interfaces.InterfaceAlias AS Interface_Alias, Interfaces.InterfaceLastChange AS Interface_Last_ChangeFROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)WHERE  ((Nodes.RouterLocation = 'stores') AND (Nodes.Status <> '2') AND (Interfaces.InterfaceName LIKE '%Serial0/3/0%') AND ((Interfaces.Status <> '1') OR ((Interfaces.FullName LIKE '%sync%') AND ((NullIf(InBps,-2)+NullIf(OutBps,-2) > 0) AND((Nodes.Node_Type = 'Router') AND(Nodes.Status <> '2'))))) AND (Nodes.Status <> '9'))Select count (*) AS StoresDown --Interfaces.StatusLED AS Status_Icon, Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.Caption AS NodeName, Interfaces.InterfaceName AS Interface_Name, Nodes.State AS State, Interfaces.InterfaceAlias AS Interface_Alias, Interfaces.InterfaceLastChange AS Interface_Last_Change FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) WHERE ((Nodes.RouterLocation = 'stores') AND (Nodes.Status <> '1') AND (Interfaces.InterfaceName LIKE '%Serial0/3/0%') AND ((Interfaces.Status <> '1') OR ((Interfaces.FullName NOT LIKE '%sync%') AND ((NullIf(InBps,-2)+NullIf(OutBps,-2) = 0) AND ((Nodes.Node_Type = 'Router') AND (Nodes.Status <> '1'))))) AND (Nodes.Status <> '9'))"
     
        DIM objRS
        Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.Open mySQL, objConn
     
    	Do While Not objRS.EOF
    		objRS.MoveNext
    	Loop
     
    	Set objRS = objRS.NextRecordset("ADODB.Recordset")
     
    	Do While Not objRS.EOF
    		objRS.MoveNext
     
    	Loop
        %>
    <HTML>
     
    <HEAD>
     
    <TITLE>
    Custom Orion Database Query
    </TITLE>
     
    </HEAD>
     
    <container>
     
    <BODY>
     
    <table>
     
    <th><h1>Stores On DBU</h1></th>
    <th><h1>Stores Down</h1></th>
     
    <tr><th><h2><% Response.Write objRS("StoresOnDBU")%></h2></th>
    <th><h3><% Response.Write objRS("StoresDown")%></h3></th></tr>
     
    </table>
     
     
    <%
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
     
    </BODY>
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Capture the sql string by response.write to your browser, copy & paste it into your sql server query tool and see if you are getting the desired recordset.

    And why are you using next recordset? I believe that would be incorrect unless your single sql query created two recordsets. But I don't use that method, I may be wrong.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    2
    Rep Power
    0

    THanks for the help


    Originally Posted by Doug G
    Capture the sql string by response.write to your browser, copy & paste it into your sql server query tool and see if you are getting the desired recordset.

    And why are you using next recordset? I believe that would be incorrect unless your single sql query created two recordsets. But I don't use that method, I may be wrong.
    Thanks for the help, the query was working in the sql editor but it seems like ASP does not like the --, after reformatting the SQL queries its working and I changed the next recordset, it was incorrect. Working code below:

    ASP Code:
     
        <%
        DIM objConn
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "DRIVER={SQL Server};SERVER=name;UID=user;PWD=password;DATABASE=name"
     
        DIM mySQL
     
    	mySQL = "Select COUNT (*) AS StoresOnDBU FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) WHERE ((Nodes.RouterLocation = 'stores') AND (Nodes.Status <> '2') AND (Interfaces.InterfaceName LIKE '%Serial0/3/0%') AND ((Interfaces.Status <> '1') OR ((Interfaces.FullName LIKE '%sync%') AND ((NullIf(InBps,-2)+NullIf(OutBps,-2) = 0) AND ((Nodes.Node_Type = 'Router') AND (Nodes.Status <> '2'))))) AND (Nodes.Status <> '9'));
     
    Select COUNT (*) AS StoresDown FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) WHERE ((Nodes.RouterLocation = 'stores') AND (Nodes.Status <> '1') AND (Interfaces.InterfaceName LIKE '%Serial0/3/0%') AND ((Interfaces.Status <> '1') OR ((Interfaces.FullName NOT LIKE '%sync%') AND ((NullIf(InBps,-2)+NullIf(OutBps,-2) = 0) AND ((Nodes.Node_Type = 'Router') AND (Nodes.Status <> '1'))))) AND (Nodes.Status <> '9'))"
     
     
        DIM objRS
        Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.Open mySQL, objConn
        %>
     
    <HTML>
     
    <HEAD>
     
    <TITLE>
    Custom Orion Database Query
    </TITLE>
     
    </HEAD>
     
    <BODY>
     
    <table>	
     
    <th><h1>Stores On DBU</h1></th>
    <th><h1>Stores Down</h1></th>
     
    <tr><th><h2>
    	<%
    	DO while Not objRS.EOF
    	Response.Write objRS ("StoresOnDBU")
    	objRS.MoveNext
    	Loop
    	%>
    </h2></th>
    	<%
    	Set objRS = objRS.NextRecordset()
    	%>
    <th><h3>
    	<%
    	Do while Not objRS.EOF
    	Response.Write objRS ("StoresDown")
    	objRS.MoveNext
    	Loop
    	%>
    </h3></th></tr>
     
    </table>
     
    </BODY>
    	<%
    	objRS.Close
    	Set objRS = Nothing
    	objConn.Close
    	Set objConn = Nothing
    	%>
    </HTML>
    ASP
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Thanks for posting your solution, I'm glad you got it working.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo