|
You can make use of check constraint on your table for this. Something like:
Alter YourTableName
check (intCol > 0 and intCol < 100)
Although, you should try and filter out faulty data at the UI only. Why to hit database to know that it is a faulty data? It will make your application slow.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks,
oh yeah.. i forgot about the check 
and you are right to validate the data in the UI. which i am aleady doing it. the reason why i want to do the validation in the database design level, is to make sure that we are adding the right data and also, to prevent the other developers from playing with the database. our database is shared one and we are using it many applications.
thanks for your reply
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
You need to think about what you are doing, what are you going to do when you get a validation fail, raise an error. Then what are you going to do, trap the error in your DAL/BusinessObject and pass it back to the UI.
This is called PROGRAMMING BY ERROR and is the absolute worst validation model you could build.
Put you validation is either the UI or the BusinessObject. Validation does not belong in the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply.
i agree with you. but for your information, i am not waiting to get the error from that database in order to catch it in my DAL/BusinessObject.
i am already doing my validation also in the UI. what i really want to make sure is, the data are inserted right. i dont want to keep any chance for developers to play with the application UI to update the database.
now you make me confused. because i am doing the database design. here is another question: if i consider the following in my database design:
- primary keys
- Forien Keys
- Default Values
- Check Constraint
- indexes
- Database Diagrams
is it bad database design?
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
Yah, I opened my (big mouth) reply before you had replied, got delayed and posted after you.
What you are doing is correct and is the reason constraints are in the database, if you were doing this as the primary validation my argument would apply. The additional elements you raise are all valid and should be used for a well designed database, what you cannot do is expect and error.
With a FK if you decided to try and write the record and trap the error for a FK violation as the primary validation it would be wrong. Having the FK to enforce data integrity is absolutely valid.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Many ways to do validation in database
(1) Constraints
(2) SP
(3) Triggers
|
|
|
|
|
|
Hi,
I have a table where the results are sorted using an "priority" column, eg:
Doc_Value priority
------------------------
aaa 0
xxx 1
bbb 3
ccc 0
aaa 2
I need the SQL results to be in a specific order based on the "priority", but not in ascending or descending order. The order
that I want them in is 1,2,3,0,0,0,0....
Highest priority start with 1 to infinite (9999) number but the lowest priority is 0...
Any suggestion guys? 
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008 
Petaling Jaya MOP (Otai)
|
|
|
|
|
Use a case statement in your order by clause
order by case when priority = 0 then 9999 else sequence end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot its working. +5 
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Introduce a new table called PriorityOrder with something like:
Priority PriorityOrder
-------------------------
0 5
1 1
2 2
3 3
4 4
And then join your tables with this table on the Priority column and then Order by the PriorityOrder column.
This design gives you the flexibility to change the ordering any time by changing the contents of this table instead of changing code.
|
|
|
|
|
Wow what a nasty solution, he simply wants to move the 0 value records to the end of the sort and you want to create and support another table. So every time a new record is created he potentially needs to update the priority table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Priorities are usually limited and may be used in more than one table. This is a generic solution which works best if Priority is stored in its own reference table (a.k.a. Master table), in which case the PriorityOrder column can appear in the same table.
|
|
|
|
|
Hi Shameel,
Thanks for your reply to my problem.
Like Mycroft Holmes said, I just want a simply to move the 0 value records to the end of the sort.
Regards.
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Hi,
Mycroft's solution is elegant and I like the way he presents the solution.
However, there are a few more ways which will accomplish the task, though again I like Mycroft's solution
declare @t table(docvalue varchar(50),priority int)
insert into @t select 'aaa',0 union all select 'xxx', 1 union all select 'bbb', 3 union all
select 'ccc',0 union all select 'aaa',2
Query1:
select docvalue,priority from @t where priority <> 0 group by priority,docvalue
union all
select * from @t where priority = 0
Query 2:
select distinct * from @t where priority <> 0 group by priority,docvalue
union
select * from @t where priority = 0
Output:
docvalue priority
xxx 1
aaa 2
bbb 3
aaa 0
ccc 0
Niladri Biswas
|
|
|
|
|
Hi guys.. Actually i have an web application running on vs2003 it fetches the records from a list of xml file now i want to import all my data that is present in the xml file to sql db(sybase).. can any one guide me as to how can i import the data using c# coding... i googled alot dint get the apt one..I tried using Sqlbulkcopy but that doesnt work with vs2003... and also there are duplicate rows in my xml files...
Thanks in advance...
|
|
|
|
|
Read in your xml file
convert it into a datatable
clean out the duplicates
save each record to the database via a database connection and a sqlcommand
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanx for ur response... since im a fresher do u hav any sample codes or can u mention any tutorials that wil provide me a helping hand... anyways thanks for ur reply...
regards,
Thashif
|
|
|
|
|
Sorry, that's maybe 4 hours work and if I did that for you then how do you expect to become anything but a "fresher"
As a general comment, learn to use the keyboard, typos I can live with, but that crap you used is just horrible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I neva asked u 2 work or complete the task for me.. i only asked u 2 mention any tutorials or sample codes if u know.. wel learn 2 use keyboard ???? what does that mean??? again, crap??? i dint get u....
|
|
|
|
|
<<thashif>> wrote: I neva asked u 2 work or complete the task for me
You are right so take the ideas I gave you one line at a time and feed them into Google, look at the results and you will find lots of articles and tutorial on each of the subjects. Then put them together in a solution.
As for your keyboard use, are you serious, there are 101 keys on most keyboards and you want to use your like a bloody phone. Wake up to yourself and communicate like a professional or at least competent person.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have uninstalled sql server express 2005 and installed sql server express 2008 but now I found that almost all )reporting services) windows services are still exists and that's why the reporting services installation for 2008 was failed because it was using the same instance name.
what can I do?
|
|
|
|
|
I am using SQL server 2008 express on WIndows server 2008 and everything is fine so far but today is the first time for me to use the reprting services..
when I go to the reporting services web page I just get a blank white page with the following text:
server/ReportServer_SSGSQL - /
Microsoft SQL Server Reporting Services Version 10.0.1600.22
what can I do?
Jassim Rahma
|
|
|
|
|
Hi All ,
If have table contains of seven columns or more, and i need to return the douplictate records across only three column.how i can writ this using SQL query ?
for example i have this table :
Clmn1 | Clmn2 | Clmn3 | Clmn4 .......|Clmn7
AJ 35 25 0 19
BQ 10 12 1 8
KS 35 25 3 19
i need to return all records that have duplicat value in this three columns only "Clmn2" , "Clmn3" and "Colmn7" . like this:
Clmn1 | Clmn2 | colmn3 | clmn4 .......|colmn7
AJ 35 25 0 19
Thanks,
KS 35 25 3 19
|
|
|
|
|
Hint: Use GROUP BY clause with HAVING COUNT(*) > 0
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
