How to Retrieve the Rows where particular Column Data equals NULL

Hi all,

I have a datatable Employee which is containg columns Emp_ID, Emp_Name, Manager_ID

I want to retrieve all the rows whose Manager_ID=NULL

I wirte the query select * from Employee where Manager_ID=NULL

but it is not working. How to do this. Please help me.

with regards

Ravinder Reddy.K

You have to use IS operator on NULL..

select * from Employee where Manager_ID is NULL

remember,

NULL = NULL => always False

NULL IS NULL = > True

|||

>NULL = NULL => always False

Not always... this is dependent on the setting of ANSI_NULLS. Try the following as an example:

SET ANSI_NULLS ON
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'No'


SET ANSI_NULLS OFF
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'Yes'

Chris

||| Forget the Settings BUDDY

How to retrieve data as XML from SQL database

Hi,

I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:

User searches for all entries with Forename = John; Results are listed in a gridview as per expected.

User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format

<records>
<record>
<Forename>John</Forename>
<Surname>Smith</Surname>
<Email>j.smith@.blah.com</Email>
<Ext>1234</Ext>
<DeptList>History</DeptList>
</record>
</records>

I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:

CREATE

PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML

@.Forename

varchar(50),

@.Surname

varchar(50),

@.Email

varchar(50),

@.Ext

varchar(4),

@.DeptList

varchar(50)

AS

SELECT

Forename, Surname, Email, Ext, DeptList

FROM

dbo.record

WHERE

ForenameLIKECOALESCE(@.Forename,Forename)AND

Surname

LIKECOALESCE(@.Surname,Surname)AND

Email

LIKECOALESCE(@.Email,Email)AND

Ext

LIKECOALESCE(@.Ext,Ext)AND

DeptList

LIKECOALESCE(@.DeptList,DeptList)

FOR

XMLAUTO,ELEMENTS

If someone could be kind enough to help me out with this, I'd be really grateful.

Many thanks,

Tom

Hi tomosap,

Yes, this is a good way to get that.

Another way is to fill everything to run the query and fill data into a DataSet. You will be able to get the DataSet's data in XML format by calling DataSet.WriteXml() method.

Both these methods are good.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Hi,

Thanks for your reply - I'm afraid that my needs have slightly changed since this was posted. I now have an XML file which I transform using XSL so it displays all the data in a gridview. What I want to do is to have a search form so that I can search the data that's in the gridview. I.e.

Name: [INPUT]

Results in Gridview...

User enters name in input, clicks search, then all matches in Name column on the gridview is displayed. I've successfully managed to do this with a basic SQL database using the object source control and passing paramaters, but I don't know how to do this when the data source is XML.

All help would be appreciated.

Thanks,

Tom