从数据库自动完成 [英] Autocomplete from database
本文介绍了从数据库自动完成的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用以下代码实现了自动完成文本框，并且效果很好:
Hi,
I implemented an autocomplete textbox using following code and it works fine :
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AutoComplete2._Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static string[] GetCompletionList(string prefixText, int count)
{
return "this is sample text".Split();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
ServiceMethod="GetCompletionList"
CompletionInterval="500"
ServicePath="" TargetControlID="TextBox1"
EnableCaching="true"
CompletionSetCount="20"
DelimiterCharacters=";, :"
UseContextKey="True">
</asp:AutoCompleteExtender>
<br />
</div>
</form>
</body>
</html>
我想从数据库读取自动完成功能.我尝试在上面的脚本中使用以下代码，但是它没有用，并显示了一些错误
在"SqlConnection oConn = new SqlConnection(connect);"部分:
I want the autocomplete to read from database. And i tried using the following code in above script, but it didn''t work and shows some error
in "SqlConnection oConn = new SqlConnection(connect);" part:
<script>
public string[] GetCompanyName(string prefixText, int count)
{
string sql = "Select * from Table1 Where Company_Name like '" + prefixText + "%" + "'";
string connect = ConfigurationManager.AppSettings["Conn"];
SqlConnection oConn = new SqlConnection(connect);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
da.SelectCommand.Parameters.Add(@prefixText, SqlDbType.NVarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["Company_Name"].ToString(), i);
i++;
}
return items;
}
</script>
谁能告诉我哪里错了?我上面的代码正确吗?
could anyone tell me where''s the wrong? Is my code above correct?
推荐答案
假设在配置管理器(在Web.config中)中正确配置了连接字符串，并且以下代码返回有效的连接字符串，
Assuming that, the connection string is properly configured in the configuration manager (In Web.config) and the following code returns a valid connection string,
string connect = ConfigurationManager.AppSettings["Conn"];
您需要使用以下代码打开连接:
You need to open the connection using the following code:
try
{
oConn.Open();
}
catch (Exception ex)
{
//You can log your exception message here
}
finally
{
// Closing sql Connection
oConn.Close();
}
如果未打开连接，则DataAdapter(SqlDataAdapter)将无法从数据库中获取数据.希望这可以解决问题. :)
If the connection is not opened, the DataAdapter (SqlDataAdapter) won''t be able to fetch data from the database. Hopefully, this will solve the problem. :)
谢谢朋友.
我已经解决了问题.
我在aspx文件上添加了以下代码:
Hi,thanks friend.
I have solved the problem.
I added following code on aspx file:
<code>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %></code>
而此代码在cs文件中:
And this code on cs file:
<pre><br />
using System.Data.SqlClient;</pre>
它工作正常，可以从数据库中读取和建议.
完整的代码如下所示:
it works fine,it reads and suggests from database.
The complete code will look like this :
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AutoComplete2._Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static string[] GetCompletionList(string prefixText, int count)
{
string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(conString);
connection.Open();
SqlParameter prm;
string sql = "Select Company_Name FROM Table1 WHERE Company_Name LIKE @prefixText";
SqlDataAdapter cmd = new SqlDataAdapter(sql, connection);
prm = new SqlParameter("@prefixText", SqlDbType.VarChar, 50);
prm.Value = prefixText + "%";
cmd.SelectCommand.Parameters.Add(prm);
DataTable dt = new DataTable();
cmd.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["Company_Name"].ToString(), i);
i++;
}
connection.Close();
return items;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<asp:TextBox ID="TextBox1" runat="server" Height="22px" Width="252px" ></asp:TextBox>
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
ServiceMethod="GetCompletionList"
CompletionInterval="500"
ServicePath="" TargetControlID="TextBox1"
EnableCaching="true"
CompletionSetCount="20"
DelimiterCharacters=";, :"
UseContextKey="True">
</asp:AutoCompleteExtender>
<br />
</div>
</form>
</body>
</html>
这篇关于从数据库自动完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
