null records (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

travismp -> 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 -> 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))



pd_it_guy -> 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 -> 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 -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625