Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter

Hello,

I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related.

The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated)

When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.

 

Code:

.

.

<td style="width: 3px">

<asp:TextBox ID="txtName" runat="server"></asp:TextBox>

</td>

.

. 

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"

Width="533px">

<Columns>

<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"

SortExpression="LogId" />

<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />

<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />

<asp:TemplateField>

<ItemTemplate>

<span>

<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>

</ItemTemplate>

</asp:TemplateField>

 

</Columns>

<HeaderStyle BackColor="#608FC8" />

<AlternatingRowStyle BackColor="#FFFFC0" />

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"

SelectCommand="spViewLog" SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

Stored Procedure:

ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )

 

AS

SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')

RETURN

 

I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.

 Any guidance would be much appreciated.

Thank you

Lee