请帮忙解决这个问题 [英] Please help to solve this issue

问题描述

大家好。我有一个数据库供应商，如下所示。我有一个文本框，其中包含逗号分隔的字符串，如SMPS，MotherBoard。我必须选择包含SMPS和主板的供应商名称。我已经为它编写了查询。但我的代码选择那些包含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宗教硬盘

48 ryy电池

48 ryy硬盘

48 ryy摇杆



我的代码如下

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 selects those vendors that contains SMPS or Motherboard. 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 bindddl()
 {
 DataTable objDt = new DataTable();
        objDt.Columns.Add("Vendor_Name");

     string serial = 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() +"'";
            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();        
        
 }

推荐答案

尝试

Try

string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type = '" + s[i].ToString() +"'" OR vendor name = '" + s[i].ToString() +"'"

如果我选择'joystick'作为Asset_Type，那么有3个可能的供应商......

If I choose 'joystick' as the Asset_Type then there are 3 possible vendors ...

Future India
dgd
ryy

所以如果我想让销售'joystick's AND'RAM'的供应商那么以下查询将返回JUST dgd ...我认为你就是这样...

so if I want to get the vendor who sells 'joystick's AND 'RAM' then the following query will return JUST dgd ... which I think is what you are after

SELECT * from vendor
WHERE Asset_Type = 'joystick'
AND Vendor_Name in (
    SELECT Vendor_Name from vendor
    WHERE Asset_Type = 'RAM')

你可以尝试下面的代码而不是for循环 - 因为你只有2个项目可以找到。

you can try below code instead of for loop - because you have only 2 items to findout.

string serial = TextBox8.Text;
string[] s= serial.Split(',');
dim a as string = s[0].tostring
dim b as string = s[1].tostring

string qry = "SELECT DISTINCT Vendor_Name FROM [Vendor] WHERE Asset_Type in( '" + a +"','" + b +"')";
SqlDataAdapter adpt = new SqlDataAdapter(qry, con);

这篇关于请帮忙解决这个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！