Here's the code. Thanks again!
[Code Search_click()]
Dim DBdataset As DataSet
Dim DBAdapter As OleDbDataAdapter
connection = New OleDbConnection(connectionString)
' Create a OleDbDataAdapter for the table.
DBAdapter = New OleDbDataAdapter()
' A table mapping names the DataTable.
DBAdapter.TableMappings.Add("Table", "Tickets")
' Open the connection.
connection.Open()
' Create a OleDbCommand to retrieve Tickets data.
Dim DBSelectCommand As OleDbCommand
Dim sStat As String
sStat = "New"
If IsNumeric(txtSearch.Text) Then
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where [ticketno] = " & txtSearch.Text, connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where status <> '" & sStat & "' AND [ticketno] = " & txtSearch.Text, connection)
End If
ElseIf Not IsNumeric(txtSearch.Text) And txtSearch.Text <> "" Then
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where [lname] LIKE '%" & txtSearch.Text & "%' OR [product] LIKE '%" & txtSearch.Text & "%' OR [company] LIKE '%" & txtSearch.Text & "%'", connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where status <> '" & sStat & "' AND ([lname] LIKE '%" & txtSearch.Text & "%' OR [product] LIKE '%" & txtSearch.Text & "%' OR [company] LIKE '%" & txtSearch.Text & "%')", connection)
End If
Else
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] FROM Tickets", connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] FROM Tickets WHERE status <> '" & sStat & "'", connection)
End If
End If
DBSelectCommand.CommandType = CommandType.Text
' Set the OleDbDataAdapter's SelectCommand.
DBAdapter.SelectCommand = DBSelectCommand
' Fill the DataSet.
DBdataset = New DataSet("Tickets")
DBAdapter.Fill(DBdataset)
connection.Close()
Dim Dbdatatable As DataTable
Dbdatatable = DBdataset.Tables(0)
DBView = Dbdatatable.DefaultView
Me.GridView1.DataSource = DBdataset
Me.GridView1.DataBind()
[/Code]
[Code GridView1_Sorting()]
Dim DBdataset As DataSet
Dim DBAdapter As OleDbDataAdapter
connection = New OleDbConnection(connectionString)
' Create a OleDbDataAdapter for the table.
DBAdapter = New OleDbDataAdapter()
' A table mapping names the DataTable.
DBAdapter.TableMappings.Add("Table", "Tickets")
' Open the connection.
connection.Open()
' Create a OleDbCommand to retrieve Tickets data.
Dim sStat As String
sStat = "New"
Dim DBSelectCommand As OleDbCommand
If IsNumeric(txtSearch.Text) Then
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where [ticketno] = " & txtSearch.Text, connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where status <> '" & sStat & "' AND [ticketno] = " & txtSearch.Text, connection)
End If
ElseIf Not IsNumeric(txtSearch.Text) And txtSearch.Text <> "" Then
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where [lname] LIKE '%" & txtSearch.Text & "%' OR [product] LIKE '%" & txtSearch.Text & "%' OR [company] LIKE '%" & txtSearch.Text & "%'", connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] from Tickets Where status <> '" & sStat & "' AND ([lname] LIKE '%" & txtSearch.Text & "%' OR [product] LIKE '%" & txtSearch.Text & "%' OR [company] LIKE '%" & txtSearch.Text & "%')", connection)
End If
Else
If Session("UserType") = "Admin" Then
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] FROM Tickets", connection)
Else
DBSelectCommand = New OleDbCommand("SELECT [ticketno], [lname], [company], [product], [date_entered], [assigned], [status] FROM Tickets WHERE status <> '" & sStat & "'", connection)
End If
End If
DBSelectCommand.CommandType = CommandType.Text
' Set the OleDbDataAdapter's SelectCommand.
DBAdapter.SelectCommand = DBSelectCommand
' Fill the DataSet.
DBdataset = New DataSet("Tickets")
DBAdapter.Fill(DBdataset)
connection.Close()
Dim Dbdatatable As DataTable
Dbdatatable = DBdataset.Tables(0)
DBView = Dbdatatable.DefaultView
Dim sDirection As String
If ViewState("sortDirection") Is Nothing Then
ViewState("sortDirection") = SortDirection.Ascending
sDirection = " ASC"
ElseIf ViewState("sortDirection") = SortDirection.Ascending Then
ViewState("sortDirection") = SortDirection.Descending
sDirection = " DESC"
ElseIf ViewState("sortDirection") = SortDirection.Descending Then
ViewState("sortDirection") = SortDirection.Ascending
sDirection = " ASC"
Else
ViewState("sortDirection") = SortDirection.Ascending
sDirection = " ASC"
End If
DBView.Sort = e.SortExpression & sDirection
Me.GridView1.DataSource = DBView
Me.GridView1.DataBind()
[/Code]