C# not liking SQL JOINS

Question

I am trying to compare and build a table comparing two columns(Using LIKE). In two classes(Win32_Printer.name and Win32_PrinterDriver.name).

The only issue is I cannot run any kind of JOIN

string query = string.Format("SELECT * 
FROM Win32_Printer INNER JOIN Win32_PrinterDriver
ON Win32_Printer.Name = Win32_PrinterDriver.Name
WHERE Win32_Printer.Name LIKE '%', Win32_PrinterDriver.Name, '%'");

After this is run I get thrown an error:

You are missing `OR` in between `WHERE Win32_Printer.Name LIKE '%', Win32_PrinterDriver.Name, '%'"`, perhaps `WHERE Win32_Printer.Name LIKE '%' OR Win32_PrinterDriver.Name LIKE '%'"` — Abhik Chakraborty, Feb 26 '15 at 11:18
Wouldn't string concatenation be done with a `+`, not a `,`? — spender, Feb 26 '15 at 11:20
No neither of those work, I am still getting the same error. When I run `SELECT * FROM Win32_Printer INNER JOIN Win32_PrinterDriver ON Win32_Printer.Name = Win32_PrinterDriver.Name` It also doesn't work — nrd22, Feb 26 '15 at 11:26
Can you please Debug and see what the string query contains after your String.Format ? — Paul Weiland, Feb 26 '15 at 11:41
`SELECT * FROM Win32_Printer INNER JOIN Win32_PrinterDriver ON Win32_Printer.Name = Win32_PrinterDriver.Name WHERE Win32_Printer.Name LIKE '%' OR Win32_PrinterDriver.Name LIKE '%'` — nrd22, Feb 26 '15 at 11:47
Aside from the OR you added later in your code I see no difference between the string you want to format and the formated string, so the string.format actually does do nothing for you. I think in the formated string you want the actual name of Win32_Printer.Name and Win32_PrinterDriver.Name, don`t you ? — Paul Weiland, Feb 26 '15 at 12:00
This might produce the result you wanted: string.Format("SELECT * FROM Win32_Printer INNER JOIN Win32_PrinterDriver ON {0} = {1} WHERE {0} LIKE '%', {1}, '%'", Win32_Printer.Name, Win32_PrinterDriver.Name); String format will replace {0} with the actual string saved in the property Win32_Printer.Name and {1} with the actual string saved in Win32_PrinterDriver.Name — Paul Weiland, Feb 26 '15 at 12:06
I get the error win32_printer does not exist in the current context any idea how to fix that? — nrd22, Feb 26 '15 at 12:09
Sorry, I'm confused now. What is Win32_Printer ? Is that a class inside C# or is it a table inside the database ? If it is a table inside the database what I just told you is wrong. If it is a class in C# ofcourse you need to create an object of this class and then use variable-name where the object of the class is referenced instead of the class name itself. For example if you create an object of Win32_Printer as following: Win32_Printer myPrinter = new Win32_Printer(); you need to use myPrinter.Name inside your string.format instead of Win32_Printer.Name. — Paul Weiland, Feb 26 '15 at 12:13
So it is a class that has the property Name ? Do you already have an object of this class created ? If so see my edit in the above comment and try replacing Win32_Printer.Name with nameOfYourVariable.Name and the same for Win32.PrinterDriver. — Paul Weiland, Feb 26 '15 at 12:17
Ok I got it to work. What I did was write a for loop for when the final column appears do a loop which returns `SELECT * from Win32_PrinterDriver WHERE Name LIKE '%" + name + "%'` — nrd22, Feb 26 '15 at 12:23

score 0 · Answer 1 · answered Feb 26 '15 at 11:32

0

I think you repeat the join condition. You can go with just

SELECT * 
FROM Win32_Printer 
  INNER JOIN Win32_PrinterDriver ON Win32_Printer.Name = Win32_PrinterDriver.Name

or if you want compare with like:

SELECT * 
FROM Win32_Printer P
  INNER JOIN Win32_PrinterDriver D ON D.Name LIKE '%'+P.Name+'%'

answered Feb 26 '15 at 11:32

less

699
6
25

As stated in a previous response this doesn't work either and an error is thrown. And the second one does the same. – nrd22 Feb 26 '15 at 11:34
I don't think you got an sql problem then. – less Feb 26 '15 at 11:35
If I select each table it works fine `SELECT * FROM Win32_Printer` – nrd22 Feb 26 '15 at 11:36
Can you provide SQL table definitions? – less Feb 26 '15 at 11:37
Win32_Printer and Win32_PrinterDriver are C# Classes – nrd22 Feb 26 '15 at 11:38
Then I don't understand what you're doing, sorry – less Feb 26 '15 at 11:41
It's okay, very strange – nrd22 Feb 26 '15 at 11:57
maybe this could help: http://stackoverflow.com/questions/3206846/how-to-join-in-a-wmi-query-wql – less Feb 26 '15 at 11:57

score 0 · Answer 2 · answered Feb 26 '15 at 12:27

        string query = string.Format("SELECT * FROM Win32_Printer")
        try
        {
            ManagementObjectSearcher searcher = new ManagementObjectSearcher(query);

        ManagementObjectCollection coll = searcher.Get();
        string name = "";
        foreach (ManagementObject printer in coll)
        {
            foreach (PropertyData property in printer.Properties)
            {

                if (property.Name == "Name")
                {
                    name = property.Value.ToString();
                }
                rtxLog.AppendText(property.Name + "-->" + property.Value + "\n");
                if (property.Name == "WorkOffline")
                {
                    string querydriver = string.Format("SELECT * from Win32_PrinterDriver WHERE Name LIKE '%" + name + "%'");
                    ManagementObjectSearcher searcherdriver = new ManagementObjectSearcher(querydriver);
                    ManagementObjectCollection colldriver = searcherdriver.Get();

                    foreach (ManagementObject driver in colldriver)
                    {
                        foreach (PropertyData propertydriver in driver.Properties)
                        {
                            rtxLog.AppendText(propertydriver.Name + "-->" + propertydriver.Value + "\n");
                        }
                    }
                }
                rtxNameLog.AppendText(name + "\n");
            }
            rtxLog.AppendText("\n\n\n\n\n\n\n\n\n\n\n");
        }

C# not liking SQL JOINS

2 Answers2