How can I search to see if several columns contain a value?
I have been playing around with this for several weeks, and have really gotten nowhere. I can search the columns in the table and bring up all of the rows that match the search exactly (When only applying this search to a single column), but I would like to search several columns and bring up any rows that include the value in any of the columns. Additionally, I would like to have it so that it wouldn't need to be an exact match (i.e. I search "Fran", and it brings up "Fran", "Frank", "Franklin", etc.)...
How would I go about doing this so that it actually works?
Any help is greatly appreciated.
Thanks,
Here is the code that I am using:
// connect to the database
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"\DB.accdb;Persist Security Info=False;";
// create and open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
// run the query
command.CommandText = "SELECT ID AS [#], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip] FROM Contacts WHERE FirstName = " + textBox1.Text + ";";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
// close the connection
conn.Close();
bindingSource1.DataSource = ds.Tables[0];
// set the grid's data source
dataGridViewSearch1.DataSource = bindingSource1;
// set the size of the dataGridView Columns
this.dataGridViewSearch1.Columns[0].Width = 50;
//this.dataGridViewSearch1.Columns[1].Width =
this.dataGridViewSearch1.Columns[2].Width = 244;
this.dataGridViewSearch1.Columns[3].Width = 50;
this.dataGridViewSearch1.Columns[4].Width = 50;
//this.dataGridViewSearch1.Columns[5].Width =
//this.dataGridViewSearch1.Columns
.Width =
this.dataGridViewSearch1.Columns[7].Width = 75;
this.dataGridViewSearch1.Columns
.Width = 75;
//this.dataGridViewSearch1.Columns[9].Width = 40;
[2479 byte] By [
MLyons10] at [2008-1-10]
O.K., I tried using this command.CommandText, and I get an "OleDBException was Unhandled (No value given for one or more required parameters.) Error.Here is the line that I was trying to use:
command.CommandText = "SELECT ID AS [#], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip] FROM Contacts WHERE FirstName = " + textBox1.Text + " OR FirstName = " + textBox1.Text + " OR LastName = " + textBox1.Text + " OR Address1 = " + textBox1.Text + " OR Address2 = " + textBox1.Text + " OR City = " + textBox1.Text + " OR State = " + textBox1.Text + " OR Zip = " + textBox1.Text + ";";Based on the article I read, it sounded like this would work, but it doesn't. I'm not sure what might be wrong with this...
Any help is greatly appreciated.
Thanks Again,
O.K., I just tried this, because I saw that when using a static string the ' was used around the search term, and got an OleDBException was Unhandled (Data Type Mismatch in Criteria Expression)...
I'm not sure what I'm doing wrong...
command.CommandText = "SELECT ID AS [#], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip] FROM Contacts WHERE FirstName = '" + textBox1.Text + "' OR FirstName = '" + textBox1.Text + "' OR LastName = '" + textBox1.Text + '" OR Address1 = "' + textBox1.Text + '" OR Address2 = '" + textBox1.Text + "' OR City = '" + textBox1.Text + "' OR State = '" + textBox1.Text + "' OR Zip = '" + textBox1.Text + "';";Any help and information is greatly appreciated.
Thanks Again,
O.K., I tried to just get it working with one field (In hopes that I'll be able to add more fields going forward...). So, after reading a couple of articles, I tried this:
command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] FROM 'Contacts' WHERE ID = " + SearchtextBox1.Text + ";";
This gave me this error
leDBException was unhandled Syntax error (missing operator) in query expression
Then I tried this:
command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] WHERE ID = " + SearchtextBox1.Text + ";";
And got this error: OleDBException was unhandled The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
Then I tried this:
command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] WHERE ID = '" + SearchtextBox1.Text + "';";
And got this error again: OleDBException was unhandled The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
I don't understand what I'm doing wrong. Every time I build my application I get a new error... I tried researching these errors to see what the issue might be, but the descriptions are not very helpful...
Any help is greatly appreciated.
Thanks Again,
O.K., I just tried this:
command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] WHERE Company = 'My Company';";
And it doesn't give me any errors, it does everything as expected, but nothing shows up in the dataGridView (Though I KNOW a row meets this search criteria). When I tried specifying the search in this way on the ID field though, I still get the following error for some reason:
OLEDBException was unhandled Data Type Mismatch in criteria expression
Any help and information is greatly appreciated, as I don't understand what the issue is here...
Also, I have a number of check boxes on this form representing the various fields. Is there a way to cause the search to search the fields that are checked? I'm curious, as if I had to write the code for each possibility, there would be a lot of code... lol
Thanks Again,
Hi MLyons10,
I suggest you to write a SQL query statement as follows and check if it works for you.
Code Block
string strSQL = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] FROM contact WHERE ID = @ID";
OleDbCommand cmd = new OleDbCommand(strSQL);
cmd.Parameters.Add("@ID",OleDbType.VarChar).Value = SearchtextBox1.Text;
Hope this helps,
Regards,
Citizens on the earth
 MLyons10 wrote: |
| O.K., I just tried this: command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] WHERE Company = 'My Company';";
And it doesn't give me any errors, it does everything as expected, but nothing shows up in the dataGridView (Though I KNOW a row meets this search criteria). When I tried specifying the search in this way on the ID field though, I still get the following error for some reason: OLEDBException was unhandled Data Type Mismatch in criteria expression Any help and information is greatly appreciated, as I don't understand what the issue is here... Also, I have a number of check boxes on this form representing the various fields. Is there a way to cause the search to search the fields that are checked? I'm curious, as if I had to write the code for each possibility, there would be a lot of code... lol Thanks Again,
| |
command.CommandText = "SELECT ID AS [#], Company AS [Company], FirstName AS [First Name], LastName AS [Last Name], Address1 AS [Address1], Address2 AS [Address2], City AS [City], State AS [State], Zip AS [Zip], WPhone AS [Work Phone] WHERE Company = 'My Company';";
There is something wrong with this SQL query statement. You have lost "From contact" SQL statement before "Where" condition statement.
Regards,
Citizens on the earth
If you would like to search sevral columns including a value such as "fran", you can try to write SQL statement as follows:
SELECT * FROM [Table] Where [Field] like '%fran%'
Hope this helps,
Regards,
Citizens on the earth
Thank you very much for all of your help and very informative responses. As you can see, I tried a lot of things, but just couldn't get this working whatsoever...
Thanks again for your help.
The only remaining question I have is, I can't seem to search fields that contain numbers. When I search a field that would contain a number (I would want this to = rather than like), it doesn't seem to find this field. Additionally, When I try to search based on the ID field, I get a Data Type Mismatch error... All of the examples of this error that I have found have been related to updating the database, not reading data from it... I don't understand this.
Thanks Again,
