Binding RadMenu to SQL Database

[Flippie]

"The ParentID of the root items must be null (nothing)" From a database perspective will it not be better to change the control to rather use "0" for the ParentID of the root items. According to my understanding one should try to avoide allowing null (nothing) in any data column. One possible solution might be to change the Dataset and replace all value in the ParentID for "0" to null (nothing) but I am not sure if this is possible?

[Atanas Korchev]

Hello Flippie,

It is possible to traverse all datarows and set their parentid column to null before databinding RadMenu. Here is some simple code:

foreach (DataRow row in myDataTable.Rows)
{
    int parentID = (int)row["ParentID"];
    if (parentID == 0)
    {
      row["ParentID"] = null;
    }
}

Alternatively you can modify your database query so it returns NULL instead of 0 (for SQL server):

use a query that returns the expected value (null). For example:

 SELECT ID, Text, IF(ParentID = 0, NULL, ParentID) FROM tblData

Regards,
Albert
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Stewart Ellis]

Hey Albert, I'm trying to create a stored procedure that swaps in the null value on the parentId. Here's the code:

SELECT

menuId, text, url, target, sort, langId, IF(parentID = 0, NULL, parentID)
FROM Menu
WHERE (langId = @langId)
ORDER BY sort

VS2008 is not happy with that syntax though. When I try to compile the query it says:

Incorrect syntax near the Keywork IF.
Incorrect syntax near ','.

I have also tried NullIf and that does not work either. Any ideas

[Jeremy T. Fuller]

Try this:

	SELECT
	menuId,
	text,
	url,
	target,
	sort,
	langId,
	CASE WHEN parentID = 0 THEN NULL
	ELSE parentID
	END
	FROM
	Menu
	WHERE
	(langId = @langId)
	ORDER BY
	sort

[Stewart Ellis]

cool  - that worked...

Thank you