AJAX AutoCompleteExtender from query help

[smbrown]

I am trying to use a query to give the suggestions for the AutoCompleteExtender. The event is fired, and I catch an error
"No value given for one or more parameters."
I am thinking that the line:
da.SelectCommand.Parameters.Add("@prefixText", OleDbType.VarChar, 50).Value = prefixText & "%"
is not passing the value of the prefixText to the query. Not sure what this is supposed to look like.
Any ideas?

Dim Con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\AJAXEnabledWebSite3\App_Data\Test.mdb"
Dim dt As New DataTable()
Dim sql As String = "SELECT * FROM Test WHERE Name like @prefixText"
Dim connection As New OleDbConnection(Con)
Dim i As Integer = 0
        Try
            connection.Open()
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, Con)
            da.SelectCommand.Parameters.Add("@prefixText", OleDbType.VarChar, 50).Value = prefixText & "%"
            da.Fill(dt)
            Dim items As String() = New String(dt.Rows.Count - 1) {}
            For Each dr As DataRow In dt.Rows
                items.SetValue(dr("Name").ToString(), i)
                i += 1
            Next
            Return items
        Catch ex As System.Data.OleDb.OleDbException
            Dim MsgResult As MsgBoxResult
            Dim msg As String = "Fetch Error:"
            msg += ex.Message
            MsgResult = MsgBox(msg)
        Finally
            connection.Close()
        End Try

Thanks,
SMBrown

 

[smbrown]

Got it. I just eliminated the parameters.add and pass the value straight to the select statement. Made a few changes to the order I was doing this in.
So now the code looks like this:

Dim Con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\AJAXEnabledWebSite3\App_Data\Test.mdb"
        Dim dt As New DataTable()
        Dim sql As String = "SELECT * FROM Test WHERE Name LIKE '" & prefixText & "%'"
        Dim connection As New OleDbConnection(Con)
        Dim i As Integer = 0
        Try
            connection.Open()
            Dim cmd As New OleDbCommand(sql, connection)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            cmd.ExecuteNonQuery()
            da.Fill(dt)
            Dim items As String() = New String(dt.Rows.Count - 1) {}
            For Each dr As DataRow In dt.Rows
                items.SetValue(dr("Name").ToString(), i)
                i += 1
            Next
            Return items
        Catch ex As System.Data.OleDb.OleDbException
            Dim MsgResult As MsgBoxResult
            Dim msg As String = "Fetch Error:"
            msg += ex.Message
            MsgResult = MsgBox(msg)
        Finally
            connection.Close()
        End Try

[\code]

Thanks, 
SMBrown