problem with MSSQL2000 or VS2005 ?

[victorashi]

is there a way that the MSSQL2000 doesn`t show a column if it`s value in it is NULL, or a way in VS2005, for making a website, that doesn`t let me to see the column if it is NULL.
it`s unproffesional to see on a website with columns with NULL values. Please help . I tried the hole afternoon. I`m afraid i won`t sleep !

 

[SQLDenis]

lookup COALESCE and ISNULL in Books On Line

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[victorashi]

i want to select one person from a table by name, i have problems with marital status, because a married person has no data in the divorced_date column and widdowed_date colmn, so i don`t want this columns shown .
can you help me in this matter??

 

[victorashi]

don`t imagine it is the simple select statement that i am using wrong, the fact is , i want to select a person through a procedure :
ALTER PROCEDURE DetailesEmployee
@nr int
AS
BEGIN
SELECT dbo.table.ID,
dbo.table.nume,
dbo.table.datebirth,
dbo.table.plcbirth,
dbo.table.occupation,
dbo.table.marital_sts,
dbo.table.children,
dbo.table.PO,
dbo.table.adress_RO,
dbo.table.adress_DE,
dbo.table.tata,
dbo.table.mama,
dbo.viza.date1,
dbo.viza.date2,
dbo.passport.nr_passport,
dbo.passport.date1,
dbo.pasaport.date2,
dbo.work_perm.nr_perm,
dbo.work_perm.DATE1,
dbo.work_perm.DATE2,
dbo.casatorit.wife,
dbo.married.datebithw,
dbo.married.datem,
dbo.married.placeofm,
dbo.divorced.divorced_date,
dbo.widdowed.widdow_date
from table
INNER JOIN viza ON table.ID = viza.ID
INNER JOIN work_permit ON table.id = work_permit.id
INNER JOIN passport ON table.ID = passport.ID
LEFT OUTER JOIN married ON table. ID= married.ID
LEFT OUTER JOIN divorced ON table.ID = divorced.ID
LEFT OUTER JOIN widdowed ON table.ID = widdowed.ID
where table.ID = @nr
END
This causes me trouble !

 

[gmmastros]

As Denis suggested, lookup IsNull and Coalesce to find your answer.

ALTER      PROCEDURE DetailesEmployee
@nr int 
AS
BEGIN
SELECT dbo.table.ID, 
dbo.table.nume, 
dbo.table.datebirth,
dbo.table.plcbirth, 
dbo.table.occupation,
dbo.table.marital_sts,
dbo.table.children, 
dbo.table.PO, 
dbo.table.adress_RO, 
dbo.table.adress_DE, 
dbo.table.tata,
dbo.table.mama, 
dbo.viza.date1, 
dbo.viza.date2,
dbo.passport.nr_passport, 
dbo.passport.date1,
dbo.pasaport.date2, 
dbo.work_perm.nr_perm,
dbo.work_perm.DATE1, 
dbo.work_perm.DATE2, 
dbo.casatorit.wife, 
[!]IsNull([/!]dbo.married.datebithw[!], '') As datebithw[/!],
IsNull(dbo.married.datem, '') As datem,
dbo.married.placeofm, 
dbo.divorced.divorced_date, 
dbo.widdowed.widdow_date 
from table
INNER JOIN viza ON table.ID = viza.ID 
INNER JOIN work_permit ON table.id = work_permit.id 
INNER JOIN passport ON table.ID = passport.ID 
LEFT OUTER JOIN married ON table. ID= married.ID
LEFT OUTER JOIN divorced ON table.ID = divorced.ID
LEFT OUTER JOIN widdowed ON table.ID = widdowed.ID
where table.ID = @nr

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[victorashi]

THIS IS THE SOLUTION TO THE PROBLEM , I WANTED THET SOME COLLUMNS WON`T BE SHOWN , IN CASE THE PERSON IS MARRIED , THE WIDDOW TABLE AND THE DIVORCED WON`T BE SHOWN, VICEVERSA FOR THE DIVORCED( THE MARRIED , AND THE WIDDOWED ) WON`T BE SHOWN:
ALTER PROCEDURE DetaliiAngajat
@nr int
AS
declare @b int
BEGIN
set @b= (select st_civ from dbo.tabel_de_baza where nr_crt=@nr)
if @b=1
SELECT dbo.tabel_de_baza.nr_crt, dbo.tabel_de_baza.nume_prenume, dbo.tabel_de_baza.data_nasterii, dbo.tabel_de_baza.locul_nasterii,
dbo.tabel_de_baza.meseria, dbo.tabel_de_baza.st_civ, dbo.tabel_de_baza.copii, dbo.tabel_de_baza.judet, dbo.tabel_de_baza.adresa_RO,
dbo.tabel_de_baza.adresa_DE, dbo.tabel_de_baza.prenume_tata, dbo.tabel_de_baza.prenume_mama, dbo.vize.data_eliberarii_vizei,
dbo.vize.data_expirarii_vizei, dbo.pasaport.nr_pasaport, dbo.pasaport.data_eliberarii_pasap, dbo.pasaport.data_expirarii_pasap,
dbo.permis_munc.nr_perm, dbo.permis_munc.data_eliberarii_perm, dbo.permis_munc.data_expirarii_perm
from tabel_de_baza
INNER JOIN vize ON tabel_de_baza.nr_crt = vize.nr_crt
INNER JOIN permis_munc ON tabel_de_baza.nr_crt = permis_munc.nr_crt
INNER JOIN pasaport ON tabel_de_baza.nr_crt = pasaport.nr_crt
LEFT OUTER JOIN casatorit ON tabel_de_baza.nr_crt = casatorit.nr_crt
LEFT OUTER JOIN vaduv ON tabel_de_baza.nr_crt = vaduv.nr_crt
LEFT OUTER JOIN divortat ON tabel_de_baza.nr_crt = divortat.nr_crt
where tabel_de_baza.nr_crt = @nr
if @b=2
SELECT dbo.tabel_de_baza.nr_crt, dbo.tabel_de_baza.nume_prenume, dbo.tabel_de_baza.data_nasterii, dbo.tabel_de_baza.locul_nasterii,
dbo.tabel_de_baza.meseria, dbo.tabel_de_baza.st_civ, dbo.tabel_de_baza.copii, dbo.tabel_de_baza.judet, dbo.tabel_de_baza.adresa_RO,
dbo.tabel_de_baza.adresa_DE, dbo.tabel_de_baza.prenume_tata, dbo.tabel_de_baza.prenume_mama, dbo.vize.data_eliberarii_vizei,
dbo.vize.data_expirarii_vizei, dbo.pasaport.nr_pasaport, dbo.pasaport.data_eliberarii_pasap, dbo.pasaport.data_expirarii_pasap,
dbo.permis_munc.nr_perm, dbo.permis_munc.data_eliberarii_perm, dbo.permis_munc.data_expirarii_perm,
dbo.casatorit.pren_sotie,dbo.casatorit.dat_nas_so,dbo.casatorit.data_casat,dbo.casatorit.locul_casat
from tabel_de_baza
INNER JOIN vize ON tabel_de_baza.nr_crt = vize.nr_crt
INNER JOIN permis_munc ON tabel_de_baza.nr_crt = permis_munc.nr_crt
INNER JOIN pasaport ON tabel_de_baza.nr_crt = pasaport.nr_crt
LEFT OUTER JOIN casatorit ON tabel_de_baza.nr_crt = casatorit.nr_crt
LEFT OUTER JOIN vaduv ON tabel_de_baza.nr_crt = vaduv.nr_crt
LEFT OUTER JOIN divortat ON tabel_de_baza.nr_crt = divortat.nr_crt
where tabel_de_baza.nr_crt = @nr
if @b=3
SELECT dbo.tabel_de_baza.nr_crt, dbo.tabel_de_baza.nume_prenume, dbo.tabel_de_baza.data_nasterii, dbo.tabel_de_baza.locul_nasterii,
dbo.tabel_de_baza.meseria, dbo.tabel_de_baza.st_civ, dbo.tabel_de_baza.copii, dbo.tabel_de_baza.judet, dbo.tabel_de_baza.adresa_RO,
dbo.tabel_de_baza.adresa_DE, dbo.tabel_de_baza.prenume_tata, dbo.tabel_de_baza.prenume_mama, dbo.vize.data_eliberarii_vizei,
dbo.vize.data_expirarii_vizei, dbo.pasaport.nr_pasaport, dbo.pasaport.data_eliberarii_pasap, dbo.pasaport.data_expirarii_pasap,
dbo.permis_munc.nr_perm, dbo.permis_munc.data_eliberarii_perm, dbo.permis_munc.data_expirarii_perm,
dbo.divortat.data_divort
from tabel_de_baza
INNER JOIN vize ON tabel_de_baza.nr_crt = vize.nr_crt
INNER JOIN permis_munc ON tabel_de_baza.nr_crt = permis_munc.nr_crt
INNER JOIN pasaport ON tabel_de_baza.nr_crt = pasaport.nr_crt
LEFT OUTER JOIN casatorit ON tabel_de_baza.nr_crt = casatorit.nr_crt
LEFT OUTER JOIN vaduv ON tabel_de_baza.nr_crt = vaduv.nr_crt
LEFT OUTER JOIN divortat ON tabel_de_baza.nr_crt = divortat.nr_crt
where tabel_de_baza.nr_crt = @nr
if @b=4
SELECT dbo.tabel_de_baza.nr_crt, dbo.tabel_de_baza.nume_prenume, dbo.tabel_de_baza.data_nasterii, dbo.tabel_de_baza.locul_nasterii,
dbo.tabel_de_baza.meseria, dbo.tabel_de_baza.st_civ, dbo.tabel_de_baza.copii, dbo.tabel_de_baza.judet, dbo.tabel_de_baza.adresa_RO,
dbo.tabel_de_baza.adresa_DE, dbo.tabel_de_baza.prenume_tata, dbo.tabel_de_baza.prenume_mama, dbo.vize.data_eliberarii_vizei,
dbo.vize.data_expirarii_vizei, dbo.pasaport.nr_pasaport, dbo.pasaport.data_eliberarii_pasap, dbo.pasaport.data_expirarii_pasap,
dbo.permis_munc.nr_perm, dbo.permis_munc.data_eliberarii_perm, dbo.permis_munc.data_expirarii_perm,
dbo.vaduv.data_deces
from tabel_de_baza
INNER JOIN vize ON tabel_de_baza.nr_crt = vize.nr_crt
INNER JOIN permis_munc ON tabel_de_baza.nr_crt = permis_munc.nr_crt
INNER JOIN pasaport ON tabel_de_baza.nr_crt = pasaport.nr_crt
LEFT OUTER JOIN casatorit ON tabel_de_baza.nr_crt = casatorit.nr_crt
LEFT OUTER JOIN vaduv ON tabel_de_baza.nr_crt = vaduv.nr_crt
LEFT OUTER JOIN divortat ON tabel_de_baza.nr_crt = divortat.nr_crt
where tabel_de_baza.nr_crt = @nr
END

 

[victorashi]

BUT THIS DOES`NT WORK PROPERLY WITH VS2005 ASP, ALTHOUGH THE GRIDWIEV HAS AUTO-GENERATED FIELDS