Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBScript - sorting table

Posted on 2011-03-17
9
Medium Priority
?
1,333 Views
Last Modified: 2012-08-13
The code below shows all the printers on our print server and their relevant status.

I want to be able to sort it so that printers with an "ERROR" or "Unknown or OFFLINE" status are at the top of the table, with the table row background in red and font colour in white.

Thanks in advance.



<HTML>
<style type="text/css">
    body {
        font-family:Arial;
        font-size: 12px;
        color: #49403B;
        background: #FFFFFF;
        scroll:no;
        }
    hr {
        color: #666666;
        }
    input.text, option {
        font-size: 18px;
        background-color: #FFFFBB;
        }
     table {
        font-size: 12px;
        text-align: left;
    }
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
 GetInfo
 iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
 For i = (objTable.Rows.Length - 1) to 0 Step -1
 myNewRow = Document.All.objTable.deleteRow(i)
 Next
 Set objRow = objTableBody.InsertRow()
 objRow.Style.fontWeight = "bold"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Name"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Location"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Status"
 strComputer = "CMRIS"
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & _
 strComputer & "\root\cimv2")
 Set colPrinters = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_Printer")
 For Each objPrinter in colPrinters
 Set objRow = objTableBody.InsertRow()
 Set objCell = objRow.InsertCell()
 objCell.InnerText = objPrinter.Name
 Set objCell = objRow.InsertCell()
 objCell.InnerText = objPrinter.Location
 Set objCell = objRow.InsertCell()
 Select Case objPrinter.PrinterStatus
 Case 1
 strPrinterStatus = "ERROR"
 Case 2
 strPrinterStatus = "Unknown or OFFLINE"
 Case 3
 strPrinterStatus = "Ready"
 Case 4
 strPrinterStatus = "Printing"
 Case 5
 strPrinterStatus = "Warming up"
 End Select
 objCell.InnerText = strPrinterStatus
 Next
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
Comment
Question by:IM&T SRFT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:prashanthd
ID: 35155657
Hi,

Can you check if the sort is working?

regards
Prashanth
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            WScript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            WScript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("name")
        WScript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("location")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("status")
        WScript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
 

Author Comment

by:IM&T SRFT
ID: 35155828
Thanks for this. Have just tried it but I get a runtime error:

Line: 79
Error: Object required: 'WScript'
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35155946
Try the following, forgot to comment the wscript

regards
Prashanth
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            'wscript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            'wscript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("name")
        'wscript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("location")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("status")
        'wscript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:IM&T SRFT
ID: 35155976
Getting another error:

Line: 80
Error: Non-nullable column cannot be updated to Null.

This is probably related to the fact thjat some printers have nothing in their location field.

Thanks.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35156029
can you try inserting "On error resume next" on line no 29
0
 

Author Comment

by:IM&T SRFT
ID: 35156214
The table now displays and sorts correctly. There are 2 issues:

1. Anywhere there is no location, it's completely blank even the table cell is missing and it looks quite strange. I would like it to still show a table cell with no writing in it.

2. I would like the ERROR and OFFLINE statuses to make the whole row red, with a white font. I want to use this to easily see which printers have a problem.

It's nearly there and I really appreciate your efforts.
0
 
LVL 12

Accepted Solution

by:
prashanthd earned 2000 total points
ID: 35156357
My knowledge of HTML is limited, the font color will be changed to Red if there is a problem. I had fixed the location null issue.
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    On Error Resume Next		
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            'wscript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            'wscript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        If DataList.Fields.Item("sortst")="1" Then
            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("name")&"</font>"
        Else
            objCell.Innertext = DataList.Fields.Item("name")
        End If
        'wscript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        
        If len(DataList.Fields.Item("location")) > 0 Then
	        If DataList.Fields.Item("sortst")="1" Then
	            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("location")&"</font>"
	        Else
	            objCell.Innertext = DataList.Fields.Item("location")
	        End If
        Else
        	objCell.Innertext = "  "
        End If
        Set objCell = objRow.InsertCell()
        If DataList.Fields.Item("sortst")="1" Then
            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("status")&"</font>"
        Else
            objCell.Innertext = DataList.Fields.Item("status")
        End If
        'wscript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
 

Author Comment

by:IM&T SRFT
ID: 35156558
This is perfect. Thank you.
0
 

Author Closing Comment

by:IM&T SRFT
ID: 35156562
Very impressive. Thank you.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question