MySql - No results from query with WHERE
-
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution? MySql 5.7.17 MySql database on remote server. (and local) Windows C# Form VS 2010 .NET 3.5 private void txtSearchName_TextChanged(object sender, EventArgs e) { string srch = "Strain"; //txtSearchName.Text + "%"; this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch); } VS 2010 DataSet Query SELECT `ID`, `Company`, `Last Name`, `First Name`, 'etc' FROM `contacts` WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1 ORDER BY 'Last Name'
-
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution? MySql 5.7.17 MySql database on remote server. (and local) Windows C# Form VS 2010 .NET 3.5 private void txtSearchName_TextChanged(object sender, EventArgs e) { string srch = "Strain"; //txtSearchName.Text + "%"; this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch); } VS 2010 DataSet Query SELECT `ID`, `Company`, `Last Name`, `First Name`, 'etc' FROM `contacts` WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1 ORDER BY 'Last Name'
Errors = field names with spaces - this will drive you nuts as you now need to bracket the field names with [] try WHERE [Last Name] like @param1 etc..
Never underestimate the power of human stupidity RAH
-
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution? MySql 5.7.17 MySql database on remote server. (and local) Windows C# Form VS 2010 .NET 3.5 private void txtSearchName_TextChanged(object sender, EventArgs e) { string srch = "Strain"; //txtSearchName.Text + "%"; this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch); } VS 2010 DataSet Query SELECT `ID`, `Company`, `Last Name`, `First Name`, 'etc' FROM `contacts` WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1 ORDER BY 'Last Name'
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses
[name with blank]while MySQL`name with blank`. Next, a comparison with LIKE expects a wild card.Straindoes not contain a wild card, useStrain%instead - and make sure that you have data starting with that in one of the columns... -
Errors = field names with spaces - this will drive you nuts as you now need to bracket the field names with [] try WHERE [Last Name] like @param1 etc..
Never underestimate the power of human stupidity RAH
Brackets is only on SQL Server, on MySQL you need to use the backtick ` Both supports the use of double quote " as that is the ANSI standard. You may need to SET sql_mode = 'ANSI_QUOTES' though.
Wrong is evil and must be defeated. - Jeff Ello
-
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses
[name with blank]while MySQL`name with blank`. Next, a comparison with LIKE expects a wild card.Straindoes not contain a wild card, useStrain%instead - and make sure that you have data starting with that in one of the columns...Bernhard, Thank you. I don't know how I missed the backtick. I kept looking for something serious. I discovered it about an hour after posting. It became obvious when I executed queries with Workbench. That is a great tool! Well, I corrected that. As to the wildcard, I had that done properly in the query I was interested in, appending the % to the string before passing to the query. The web is filled with advice on wildcards, most is incorrect. I always get answers quickly here.
-
Brackets is only on SQL Server, on MySQL you need to use the backtick ` Both supports the use of double quote " as that is the ANSI standard. You may need to SET sql_mode = 'ANSI_QUOTES' though.
Wrong is evil and must be defeated. - Jeff Ello
Ah thanks, have not got used to the variations in MySQL yet and I would never find that wrinkle - spaces in field names are NEVER acceptable.
Never underestimate the power of human stupidity RAH