Please help to solve this issue

Question

1.00/5 (1 vote)

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 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

C#

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

Posted 4-Jul-14 23:03pm

Member 10578683

Updated 4-Jul-14 23:51pm

Siva Hyderabad

v2

Add a Solution

3 solutions

Solution 1

Try

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

Posted 4-Jul-14 23:29pm

Abhinav S

Comments

Member 10578683 5-Jul-14 5:45am

It is not correctly working. Suppose in textbox RAM,joystick there then it should select the vendor name dgd but instead of that it is selecting Future India, dgd,rry

Solution 2

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

C#

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

Posted 5-Jul-14 0:47am

purvivani

Updated 5-Jul-14 0:59am

v2

Comments

Member 10578683 5-Jul-14 6:55am

I have not the constant asset type. It varies. The string may contain 3 items or 4 items. how will i do in that case

purvivani 5-Jul-14 7:17am

for that you can write code like this
string s1 = string.Empty;
for (int i = 0; i < s.Length; i++)
{
string s1 ="," + '" & s[i].ToString() &"' " ;

}
if (s1.Length > 0)
{
if ( s1.StartsWith (",") )
{
s1 = mid (s1,2)
}
}

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

Member 10578683 5-Jul-14 7:28am

showing error in the line mid(s1,2)

purvivani 5-Jul-14 7:35am

which is error?

Member 10578683 5-Jul-14 7:37am

It is showing error mid does not exis

Member 10578683 5-Jul-14 7:44am

I think you are giving the VB code. I m doing in c#

CHill60 5-Jul-14 10:01am

I don't think he's giving you anything useful to be honest - none of it would compile

purvivani 5-Jul-14 11:48am

in C# u can do as
s1 == Strings.Mid(s1, 2)

Member 10578683 7-Jul-14 1:42am

It is not working. It is showing String does not contain a definition of Mid

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)

CHill60 · Accepted Answer · 2014-07-05T04:19:00

Solution 3

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

Future India
dgd
ryy

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

SQL

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

Posted 5-Jul-14 4:19am

CHill60

Comments

Member 10578683 7-Jul-14 1:33am

Yes you are right but u have written this code for particular assets such as joystick and RAM. but in general how will i do it for any asset type

Member 10578683 7-Jul-14 2:50am

Thanks Chill. I modified your code a little and it is working fine. Thank you Very much.

CHill60 8-Jul-14 5:05am

Good! By the way I deliberately chose assets that could have multiple matches with one vendor but where the subquery would narrow it down further, to demonstrate the principle clearly...those were the first 2 that caught my eye!