Hi Everyone
I'm designing a program with a front end vb.net (2007 Express version) app and a back-end Access 2007 database. I've added the following query to the database dataset (SAUJSDataSet). I have a table which contains information of students and there is a search form where the user can input a variety of search parameters depending on how specific they want their search to be. I used the following query
SELECT * FROM Students WHERE (FirstName LIKE '%' + ? + '%') AND (LastName LIKE '%' + ? + '%') AND (TelNum LIKE '%' + ? + '%') AND (CelNum LIKE '%' + ? + '%') AND (Email LIKE '%' + ? + '%') AND (University LIKE '%' + ? + '%') AND (Degree LIKE '%' + ? + '%')The query is called as follows when the search button is pressed, where fname, lname, tel, cell, email, univ and degree are variables storing the relevant search criteria.
Me.StudentsTableAdapter.FillBySearchStudentG(SaujsDataSet.Students, fname, lname, tel, cell, email, univ, degree)
It works fine if all the fields in the table that are searched have a value in it (i.e, so long as each record has a first name, last name, telnum, celnum, email, university and degree) but the moment one of those fields is blank in the database (e.g. the person doesn't have an email address) it won't include that record in the results even if no email address is entered in the search parameters and the record matches all other search criteria.
How can i modify my code so that it will return records with no data in specific fields so long as there is nothing specified in that field in the search criteria (e.g. search for FirstName = "Sar", LastName = "Sm", telnum, celnum, email, university and degree are left blank, should return the record with FirstName = "Sarah", LastName = "Smith", telnum ="1234567890", celnum = "0987654321", email = "", ... and so on
I hope i've made some sense with what I'm trying to ask. I'd appreciate any help with this.
Thanks
Laura