Link to home
Create AccountLog in
Avatar of cer_petsafe
cer_petsafeFlag for United States of America

asked on

Ajax Autocomplete showing wrong data

I'm using a textbox with an autocomplete extender and I finaly got it to display proberly. It works fine except if I enter in a 1, 2, or 3 as the first prefix. If I enter say a 3 or 300, the autocomplete extender will display a bunch of numberical data that isn't even in the data table.  The strange thing is, when I run the WebMethod it works perfectly with no errors.  I'm thinking that it has something to do with the query string using "like" but I don't know much about queries and I could use some help. I attached the WebMethod below.
<WebMethod()> _
    Public Function GetAssyNumber(ByVal prefixText As String) As String()
 
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & """" & "|DataDirectory|\Manufacture Yields.mdf" & """" & ";Integrated Security=True;User Instance=True")
        Dim comText As String = "SELECT * FROM [Yield Table] WHERE [SKU_Number] like @prefixText"
        Dim DA As SqlDataAdapter = New SqlDataAdapter(comText, conn)
        DA.SelectCommand.Parameters.Add("@prefixText", Data.SqlDbType.VarChar, 50).Value = prefixText + "%"
        Dim DT As New DataTable
        DA.Fill(DT)
        Dim cnt As Integer = DT.Rows.Count
        Dim DR As DataRow
        Dim items(cnt - 1) As String
        'Dim SkuNo As New List(Of String)
        Dim i As Integer = 0
        For Each DR In DT.Rows
            items.SetValue(DR("SKU_Number").ToString, i)
            i = i + 1
        Next
 
        Return items.ToArray()
    End Function

Open in new window

Avatar of cer_petsafe
cer_petsafe
Flag of United States of America image

ASKER

Sorry, something happened to my code snippet.  Here is the WebMethod again.
<WebMethod()> _
    Public Function GetAssyNumber(ByVal prefixText As String) As String()
 
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & """" & "|DataDirectory|\Manufacture Yields.mdf" & """" & ";Integrated Security=True;User Instance=True")
        Dim comText As String = "SELECT * FROM [Yield Table] WHERE [SKU_Number] like @prefixText"
        Dim DA As SqlDataAdapter = New SqlDataAdapter(comText, conn)
        DA.SelectCommand.Parameters.Add("@prefixText", Data.SqlDbType.VarChar, 50).Value = prefixText + "%"
        Dim DT As New DataTable
        DA.Fill(DT)
        Dim cnt As Integer = DT.Rows.Count
        Dim DR As DataRow
        Dim items(cnt - 1) As String
        
        Dim i As Integer = 0
        For Each DR In DT.Rows
            'SkuNo.Add(DR("SKU_Number").ToString)
            items.SetValue(DR("SKU_Number").ToString, i)
            i = i + 1
        Next
 
        
        Return items.ToArray()
    End Function

Open in new window

My first thoughts were that it had something to do with the items array being returned whether or not the datatable has items, but I took a closer look and realized that you're actually instantiating the size using the row count.

Could you perhaps step into the method and see if, for some reason, the adapter is passing back values when you enter a 1, 2, or 3?

I, obviously, don't know anything about what's in your table, but considering the column you're checking is for SKU Numbers, I don't see a reason why you can't have a few that start with 1, 2, or 3.
When I step through the WebMothod and enter in the number 3.  I get the correct values : 340EFS, 300GIFD, 300GIFD, 340EFS, etc.  But when I do the same on the webapp I get values like: 272, 91, -32, 340EFS, 272, -350, etc.  Notice that the "340EFS" is displayed, but those other numbers are not in the data table.
That's really strange.
At the risk of being a complete pain, I just want to confirm that when you're stepping through the for each, it's not adding those values?  I just can't figure out how they could somehow be added into the list when the web method isn't returning them.
No, When I step through the WebMethod its only giving my the correct values.  I did change the query to reture distinct values though, but that didn't fix the current problem.  Here is the latest WebMethod.
   <WebMethod()> _
    Public Function GetAssyNumber(ByVal prefixText As String) As String()
 
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & """" & "|DataDirectory|\Manufacture Yields.mdf" & """" & ";Integrated Security=True;User Instance=True")
        Dim comText As String = "SELECT DISTINCT [SKU_Number] FROM [Yield Table] WHERE [SKU_Number] like @prefixText"
        Dim DA As SqlDataAdapter = New SqlDataAdapter(comText, conn)
        DA.SelectCommand.Parameters.Add("@prefixText", Data.SqlDbType.VarChar, 50).Value = prefixText + "%"
        Dim DT As New DataTable
        DA.Fill(DT)
        Dim cnt As Integer = DT.Rows.Count
        Dim DR As DataRow
        Dim items(cnt - 1) As String
        Dim i As Integer = 0
        For Each DR In DT.Rows
            items.SetValue(DR("SKU_Number").ToString, i)
            i = i + 1
        Next
 
        Return items.ToArray()
    End Function

Open in new window

OK I know what the problem is but I'm not sure how to fix it.  I noticed that it was only giving me problems with numerical enteries whit hyphens in them.  I realize now that it is showing the SUM of the item number and not the item number as a string.  But it is not summing in the WebMethod, its something to do with the webapp.  So how do I fix this? I'll post the code from the .aspx page.
<asp:TextBox ID="SKU" runat="server"  Text='<%# Bind("SKU_Number")%>' AutoPostBack="True" />
                                                        <cc1:AutoCompleteExtender ID="AutoCompleteExtender" 
                                                                runat="server" 
                                                                MinimumPrefixLength="1"
                                                                Enabled="True" 
                                                                EnableCaching="True"
                                                                ServiceMethod="GetAssyNumber"
                                                                ServicePath="AutoComplete.asmx" 
                                                                TargetControlID="SKU" 
                                                                CompletionSetCount="10" >
                                                         </cc1:AutoCompleteExtender>  

Open in new window

I just tested this out on one of my own apps with an auto complete extender.  It seems to be a bug with the extender that I had never noticed before.  You should report this to them.

A possible work-around would be to change your query so that it strips out any hyphens:

Dim comText As String = "SELECT DISTINCT REPLACE([SKU_Number], '-', ' ') FROM [Yield Table] WHERE [SKU_Number] like @prefixText"
I get the following error in the WebMethod with that query.

"System.ArgumentException: Column 'SKU_Number' does not belong to table."

Also the hyphen is pretty important because these items need to match items in Oracle exactly.  Don't you just love Oracle :)
ASKER CERTIFIED SOLUTION
Avatar of Bane83
Bane83
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Well the backslashes work but then I have to remove them before I can use the selected value in the texbox.  I'll try to update the toolkit and see if that works.
OK I fixed it.  The update didn't fix the problem but I took your code and put quotes around it instead of backslashes.  This seems to works great. No quotations show up in the texbox upon selection from the autocomplete box.  Here is the code.  Thank you Bane83 for your help.
  For Each DR In DT.Rows
            If DR("SKU_Number").ToString.Contains("-") Then
                items.SetValue("""" + DR("SKU_Number").ToString + """", i)
            Else
                items.SetValue(DR("SKU_Number").ToString, i)
            End If
            i = i + 1
        Next

Open in new window

Thanks for your help!!
Good, glad to help.