StoreProcedure, Databind and null

[Henrik Tegman]

Hi,

I have a problem with my RadGrid. I have a simple RadGrid that is bound to a StoreProcedure this SP takes 4 arguments all of them can be null.

I have bound the DataScourse Select to 4 controls (4 textboxes). The thought here is that the user will enter values into the textboxes and then he press a "search" button and the grid shows the data that the SP gets.

An example would be that the user inputs
1 in the 1st box
nothing in the 2nd box
3 in the 3rd box
nothing in the 4th box

In this case I want it to run the SP with the values (1,null,3,null)
This is all very simple and when I test it in the "ConfigureDataSource"-tutorial that the RadGrid has and I test the query it all works great, I do the same inputs leave 2 and 4 with nothing and inputs 1 and 3 in the others.
But when I then try this "live" it doesn't work, the grid doesn't even load up and give no records nothing happends.

The only thing that I can think of since I've been working with SP before is that I can't simply use a empty textbox or a null i need to use a DBNull.value otherwise the SP won't work. So how can I send in DBNull.value to the SP?

I've tried some things like
BoxNumber.Text = DBNull.value
(BoxNumber is the name of one of the textboxes), but it still doesn't work.

The "search" button simply does a

RadGrid1.DataBind();

And since its bound to the controls I would hope that a empty control would give null, but it doesn't. It seems like a very simply and small problem yet I have been stuck with it for a long time now, please help me out.

Thanks in advance.

[Radoslav]

Hi Henrik,

You could try changing the stored procedure definition to get four parameters from nvarchar() type. If some parameter is empty string ("") then insert into the database NULL value.
Also you could check out the following articles which elaborate on the same matter:
http://www.eggheadcafe.com/community/aspnet/2/10136856/insert-null-into-table.aspx
http://stackoverflow.com/questions/1207404/how-to-pass-a-null-variable-to-a-sql-stored-procedure-from-c-net-code

I hope this helps.

All the best,
Radoslav
the Telerik team

[Henrik Tegman]

Thank you for the help.

I fixed this on my own yesterday my problem was that I didn't have

ConvertEmptyStringToNull

 

="true"

 

also forgot to add:

CancelSelectOnNullParameter

 

="false"

 

to the SqlDataSource.

Thanks anyway for the help :)

[Radoslav]

Hi Henrik,

I am glad you solved the problem. In case you experience any further problems, do not hesitate to contact us again.

Regards,
Radoslav
the Telerik team

[Mike]

Henrik,

I am working on something similar to what you did...I have a text box and a button separate from the grid.  When the user enters there search criteria they then press the button which then should load the grid.

Before doing the databinding, I need to run some logic in code behind to determine which of two stored procs I will be using to do the binding.  I noticed you used:  RadGrid1.Databind()... are you using "NeedDataSource" event as well?  I'm confused on how to or if I need to use the "NeedDataSource".

Thanks for any help and insight.

Mike

[Radoslav]

Hi Mike,

The NeedDataSource event is used when the advanced data-binding approach is used. The RadGrid fires the NeedDataSource event each time it needs to be bound to a data source. The other way to bind the RadGrid is via declarative data source. However you could not merge the described approaches, you could use the only one of them to bind the RadGrid to data.  So if you have data source control which gets data from the database via StoredProcedure you could not use the NeedDataSource event.

I hope this helps.

Kind regards,
Radoslav
the Telerik team