- 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</pre>
- 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:
- Here is the code for your datasource. I usually put this under the Textbox:
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.
SelectCommand="SELECT [incident_description], [incident_name], [process_id], [classification_category_01], [classification_category_02] FROM [ServiceDeskIncidentManagement] WHERE (([incident_name] LIKE '%' + @incident_name + '%') AND ([incident_description] LIKE '%' + @incident_description + '%')) ORDER BY [process_id]"> <SelectParameters> <asp:Parameter DefaultValue="" Name="incident_name" Type="String" /> <asp:ControlParameter ControlID="NameTextBox" Name="incident_description" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource></pre> <li>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</li>