I have the following T-SQL in a SelectCommand
:
SELECT h.Business,
hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE
(h.Business like '%' + @business + '%' and h.Business is not null)
and
(hrl.frn = @frn and hrl.frn is not null)
business
and frn
are tied to control parameters and it should return data even if one or both is left blank, but if I put in data just for frn
for example, it does not return anything. I think my T-SQL is not doing the right thing and I am also not sure if I am handling the like
correctly.
if both textboxes are left empty, it should return all data. If frn
is entered, but business
is left blank, it should only return data related to that frn
. If business
if entered, but frn
is left blank, it should return all matches like
business
. If both are entered, it should return data only matching the frn
and the business
.
Also, I am not sure if doing the and is not null
is actually necessary.
protected void btnSearch_Click(object sender, EventArgs e)
{
if (txtFRN.Text == "")
frn = null;
if (txtBusiness.Text == "")
business = null;
sqlDsMaster.SelectParameters[frn].DefaultValue = frn;
sqlDsMaster.SelectParameters[business].DefaultValue = business;
sqlDsMaster.DataBind();
}
The above throws an "Object Reference not set to an instance" when it hits this line:
sqlDsMaster.SelectParameters[frn].DefaultValue = frn;
frn
and business
are properties.
Here is the SearchMaster
stored procedure:
CREAETE PROCEDURE SearchMaster
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT h.Business,
hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE (@business IS NULL OR h.Business like '%' + @business + '%')
AND (@frn IS NULL OR hrl.frn = @frn)
Here is the SearchDetails
stored procedure:
CREATE PROCEDURE SearchDetails
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT hrl.call
FROM registration hrl
INNER JOIN holder h ON h.call = hrl.call
WHERE (@business IS NULL OR h.Business LIKE '%' + @business + '%')
AND (@frn IS NULL OR hrl.frn = @frn)
Here is the SqlDataSource
for the SearchMaster
procedure:
<asp:SqlDataSource ID="sqlDsDetails"
runat="server"
ConnectionString="<%$ ConnectionStrings:cnxString %>
SelectCommandType="StoredProcedure"
SelectCommand="SearchMaster">
<SelectParameters>
<asp:ControlParameter Name="business" ControlID="txtBusiness"
Type="String" PropertyName="Text"
ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="frn" ControlID="txtFRN"
Type="String" PropertyName="Text"
ConvertEmptyStringToNull="true"/>
</SelectParameters>
</asp:SqlDataSource>
Here is the SqlDataSource
for the SearchDetails
procedure:
<asp:SqlDataSource ID="sqlDsDetails"
runat="server"
ConnectionString="<%$ ConnectionStrings:cnxString %>
SelectCommandType="StoredProcedure"
SelectCommand="SearchDetails">
<SelectParameters>
<asp:Parameter Name="frn" Type="String" DefaultValue=""
ConvertEmptyStringToNull="true" />
<asp:Parameter Name="business" Type="String" DefaultValue=""
ConvertEmptyStringToNull="true" />
</SelectParameters>
</asp:SqlDataSource>
Here is the button click that binds the SqlDsMaster
:
protected void btnSearch_Click(object sender, EventArgs e)
{
sqlDsMaster.DataBind();
}
Here is the gvMaster_RowCreated
that creates the rows for the details:
protected void gvMaster_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlDataSource ctrl =
e.Row.FindControl("sqlDsDetails") as SqlDataSource;
if (ctrl != null && e.Row.DataItem != null)
{
ctrl.SelectParameters["frn"].DefaultValue =
((DataRowView)e.Row.DataItem)["frn"].ToString();
ctrl.SelectParameters["business"].DefaultValue =
((DataRowView)e.Row.DataItem)["business"].ToString();
}
}
}
SearchMaster
and SearchDetails
both work if I run it through SQL Server Management Studio and it works if I enter both data for business
and frn
, but if I enter just one, no data is returned. Are the parameters set up correctly? Also, if I am initializing the parameters to null
in the procedure, is it still necessary to use ConvertEmptyStringToNull
?