AJAX-AutoComplete Oracle VB.NET

[kym]

I am having problems using the ajax autocomplete. I actually think it may be an Oracle problem not autocomplete problem because I can get the autocomplete working without searching the database for like values. Here is my code:

Imports System
Imports System.IO
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Threading
Imports System.Xml.Serialization
Imports System.Data.Common
Imports System.Data              ' VB.NET
Imports Oracle.DataAccess.Client
 
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class AutoCompleteDAL
    Inherits System.Web.Services.WebService
    Public Shared autoCompleteWordList As String()
 
 
    <WebMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
 
        Dim Sql As String = "SELECT NAME from NAMELIST WHERE NAME  LIKE '" & prefixText & "' || '%' ORDER BY NAME ;"
        Dim Conn As New _
               OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings( _
              "OrdConnectionString").ConnectionString)
        ' Dim Conn As OracleConnection = New OracleConnection("Data Source=dbHazard;User ID=HazUser;Password=password")
        '  Dim cmd As New OracleCommand(Sql, Conn)
        Dim cmd As New OracleClient.OracleCommand(Sql, Conn)
        Dim items As New List(Of String)
        Dim dr As OracleClient.OracleDataReader = cmd.ExecuteReader() ' VB.NET
        While dr.Read() ' VB.NET
            items.Add(dr.Item("NAME"))
        End While
 
        'items.Add(dr.Item("NAME"))
        Return items.ToArray
 
 
 
    End Function
 
 
End Class
 

Any assistance would be greatly appreciated. Thanks.

[Frinavale]

Welcome to TSDN :) I hope you like it here!

What is the error message you're getting?
If there's no error message, could you better explain what is happening?
Have you tried returning a clone of your items array?

Thanks,
-Frinny

[kym]

I do like it here! I have been searching the site for awhile but this is the first time I posted anything. I am not getting an error. I am not getting anything at all. Thanks. The autocomplete works when I am not using the Oracle datareader. It does nothing when insert in the oracle stuff. Thanks.



<asp:TextBox ID="txtNAME" runat="server" MaxLength="5"></asp:TextBox>
<ajaxtoolkit:AutoCompleteExtender ID="AutoCompleteExtender1"
TargetControlID="txtNAME" Enabled="True" ServicePath="AutoComplete.asmx"
ServiceMethod="GetCompletionList" MinimumPrefixLength="0" runat="server" FirstRowSelected="true" >

</ajaxtoolkit:AutoCompleteExtender>

[kym]

Have you tried returning a clone of your items array? No, not sure what this is. I am new to both vb.net and oracle.

[Frinavale]

You should close your data reader when you are done with it to clean things up.
Could you possibly post the code for where you are assigning your "items" array to the control using the auto complete?

Have you tried declaring your items array like this:

Dim items() As New String
 

Instead of like this:

Dim items As New List(Of String)
 

I don't think you'll need to use items.ToArray in your return statement if you change your declaration...I wonder if something might be going wrong there?

-Frinny

[kym]

You should close your data reader when you are done with it to clean things up.
Could you possibly post the code for where you are assigning your "items" array to the control using the auto complete?

Have you tried declaring your items array like this:

Dim items() As New String
 

Instead of like this:

Dim items As New List(Of String)
 

I don't think you'll need to use items.ToArray in your return statement if you change your declaration...I wonder if something might be going wrong there?

-Frinny

I tried this and got the following error:Value of type 'String' cannot be converted to '1-dimensional array of String'.

Thanks again.

[Frinavale]

I tried this and got the following error:Value of type 'String' cannot be converted to '1-dimensional array of String'.

Thanks again.

You need to specify a length for the Array...
Sorry I guess I wasn't thinking clearly earlier..I've just never used a List before.
I just looked it up and it looks like you're doing things correctly.

Earlier, did you mean to say that everything worked properly when you added items to the List manually..and then returned the toArray() version of this list to your calling function?

[kym]

The problem occurs because for some reason it exits the function on the following **** statement:

  <WebMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
 
        Dim Sql As String = "SELECT NAME from NAMELIST WHERE NAME  LIKE '" & prefixText & "' || '%' ORDER BY NAME ;"
        Dim Conn As New _
               OracleClient.OracleConnection(ConfigurationManager  .ConnectionStrings( _
              "OrdConnectionString").ConnectionString)
        ' Dim Conn As OracleConnection = New OracleConnection("Data Source=dbHazard;User ID=HazUser;Password=password")
        Dim cmd As New OracleClient.OracleCommand(Sql, Conn)
        Dim items As New List(Of String)
  '****(exits on the following line)
      Dim dr As OracleClient.OracleDataReader = cmd.ExecuteReader() ' VB.NET
        While dr.Read() ' VB.NET
            items.Add(dr.Item("NAME"))
        End While
 
        'items.Add(dr.Item("NAME"))
        Return items.ToArray
 
 
 
    End Function

[kym]

For some reason it is my connection string

[kym]

For anyone that needs to know, you can not use ; in sql statement with autocomplete this will cause an error. thanks for help.

[Frinavale]

For anyone that needs to know, you can not use ; in sql statement with autocomplete this will cause an error. thanks for help.

Thanks for sharing the solution!

:D