|
| |
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
 |
null records - 6/20/2008 11:15:36
my code:
fp_sQry="SELECT * FROM tbl_DOT_MAIN WHERE (Contractors LIKE '::Contractors::%' AND City LIKE '%::City::%' AND State LIKE '%::State::%' And (TypeofWork LIKE '%::TypeofWork::%' OR IsNull(TypeofWork)) AND [D/A_Status] LIKE '%::DS::%' AND [::Operator::]=Yes) ORDER BY Contractors ASC"
Works well except: If my user searches by TypeorWork only and selects "Painting" it will show all of the "Painting" records, and ALL records that have no type of work at all. So it will show the category selected, but any record with a null value as well. It is a drop down field if it matters. Thanks.
|
|
|
|
BeTheBall
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: null records - 6/20/2008 12:30:08
You told it to return records where the typeofwork is null. See the following portion of your SQL:
And (TypeofWork LIKE '%::TypeofWork::%' OR IsNull(TypeofWork))
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: null records - 6/20/2008 13:37:34
Just one more observation on this. You allow the record to be added even when no type of work is known, or selected at the time. Rather than having a null datafield in record, which can cause some unexpected problems in comparisons and searches, since this is appears to be some sort of contactor's database and wherever and by whomever records are input, since there must be some finite number of types of work, if there is no pick to be made, make the choice to fill the field "not stated" That way, you always have something predictable in the field and then, if you want the users to get the records where they absolutely know the skill they want, but also want them to see the the not stated's in case they might also be of interest (we assume that is why you put that in the query to begin with) then the SQL becomes a much simpler OR.
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: null records - 6/22/2008 22:22:45
I think the easiest way to go is with "pd_it_guy" always have something in that field so it is never blank. In my db have the default "not stated" or something like that. This way I do not have to even factor in null issues with my script here.
I will build on this. I do not think I will make my SQL do what I need. Thanks.
| |
|
|
|
rdouglass
Posts: 9186
From: Biddeford, ME USA
Status: offline
|
RE: null records - 6/23/2008 8:32:58
Most DB's have some kind of feature to put something in place of a NULL value. For instance, in SQL Server, I'd try something like this:
fp_sQry="SELECT *, IsNull(TypeOfWork,N'Not Stated') As NewTypeWork FROM tbl_DOT_MAIN WHERE (Contractors LIKE '::Contractors::%' AND City LIKE '%::City::%' AND State LIKE '%::State::%' And (NewTypeWork LIKE '%::TypeofWork::%')) AND [D/A_Status] LIKE '%::DS::%' AND [::Operator::]=Yes) ORDER BY Contractors ASC"
At least that's kinda' how SQL Server would work. The default field value should work as well but sometimes with big DB's NULL is a better choice.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
