How To: Create Servicedesk Full Text Search Application

  • Create new Empty ASP.Net Visual Basic project in Visual Studio. Name your project what you want the search to be called.

 

 

 

 

 

 

 

 

 

 

  • Add a Default.ASPX web page

 

 

 

 

 

 

 

 

 

 

  • Add a connection string to the ProcessManager or Ensemble database in the web.config file, depending on whether you’re using 7.0 or 7.1. The search application works with both.
  • On the database create a new user with READ ONLY permissions to access the table “ServicedeskIncidentManagement”, this username/password should be saved into the connectionstring.
  • Enter this code into the code behind file of the Default.ASPX page
Public Class _Default
    Inherits System.Web.UI.Page
    Public cnSql As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Public drReader As System.Data.SqlClient.SqlDataReader = Nothing
    ' subroutine for executing non-queries on sql server
    Public Sub sSQLNonQuery(ByVal strSQL As String)
        cnSql.Open()
        While cnSql.State <> ConnectionState.Open
            'wait here until connection opens
        End While

        Try
            Dim cmdsql As New SqlCommand(strSQL, cnSql)
            cmdsql.ExecuteNonQuery()
        Catch sqlexception As SqlException
            MsgBox("An sql error occurred while running the following query:" + vbCrLf + " " + strSQL + vbCrLf + sqlexception.Message)
        Catch ex As Exception
            MsgBox("An error occurred while running the following query:" + vbCrLf + " " + strSQL + vbCrLf + ex.Message)
        Finally
            cnSql.Close()
            While cnSql.State <> ConnectionState.Closed
                'wait here until connection closes
            End While
        End Try
    End Sub

    Public Sub sSQLQuery(ByVal strSQL As String, ByRef drReader As SqlDataReader)
        cnSql.Open()
        While cnSql.State <> ConnectionState.Open
            'wait here until connection opens
        End While

        Dim cmdsql As New SqlCommand(strSQL, cnSql)
        drReader = cmdsql.ExecuteReader()
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
  • Make a textbox with the ID NameTextBox
  • Make a search button with the ID Search
  • Create an event for the search button with the following code:
gvTickets.DataBind()
  • Here is the code for your datasource. I usually put this under the Textbox:
  • 
            
                
                
            
        
  • Create a Gridview box with the ID gvTickets, and bind the fields process_id, incident_name, incident_description, classification_category_01, and classification_category_02

Try it out. I hope it helps. It's much faster then the crap included in the Servicedesk's search. Symantec should have really released a full text search with their product. I don't know anything about software licenses, but here's the deal... you're free to use my code as long as you're not making money off of it. If you have a question please post it as a comment. If you want to contact me privately use the contact page.