I'm trying to use a web service for an auto complete text box that reads from the database. When I try to compile I get the error
我正在尝试将Web服务用于从数据库中读取的自动完整文本框。当我尝试编译时，我得到了错误
HttpExceptionUnhandled Markup and JS with indication of where the exception occurs
HttpExceptionUnhandled标记和JS，指示异常发生的位置
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"
type="text/javascript"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
rel="Stylesheet" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" type="text/javascript"></script>
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<script type="text/javascript">
$(document).ready(function () {
$("#txtSearch").autocomplete({
source: function (request, response) {
$.ajax({
error on this line url: '<%=ResolveUrl("~/Service.asmx/GetDrugs") %>',
data: "{ 'prefix': '" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return {
label: item.split('-')[0],
val: item.split('-')[1]
}
}))
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
$("#txtHidden").val(i.item.val);
},
minLength: 1
});
});
</script>
<form id="form1" runat="server">
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:HiddenField ID="txtHidden" runat="server" />
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="Submit" />
</form>
</asp:Content>
When I try to run the web service in a browser I can see the list of methods available and click on GetDrugs and it takes me to a screen where I can enter text, and it also fails. When it fails I get the following text in my browser
当我尝试在浏览器中运行Web服务时，我可以看到可用的方法列表并单击GetDrugs，它会将我带到一个我可以输入文本的屏幕，它也会失败。当它失败时，我在浏览器中收到以下文本
error that happens when I try to invoke the web service. This is after reforming the SQL and adding a ToString() to the reader in the while loop.
我尝试调用Web服务时发生的错误。这是在改造SQL并在while循环中向读者添加ToString（）之后。
System.FormatException: Input string was not in a correct format.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at System.String.Format(String format, Object arg0)
at Service.GetDrugs(String prefix) in c:\Users\dtceci2\Desktop\WebSite2\App_Code\Service.cs:line 45
The connection string is fine and SQL Server is running on my local machine. Here is the code for the web service
连接字符串很好，SQL Server正在我的本地计算机上运行。这是Web服务的代码
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Services;
/// <summary>
/// Summary description for Service_CS
/// </summary>
[WebService(Namespace = "http://localhost/~Service.asmx")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
public Service()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string[] GetDrugs(string prefix)
{
List<string> drugs= new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select distinct drug_name from rx where drug_name like @SearchText" + "'%'";
cmd.Parameters.AddWithValue("@SearchText", prefix);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
drugs.Add(string.Format("{0}}", rdr["drug_name"].ToString()));
}
}
conn.Close();
}
return drugs.ToArray();
}
}
}
The code behind for my page with the markup is simply
带有标记的页面背后的代码很简单
public partial class _Default : System.Web.UI.Page
{
protected void Submit(object sender, EventArgs e)
{
string drugName = Request.Form[txtSearch.Text];
}
}
Any clues on what I'm messing up?
关于我搞砸了什么的任何线索？
2 个解决方案
#1
1
Your plus sign is missing in the query. It should be:
查询中缺少加号。它应该是：
"select distinct drug_name from rx where drug_name like @SearchText + '%'";
The SQL that's currently getting sent to SQL Server is:
当前发送到SQL Server的SQL是：
select distinct drug_name from rx where drug_name like @SearchText'%'
which is of course no good.
这当然不好。
#2
1
Append the '%' to the incoming variable before using it in the parameter so you don't have to be concerned about concatenation in the SQL SELECT.
将“％”附加到传入变量，然后在参数中使用它，这样您就不必担心SQL SELECT中的连接。
prefix += "%";
Fixed GetCustomers() function:
修复了GetCustomers（）函数：
public string[] GetCustomers(string prefix)
{
prefix += "%";
List<string> customers = new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select distinct drug_name from rx where drug_name like @SearchText";
cmd.Parameters.AddWithValue("@SearchText", prefix);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
customers.Add(rdr["drug_name"].toString());
}
}
conn.Close();
}
return customers.ToArray();
}
}
}
#1
1
Your plus sign is missing in the query. It should be:
查询中缺少加号。它应该是：
"select distinct drug_name from rx where drug_name like @SearchText + '%'";
The SQL that's currently getting sent to SQL Server is:
当前发送到SQL Server的SQL是：
select distinct drug_name from rx where drug_name like @SearchText'%'
which is of course no good.
这当然不好。
#2
1
Append the '%' to the incoming variable before using it in the parameter so you don't have to be concerned about concatenation in the SQL SELECT.
将“％”附加到传入变量，然后在参数中使用它，这样您就不必担心SQL SELECT中的连接。
prefix += "%";
Fixed GetCustomers() function:
修复了GetCustomers（）函数：
public string[] GetCustomers(string prefix)
{
prefix += "%";
List<string> customers = new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select distinct drug_name from rx where drug_name like @SearchText";
cmd.Parameters.AddWithValue("@SearchText", prefix);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
customers.Add(rdr["drug_name"].toString());
}
}
conn.Close();
}
return customers.ToArray();
}
}
}