SQL - 创建查询以返回包含两个字符串的值 [英] SQL - creating query to return values containing two strings

问题描述

大家好。我有一个数据库供应商，如下所示。我有一个文本框，其中包含逗号分隔的字符串，如SMPS，MotherBoard。我必须选择包含SMPS和主板的供应商名称。我已经为它编写了查询。但我的代码无法正常工作。我的问题是如何选择包含SMPS和主板的供应商名称。

 ID Vendor_Name Asset_Type 
  37  Futuresoft主板
  37  Futuresoft SMPS 
  38  Future India HDD 
  38  Future India操纵杆
  38  Future India笔记本电脑屏幕
  39  Tech_M Baterry 
  39  Tech_M笔记本电脑屏幕
  39  Tech_M鼠标
  46  dgd电池
  46  dgd RAM 
  46  dgd操纵杆
  46  dgd鼠标
  46  dgd处理器
  47  Religare HDD 
  48  ryy电池
  48  ryy HDD 
  48  ryy joystick 

我的代码如下

  public   void  dddl（）
 {
 DataTable objDt =  new  DataTable（）; 
 objDt.Columns.Add（  Vendor_Name）; 
 
  string  serial = TextBox8.Text; 
会话[  St] = TextBox8.Text; 
  string  [] s = serial.Split（' ）; 
  for （ int  i =  0 ; i <  s.Length; i ++）
 {
 
 
 {
  string  s1 = s [i] .ToString（）; 
  //   string qry =SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type like'％+ s [ i] .ToString（）+％';  
  //   string qry = SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type ='+ s [i] .ToString（）+';  
  //   string qry =SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type ='+ s [i] .ToString（）+'AND Asset_Type ='+ s [i] .ToString （）+';  
  string  qry =   SELECT * from Vendor WHERE Asset_Type =' + s [ 0 ]。ToString（）+  'AND Vendor_Name in（SELECT Vendor_Name from Vendor WHERE Asset_Type like'％ + s [i]。 ToString（）+  ％'）; 
 
 SqlDataAdapter adpt =  new  SqlDataAdapter（qry，con）; 
 
 DataTable dt =  new  DataTable（）; 
 adpt.Fill（dt）; 
 objDt.Merge（dt）; 
 
 
} 
 objDt = objDt.DefaultView.ToTable（ true ，  Vendor_Name）; 
 DropDownList4.DataTextField =   Vendor_Name; 
 DropDownList4.DataValueField =   Vendor_Name; 
 DropDownList4.DataSource = objDt; 
 DropDownList4.DataBind（）; 
 
 con.Close（）; 
 
 
} 
 
} 

如何操作请帮助

解决方案

< blockquote>这样的东西可能会给你你需要的东西，但我认为最有效的是你需要对数据进行标准化，将供应商与资产类型分开然后有一个连接表。

下面的代码不准确，所以你可能需要正确调整它，但你应该明白。

 public void dddl（）
 {
 DataTable objDt = new DataTable（）; 
 objDt.Columns.Add（Vendor_Name）; 
 
 string serial = TextBox8.Text; 
 Session [St] = TextBox8.Text; 
 string [] s = serial.Split（'，'）; 
 var sbuilder = new StringBuilder（）; 
 sbuilder.AppendLine（从Vendor中选择Vendor_Name）; 
 for（int i = 0; i< s.Length; i ++）
 {
 if（i == 0）
 sbuilder.Append（Where）; 
 sbuilder.Append（string.Format（Vendor_Name in（从Vendor中选择Vendor_Name，其中Asset_Type类似于@a {0}），i））; 
 if（i< s.Length-1）
 sbuilder.Append（AND）; 
} 
 
 SqlDataAdapter adpt = new SqlDataAdapter（sbuilder.ToString（），con）; 
 for（int i = 0; i< s.Length; i ++）
 {
 adpt.SelectCommand.Paramters.AddWithValue（string.format（@ a {0}，i） ，s [i]）; 
} 
 DataTable dt = new DataTable（）; 
 adpt.Fill（objDt）; 
 
 
 
 objDt = objDt.DefaultView.ToTable（true，Vendor_Name）; 
 DropDownList4.DataTextField =Vendor_Name; 
 DropDownList4.DataValueField =Vendor_Name; 
 DropDownList4.DataSource = objDt; 
 DropDownList4.DataBind（）; 
 
 con.Close（）; 
 
 
 
 
} 

Hi Everyone. I have a database Vendor as follows. I have a text box that contains comma separated string like SMPS,MotherBoard,. I have to select the vendor name that contains both SMPS and Motherboard. I have written the query for it. but my code is not working correctly. My issue how to select Vendor name that contains both SMPS and Motherboard.

ID Vendor_Name Asset_Type
37 Futuresoft Motherboard
37 Futuresoft SMPS
38 Future India HDD
38 Future India joystick
38 Future India Laptop Screen
39 Tech_M Baterry
39 Tech_M Laptop Screen
39 Tech_M Mouse
46 dgd Battery
46 dgd RAM
46 dgd joystick
46 dgd Mouse
46 dgd Processor
47 Religare HDD
48 ryy Battery
48 ryy HDD
48 ryy joystick

My code is as follows

public void dddl()
 {
     DataTable objDt = new DataTable();
     objDt.Columns.Add("Vendor_Name");

     string serial = TextBox8.Text;
     Session["St"] = TextBox8.Text;
     string[] s = serial.Split(',');
     for (int i = 0; i < s.Length; i++)
     {


         {
             string s1 = s[i].ToString();
             //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type like '%" + s[i].ToString() + "%'";
             //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type = '" + s[i].ToString() + "'";
             //string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type = '" + s[i].ToString() + "' AND Asset_Type= '" + s[i].ToString() + "'";
             string qry = "SELECT * from Vendor WHERE Asset_Type = '" + s[0].ToString() + "' AND Vendor_Name in (SELECT Vendor_Name from Vendor WHERE Asset_Type like '%" + s[i].ToString() + "%')";

             SqlDataAdapter adpt = new SqlDataAdapter(qry, con);

             DataTable dt = new DataTable();
             adpt.Fill(dt);
             objDt.Merge(dt);


         }
         objDt = objDt.DefaultView.ToTable(true, "Vendor_Name");
         DropDownList4.DataTextField = "Vendor_Name";
         DropDownList4.DataValueField = "Vendor_Name";
         DropDownList4.DataSource = objDt;
         DropDownList4.DataBind();

         con.Close();


     }

 }

How to do it Please help

解决方案

Something like this might give you what you need, but I think to be most effective you need to normalise your data a bit, separate the vendors from the asset types then have a join table.
The code below isn't exact so you might need to tweak it properly but you should get the idea.

public void dddl()
 {
     DataTable objDt = new DataTable();
     objDt.Columns.Add("Vendor_Name");
 
     string serial = TextBox8.Text;
     Session["St"] = TextBox8.Text;
     string[] s = serial.Split(',');
     var sbuilder = new StringBuilder();
     sbuilder.AppendLine("Select Vendor_Name from Vendor");
     for (int i = 0; i < s.Length; i++)
     {
        if(i ==0)
             sbuilder.Append(" Where ");
        sbuilder.Append(string.Format("Vendor_Name in (Select Vendor_Name from Vendor where Asset_Type like @a{0})", i));
        if(i < s.Length-1)
         sbuilder.Append( " AND ");
     }
 
    SqlDataAdapter adpt = new SqlDataAdapter(sbuilder.ToString(), con);
    for(int i=0; i< s.Length; i++)
{
   adpt.SelectCommand.Paramters.AddWithValue(string.format("@a{0}", i), s[i]);
}
             DataTable dt = new DataTable();
             adpt.Fill(objDt);
 

         
         objDt = objDt.DefaultView.ToTable(true, "Vendor_Name");
         DropDownList4.DataTextField = "Vendor_Name";
         DropDownList4.DataValueField = "Vendor_Name";
         DropDownList4.DataSource = objDt;
         DropDownList4.DataBind();
 
         con.Close();
 

     
 
 }

这篇关于SQL - 创建查询以返回包含两个字符串的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！