I know this topic is all over the place, but I am not doing an INSERT, UPDATE, DELETE. My statement is a plain and simple SELECT statement and so far has worked with 116 different items in my database until I got to one.
I have a search engine and am going through every single product in our database to add information to it. This is all done through the website, but when I search for ProductID 331 and click on it, it goes to the error page that says Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It doesn't make any sense to me that the website would error with only this one product. This is the statement I am using. Does anyone know why 1 product would be causing this error?
WebService:
Public Class ProductSearch
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetProducts(ByVal prefixText As String, ByVal count As Integer)
As String()
Dim ProductSql As String = "Select DISTINCT ProductID, ProductName
FROM Product WHERE ProductName
LIKE '%' & @prefixText & '%'
ORDER BY ProductName ASC"
Using sqlConn As New SqlConnection
(System.Configuration.ConfigurationManager.ConnectionStrings
("LocalSqlServer").ConnectionString)
sqlConn.Open()
Dim myCommand As New SqlCommand(ProductSql, sqlConn)
myCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50)
.Value = prefixText
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Dim myTable As New DataTable
myTable.TableName = "ProductSearch"
myTable.Load(myReader)
sqlConn.Close()
Dim items As String() = New String(myTable.Rows.Count - 1) {}
Dim i As Integer = 0
For Each dr As DataRow In myTable.Rows
Dim id As String = dr("ProductID").ToString()
Dim name As String = dr("ProductName").ToString()
Dim item As String = AjaxControlToolkit.AutoCompleteExtender
.CreateAutoCompleteItem(name, id)
items.SetValue(item, i)
i += 1
Next
Return items
End Using
End Function
End Class
The aspx page that calls the webservice:
<%@ Page Title="Product Search" Language="VB" MasterPageFile="~/MasterPage.master"
AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"
TagPrefix="asp" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<script type="text/javascript">
function AutoCompleteClientMethod(source, eventArgs) {
var value = eventArgs.get_value();
window.location = ("/Product/Default.aspx?id=" + value)
}
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="body" Runat="Server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="ProductSearch.asmx" />
</Services>
</asp:ScriptManager>
<asp:TextBox ID="Search" runat="server" AutoComplete="off"></asp:TextBox>
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
TargetControlID="Search" ServicePath="~/ProductSearch.asmx"
ServiceMethod="GetProducts" MinimumPrefixLength="1" CompletionSetCount="120"
EnableCaching="true" OnClientItemSelected="AutoCompleteClientMethod">
</asp:AutoCompleteExtender>
</div><!--End of main div -->
</asp:Content>
UPDATE: 11/9/2011 - I have found a couple more records that have this problem. They are ProductID 331-335. I have no idea what is going on here. Could it be that those products don't really exist or that they have some kind of bug?
Here is a list of ProductIDs and their corresponding ProductNames that have this error:
122 'Managed account section of the Web Site'
331 'Elliott Wave Principle Key to Market Behavior'
332 'Targeting Profitable Entry & Exit Points'
333 'Essentials of Trading It's not WHAT You Think, It's HOW You Think'
334 'Exceptional Trading The Mind Game'
335 'Fibonacci Analysis'
