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:
[code=vbnet]
Imports System
Imports System.IO
Imports System.Web
Imports System.Web.Serv ices
Imports System.Web.Serv ices.Protocols
Imports System.Collecti ons
Imports System.Collecti ons.Generic
Imports System.Threadin g
Imports System.Xml.Seri alization
Imports System.Data.Com mon
Imports System.Data ' VB.NET
Imports Oracle.DataAcce ss.Client

<WebService(Nam espace:="http://tempuri.org/")> _
<WebServiceBind ing(ConformsTo: =WsiProfiles.Ba sicProfile1_1)> _
<Global.Microso ft.VisualBasic. CompilerService s.DesignerGener ated()> _
<System.Web.Scr ipt.Services.Sc riptService()> _
Public Class AutoCompleteDAL
Inherits System.Web.Serv ices.WebService
Public Shared autoCompleteWor dList As String()


<WebMethod()> _
Public Function GetCompletionLi st(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.Or acleConnection( ConfigurationMa nager.Connectio nStrings( _
"OrdConnectionS tring").Connect ionString)
' Dim Conn As OracleConnectio n = New OracleConnectio n("Data Source=dbHazard ;User ID=HazUser;Pass word=password")
' Dim cmd As New OracleCommand(S ql, Conn)
Dim cmd As New OracleClient.Or acleCommand(Sql , Conn)
Dim items As New List(Of String)
Dim dr As OracleClient.Or acleDataReader = cmd.ExecuteRead er() ' VB.NET
While dr.Read() ' VB.NET
items.Add(dr.It em("NAME"))
End While

'items.Add(dr.I tem("NAME"))
Return items.ToArray



End Function


End Class
[/code]
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]

AJAX-AutoComplete Oracle VB.NET Edit Post

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:Au toCompleteExten der ID="AutoComplet eExtender1"
TargetControlID ="txtNAME" Enabled="True" ServicePath="Au toComplete.asmx "
ServiceMethod=" GetCompletionLi st" MinimumPrefixLe ngth="0" runat="server" FirstRowSelecte d="true" >

</ajaxtoolkit:Aut oCompleteExtend er>

[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:
[code=vbnet]
Dim items() As New String
[/code]
Instead of like this:
[code=vbnet]
Dim items As New List(Of String)
[/code]

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:
[code=vbnet]
Dim items() As New String
[/code]
Instead of like this:
[code=vbnet]
Dim items As New List(Of String)
[/code]

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:
[code=vbnet]
<WebMethod()> _
Public Function GetCompletionLi st(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.Or acleConnection( ConfigurationMa nager .ConnectionStri ngs( _
"OrdConnectionS tring").Connect ionString)
' Dim Conn As OracleConnectio n = New OracleConnectio n("Data Source=dbHazard ;User ID=HazUser;Pass word=password")
Dim cmd As New OracleClient.Or acleCommand(Sql , Conn)
Dim items As New List(Of String)
'****(exits on the following line)
Dim dr As OracleClient.Or acleDataReader = cmd.ExecuteRead er() ' VB.NET
While dr.Read() ' VB.NET
items.Add(dr.It em("NAME"))
End While

'items.Add(dr.I tem("NAME"))
Return items.ToArray



End Function[/code]

[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