sqldatasource throwing null reference exception

This phenomenon applies to .net 2 when using select commands, haven't tested to see if the behaviour has been changed in later versions or affects insert, update or delete commands.

Having additional named select parameters that aren't used in a given select command can throw a null reference exception when you call the select method / attempt to assign the output of the select to a dataview.

For example, you decide to set five different named select parameters at the beginning of a procedure, you then have a variety of select commands to be executed depending on a set of conditions, but always using a subset of the select parameters specified at the beginning.

Even though you are referencing the parameters by name in your sqlcommmand, somewhere between the interpretation of the sql statement and the database itself something is expecting you to make use of the other parameters you named.

So for example this would fail:

dim s as sqldatasource
s = new sqldatasource s.ConnectionString = ConfigurationManager.ConnectionStrings("conn").ConnectionString

s.SelectParameters.Clear() s.SelectParameters.Add("param1", "hello")
s.SelectParameters.Add("param2", "world")
s.SelectParameters.Add("param3", "testing")
s.SelectParameters.Add("param4", "test")
s.SelectParameters.Add("param5", "testing123")

s.SelectCommand = "SELECT * FROM table1 WHERE [param1]=@param1 AND [param2]=@param2 AND [param3]=@param3"
Dim dv as data.dataview = s.Select(new DataSourceSelectArguments)

Label1.text = dv.Count

This would throw a null reference exception when you try and access the Count property of the dataview, and the reason for this is that the dataview has been set to nothing by the select. If you remove param4 and param5, then everything will work as you expect.

Therefore, if you are using the sqldatasource several times in a procedure, and want to run multiple selects or have the option of running multiple selects against it you must clear the select parameters each time then reassign them (or remove individual parameter that aren't being used in a given selectcommand) before you call a select with a new selectcommand, even if the select parameters are always a subset of the same group.


Posted on Tuesday, October 7, 2014 6:23:00 PM GMT by DonO
Thank you for this. I was using the "@" in the call to Add().

Post a comment