Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1367
  • Last Modified:

VBScript - sorting table

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
IM&T SRFT
Asked:
IM&T SRFT
  • 5
  • 4
1 Solution
 
prashanthdCommented:
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
 
IM&T SRFTAuthor Commented:
Thanks for this. Have just tried it but I get a runtime error:

Line: 79
Error: Object required: 'WScript'
0
 
prashanthdCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
IM&T SRFTAuthor Commented:
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
 
prashanthdCommented:
can you try inserting "On error resume next" on line no 29
0
 
IM&T SRFTAuthor Commented:
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
 
prashanthdCommented:
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
 
IM&T SRFTAuthor Commented:
This is perfect. Thank you.
0
 
IM&T SRFTAuthor Commented:
Very impressive. Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now