SQL - creating query to return values containing two strings

Question

0.00/5 (No votes)

See more:

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.

SQL

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

C#

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

Posted 9-Jul-14 23:42pm

Member 10578683

Updated 10-Jul-14 0:53am

Duncan Edwards Jones

v3

Add a Solution

Comments

Anurag Sinha V 10-Jul-14 5:54am

select * from Table where assetType='SMPS'and assetType='MotherBoard'

Member 10578683 10-Jul-14 6:13am

It is not exactly SMPS And motherboard. It may be any product Name like SMPS, MotherBard, RAM or SMPS,Joystick like this. i m just give an example for understanding

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Accepted Answer · 2014-07-10T00:32:00

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();
 

     
 
 }