使用sql server 2005使用asp.net查询数据库 [英] querying database with asp.net using sql server 2005

问题描述

您好，



我目前正在完成一个辅助项目。这一切的最大理想都与动态下拉框有关。我通过使用ajax / jquery工具包听说并看到了自动完成文本框功能。这就是我正在寻找的，但我想知道是否有任何简单的方法可以做到这一点？以下是我到目前为止的情况。我连接到SQL Server数据库，可以根据列检索信息，但一次只能检索一个。我的两个问题在下面提出。任何提示或更多反馈将不胜感激！





http://i.stack.imgur.com/7X56V.png [ ^ ]





http://i.stack.imgur.com/Rj4a3.png [ ^ ]







我只搜索First_Name字符。如果我要搜索Last_Name，Gender或Age，它将无效，除非我从代码中删除了First_Name列并将其替换为新的列名。这就是为什么我要问有什么方法可以在上面的代码中包含所有四列以确保一切都可以搜索？



这就是我的意思'动态'。 http://kmplot.com/analysis/index.php?p=service&cancer=ovar

上面的链接更好地说明了这一点。您随机开始在Affy id / Gene symbol文本框中键入字符，并接收无限量的序列选项供您选择。这就是我被困住的地方，需要在我的项目中实现这种方法。



我的后端代码：

 使用系统; 
 使用 System.Collections.Generic; 
 使用 System.Linq; 
 使用 System.Web; 
 使用 System.Web.UI; 
 使用 System.Web.UI.WebControls; 
 使用 System.Data; 
 使用 System.Data.SqlClient; 
 
  public   partial   class  _Default：System.Web.UI.Page 
 {
 SqlConnection con =  new  SqlConnection（ 数据源= SKYNET-1000 \\SQLEXPRESS;初始目录= CSC_480;集成安全性=真）; 
 
 
 
 受保护  void  Page_Load（ object  sender，EventArgs e）
 {
 
} 
  protected   void  Button1_Click（ object  sender，EventArgs e）
 {
 字符串 str =   select * from People where（First_Name like'％' + @search +'％'）; 
 SqlCommand win7 =  new  SqlCommand（str，con）; 
 win7.Parameters.Add（  @ search，SqlDbType.NVarChar）.Value = TextBox1.Text; 
 
 
 con.Open（）; 
 win7.ExecuteNonQuery（）; 
 SqlDataAdapter da =  new  SqlDataAdapter（）; 
 da.SelectCommand = win7; 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds，  First_Name）; 
 GridView2.DataSource = ds; 
 GridView2.DataBind（）; 
 con.Close（）; 
} 
} 

解决方案

您可以使用或 [ ^ ]关键字满足您的要求：



例如

  t_DemoDropDown 其中 First_Name 喜欢 ' ％' +  @ search  + ' ％' 或 Last_Name 喜欢 ' ％' + < span class =code-sdkkeyword> @ search  + ' ％' 或性别喜欢 ' ％' +  @ search  + ' ％' 或年龄喜欢 '  ％' +  @ search  + ' ％' 
 

祝你好运

Hello,

I am currently completing a side project. The biggest ideal of it all has to do with a dynamic drop down box. I have heard and seen of the 'auto complete textbox' feature by using ajax/jquery toolkits. That is what I'm looking for, but I was wondering if there is any way of a simple way to do it? Below is what I have so far. I am connected to SQL Server database and can retrieve information based off the columns, but only one at a time. My two questions are asked below. Any tips or more feedback would be greatly appreciated!


http://i.stack.imgur.com/7X56V.png[^]


http://i.stack.imgur.com/Rj4a3.png[^]



I'm only searching by "First_Name" characters only. If I were to search for "Last_Name", "Gender" or "Age" it wouldn't work unless I deleted the First_Name column from the code and replaced it with a new column name. That is why I am asking is there any way I could include all four columns in the code above to ensure everything would be searchable?

And this is what I mean by 'dynamic'. http://kmplot.com/analysis/index.php?p=service&cancer=ovar
The link above better illustrates. You randomly start typing characters in the "Affy id/Gene symbol" textbox and receive an unlimited options of sequences to choose from. This is where I'm stuck and need to implement this type of method into my project.

My back end code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=SKYNET-1000\\SQLEXPRESS;Initial Catalog=CSC_480;Integrated Security=True"); 
    
 

    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        String str = "select * from People where (First_Name like '%' + @search + '%')";
        SqlCommand win7 = new SqlCommand(str, con);
        win7.Parameters.Add("@search", SqlDbType.NVarChar).Value = TextBox1.Text;
 

        con.Open();
        win7.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = win7;
        DataSet ds = new DataSet();
        da.Fill(ds, "First_Name");
        GridView2.DataSource = ds;
        GridView2.DataBind();
        con.Close();
    }
}

解决方案

You can use OR[^] keyword to fulfill your requirement :

E.g.

select * from t_DemoDropDown where First_Name like '%' + @search + '%' OR Last_Name like '%' + @search + '%' OR Gender  like '%' + @search + '%' OR Age like '%' + @search + '%'

Good luck

这篇关于使用sql server 2005使用asp.net查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！