Help with a asp.NET search query

[15389049]

I am trying to search a database using a user defined string from a text box. I am getting an error message with the "objectadaptor.Fill"

Here is the code:

Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Partial Class Search
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim objConnection As OleDbConnection
        Dim objCommand As OleDbCommand
        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As DataSet
        Dim strSearch As String
        Dim strSQLQuery As String

        'some code taken from www.asp101.com

        ' Get Search 
        strSearch = TextBox1.Text

        ' If there's nothing to search for then don't search
        ' o/w build our SQL Query and execute it.
        If Len(Trim(strSearch)) > 0 Then

            ' Set up our connection.
            objConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")

            ' Set up our SQL query text.
            strSQLQuery = "SELECT FName AS Name, FROM tblUser " _
            & "OR FName LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
            & "ORDER BY FName;"
            
            ' Create new command object passing it our SQL query
            ' and telling it which connection to use.
            objCommand = New OleDbCommand(strSQLQuery, objConnection)

            ' Get a DataSet to bind the DataGrid to
            objAdapter = New OleDbDataAdapter(objCommand)
            objDataSet = New DataSet()
            objAdapter.Fill(objDataSet)            'problem line!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

            ' DataBind DG to DS
            dgPaging.DataSource = objDataSet
            dgPaging.DataBind()

            objConnection.Close()

        Else
            TextBox1.Text = "Enter Search Here"
        End If
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgPaging.SelectedIndexChanged

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Response.Redirect("myProjectsPage.aspx")
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
End Class

Please help!

[pritaeas]

What is the error message ?

[15389049]

What is the error message ?

its a problem with the:

objAdapter.Fill(objDataSet)

line

Though i think i have it fixed now, I was accesssing the database incorrectly and when i corrected this i no longer have the problem.

Thank you for posting!

here is my new code:

Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Partial Class Search
    Inherits System.Web.UI.Page


    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
        Dim objCommand As OleDbCommand
        Dim objAdapter As OleDbDataAdapter
        Dim objDataSet As DataSet
        Dim strSearch As String

        Dim objConnection As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")

        Dim cn As New OleDbConnection(objConnection)
        Dim strSQLQuery As String

        strSearch = txtSearch.Text

        If Len(Trim(strSearch)) > 0 Then

            strSQLQuery = "SELECT FName & SName FROM tblUser WHERE FName LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY FName;"

            cn.Open()
            Dim cmd As New OleDbCommand(strSQLQuery, cn)

            objCommand = New OleDbCommand(strSQLQuery, cn)

            objAdapter = New OleDbDataAdapter(objCommand)
            objDataSet = New DataSet()
            objAdapter.Fill(objDataSet)

            dgPaging.DataSource = objDataSet
            dgPaging.DataBind()

            'ListBox1.DataSource = objDataSet
            'ListBox1.DataBind()

            cn.Close()
        Else
            txtSearch.Text = "Enter search criteria!"
        End If
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgPaging.SelectedIndexChanged

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Response.Redirect("myProjectsPage.aspx")
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
End Class

[shibbard]

You never opened the connection (objConnection)

Glad you fixed it it anyway ;)