Solved

VBScript - sorting table

Posted on 2011-03-17
9
1,256 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now