Last post Aug 03, 2011 09:55 AM by Nenson
Aug 02, 2011 06:33 PM|Nenson|LINK
Hello! I need to search my SQL database (Dataset, Gridview) for numbers. Is it possible that the user enters only number/s (that does not have to be a specific date) and it returns the dates that contains it? Ive tryed many ways but there is no complete
one that will work 100% correct. Maybe an ArrayList would help? Pls. help, ty!
Aug 03, 2011 05:16 AM|Niikola|LINK
I've seen simmilar question you've already made somwhere else.
You have to have precise requirement definition first.
Do you need to search in specific table/column or you want to find it in any column in one table or anywher in database?
Do you want to search in date/datetime columns or also in numeric and character data types?
Do you want to find it against specific date format (mdy, dmy, ymd) or against any of them. This also includes more specific formats as listed in CAST and CONVERT specification for date related data types.
Do you want to have full match against day, month or year part or you want to find partial matches (eg 12 should find not only day and month but also 2012, 1912, any date of 13 century and dates between 1120 and 1129. Do you want to search for combined values,
let's say search for 23 coud match 20031231). What should happen if you search for 199803, 3199, 54, 427 or 5419?
What does mean search for input value of 2? What is result you expect if you have dates like '2005-03-01', '1999-03-21', 'February 18, 1998'
As you can see, you have to define requirements precisely if you want to get usefull answer. In many cases, fuly clarified requirements could lead you to solution.
And at the end, it would be very helpfull to anyone willing to help you if you provide sample data with expected output for given parameters (at least 2 different parameters and related results because one example only could be very missleading).
P.S. I do not understand "that does not have to be a specific date" part.
Aug 03, 2011 08:42 AM|Nenson|LINK
I have a program for subscribing on seminars. The user can choose a seminar on a GridView list. That Gridview has the name of the seminar, description, date when classes begin and the name of lecturer.
That data is stored into an SQL base, table Seminar. DataType of name, description and lecturer is VarChar except the date witch is Date(i can change this into DateTime if it would help).
The user has an option to search this DataSet/Gridview data through an TextBox, witch then on a button click passes through all the data in table Seminar. So i would like that it searches against any of them. Ive tryed CAST AND CONVERT but they give me the
same result as my LIKE search. When i enter only the day, only the month and only the year the result is correct but when i combine those it gives no result.
I have a search that gives correct results for name, description and lecturer but the problem comes with Date DataType. "that does not have to be a specific date" means that the user can write e.g. "0203" or "20 05" or "200611" and it shoud give him the
dates that have those numbers in them.
So the user is free to write what ever he wants and then if the numbers match he gets the result.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM tblSeminar WHERE ([naziv] LIKE ('%" + txtSearch.Text + "%')
OR [opis] LIKE ('%" + txtSearch.Text + "%')
OR [datum] LIKE ('%" + txtSearch.Text + "%')
OR [predavac] LIKE ('%" + txtSearch.Text + "%'))", sqlcon);
DataSet ds = new DataSet(); da.Fill(ds); da.Dispose(); sqlcon.Close(); GridView1.DataSourceID = null; GridView1.DataSource = ds; GridView1.DataBind();
This code is the best one that i have. But it works with day, month and year individually.
It wont work when i enter the full date, and i would like that it works with any number and full dates.
I will try now with the ArrayList then post you back. Thanks!
Aug 03, 2011 09:37 AM|Niikola|LINK
Pozdrav nensone :)
I have a search that gives correct results for name, description and lecturer but the problem comes with Date DataType. "that does not have to be a specific date" means that the user can write e.g. "0203" or "20 05" or "200611" and it shoud give him the dates
that have those numbers in them.
The basic problem is, as I've said, in requirement definition.
For the examples above there could be different meanings, like:
- if entered as integer 0203 will be 203
- it could mean february 3 or March 2 or year 203 (eg. for 2012 could mean December 20 or year 2012)
- 200611 could be treated as June 20, 2011 (ddmmyy) or November 2006 (yyyymm) or June 11 2020 (yymmdd)
- if you allow years to be written with less than 4 digits (eg 99 means year 1999 or 12 means 2012) or days and months to be entered with less than 2 digits (eg. 2 for Febryuary) you will have even more problems as 11 could mean Year 2011 or November any year,
or 11 day of any month/year or (as a nightmare option) even including January 1, January 2001, January 2011, etc.)
- Example '20 05' is not numeric as it have space between numbers. If you go with text field alowing numerics and spaces it could go even worse.
My suggestion after all would be either:
- use textbox with mask (something like '____-__-__' allowing end user to enter numbers to the position it wants ( '2011-__-__' would mean year 2011 while '____-06-12' would mean June 12 any year)
- use define 3 different text fields (year/month/day)
In both cases you will know exactly what user wants and you can create efficient filter.
P.S. My suggestion is to use datepart function against the date field instead of convert. If user enters year you can even use Where date >= @firstdate and date<=@lastdate. Of course, you have to calculate @firstdate and @lastdate before based on the conditions
passed (it will work for year only and year/month combinataions; for year/month/date you can directly use = operator). And yes, that means playing with dynamic sql inside stored procedure, which is not bad per se. You just have to do it properly (eg. without
embeding values inside the generated code. see sp_executesql system stored procedure.)
P.S.2. Instead of building dynamic sql inside application, do it in stored procedure, and DO NOT embed values inside the sql string like:
OR [opis] LIKE ('%" + txtSearch.Text + "%')
This is very dangerous and it open all possible doors to SQL injection attacks. If you can not use stored procedures, check how you can build sql query with parameters (I do believe it can be done, but I'm not sure)
Aug 03, 2011 09:55 AM|Nenson|LINK
Thank you man! Ill see what i can do. I have an INSERT query with Parameters for sign-in info. Živio!