Check If Record don't exist give error message or msgbx

[willdevelope]

Here is my select statement and it works. Just don't know how to validate if data don't exist by displaying mesage

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStringsyxinventoryConnectionString %>"
onselecting="SqlDataSource1_Selecting"
**********HERE IS MY SELECT QUERY STATEMENT*******
SelectCommand="SELECT [ItemDesc], [Att1], [Att2], [LotCode], [SOHQty], [Count1], [Count2], [Count3], [Variance], [ReportUpdated], [Location] FROM [VarianceReport] WHERE ([ItemNum] = @ItemNum)">
<SelectParameters>
*************************************************** <asp:FormParameter FormField="TxtVarReport" Name="ItemNum" Type="String" />

 

[jmeckley]

1st problem. your using a datasource control. don't. you cannot debug them or add logic to them. since you want to validate your results before displaying, to render a 'no data' message. you want to add logic.

so ditch the data source control, fetch the data in code and then determine if the record exists.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[adamroof]

Although i do agree the SqlDataSource has limited flexibility when trying to go above and beyond, there are features to make it feasible for the quick and dirty.

1. Use StoredProcs for selects instead of inline statements

CREATE PROCEDURE view_VarianceReport
     @ItemNum int --or whatever datatype
AS
SET NOCOUNT ON;

SELECT [ItemDesc], [Att1], [Att2], [LotCode], [SOHQty], [Count1], [Count2], [Count3], [Variance], [ReportUpdated], [Location]
FROM [VarianceReport] 
WHERE [ItemNum] = @ItemNum

2. Use a GridView to display your results.
a. You can use the EmptyDataText property to display text if the SqlDataSource returns no rows
b. You can use the OnSelecting event of the SqlDataSource to set parameters that get their value from a page variable
c. You can use the OnSelected event to display how many records it returned if you use paging, sorting, and filtering. It can also be used in your situation to alert a message to the user.

3. Here is a sample UpdatePanel / GridView / SqlDataSource implementation

<asp:UpdatePanel ID="upDomainList" runat="server">
    <ContentTemplate>
        <asp:Panel ID="pnlDomainFilter" runat="server" Style="text-align: right; padding: 3px;"
            DefaultButton="btnFilterDomains">
            <asp:Label ID="lblDomainCount" runat="server" Style="padding-right: 10px;" />
            <asp:TextBox ID="txtFilterDomain" runat="server" Width="120" Style="font-size: 7pt;" />

            <asp:DropDownList ID="ddlDomainTLDFilter" runat="server" Style="font-size: 7pt;">
                <asp:ListItem Value="0">--TLD--</asp:ListItem>
                <asp:ListItem Value=".com">com</asp:ListItem>
                <asp:ListItem Value=".net">net</asp:ListItem>
                <asp:ListItem Value=".org">org</asp:ListItem>
                <asp:ListItem Value=".tv">tv</asp:ListItem>
                <asp:ListItem Value=".us">us</asp:ListItem>
                <asp:ListItem Value=".biz">biz</asp:ListItem>
            </asp:DropDownList>

            <asp:Button ID="btnFilterDomains" runat="server" Text="Find" />
            <asp:Button ID="btnRefreshFilter" runat="server" Text="Rst" OnClick="refresh_DomainFilter" />
        </asp:Panel>

        <asp:GridView ID="gvDomainReg" runat="server" DataSourceID="dsDomainRegistrations"
            AutoGenerateColumns="false" DataKeyNames="DomainRegistrationID" UseAccessibleHeader="false"
            AllowSorting="true" AllowPaging="true" PageSize="10" Width="100%" OnRowDataBound="domainReg_RDB"
            EmptyDataText="There are no Domains to view">
            <HeaderStyle CssClass="dgHeading" />
            <PagerSettings Mode="NumericFirstLast" Position="Bottom" />
            <PagerStyle CssClass="dgPager" HorizontalAlign="Right" />
            <Columns>
                <asp:TemplateField HeaderText="Domain" SortExpression="DomainName">
                    <ItemTemplate>
                        <%# Eval("DomainName") %>
                    </ItemTemplate>                                                
                </asp:TemplateField>
                <asp:BoundField DataField="DomainTLD" HeaderText="TLD" SortExpression="DomainTLD"
                    ItemStyle-Width="40" ReadOnly="true" />
                <asp:BoundField DataField="ExpirationDate" HeaderText="Expires" DataFormatString="{0:MM/dd/yy}"
                    SortExpression="ExpirationDate" ItemStyle-Width="60" ReadOnly="true" />
                <asp:ButtonField ButtonType="Image" ImageUrl="img/action/edit.gif" CommandName="edit"
                    ItemStyle-Width="15" />
            </Columns>
        </asp:GridView>

        <asp:SqlDataSource ID="dsDomainRegistrations" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
            SelectCommand="dom_ViewRegistrations" SelectCommandType="StoredProcedure" OnSelecting="SetDSOrderID"
            CancelSelectOnNullParameter="false" OnSelected="ds_DomainSelected">
            <SelectParameters>
                <asp:Parameter Type="Int32" DefaultValue="0" Name="OrderID" />
                <asp:ControlParameter Name="nameFilter" Type="String" ControlID="txtFilterDomain"
                    ConvertEmptyStringToNull="true" />
                <asp:ControlParameter Name="tldFilter" Type="String" ControlID="ddlDomainTLDFilter"
                    PropertyName="SelectedValue" DefaultValue="0" />
            </SelectParameters>
        </asp:SqlDataSource>
    </ContentTemplate>
</asp:UpdatePanel>

4. And here are the few lines in the code behind

public void SetDSOrderID(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@OrderID"].Value = OrderID; 
    //OrderID is set from my page load logic
}
public void ds_DomainSelected(object sender, SqlDataSourceStatusEventArgs e)
{
    pnlDomainFilter.Visible = (e.AffectedRows > 0);
    lblDomainCount.Text = String.Format("{0} Domains", e.AffectedRows);
}
public void refresh_DomainFilter(object sender, EventArgs e)
{
    txtFilterDomain.Text = "";
    ddlDomainTLDFilter.SelectedIndex = -1;
    gvDomainReg.EditIndex = -1;
    gvDomainReg.DataBind();
}
public void domainReg_RDB(Object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        int DomainStatus = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "DomainStatus"));
        if (DomainStatus == 0)
        { e.Row.Attributes["class"] = "domainPayment"; }
        else if (DomainStatus == 1)
        {
            DateTime domExpiration = Convert.ToDateTime(DataBinder.Eval(e.Row.DataItem, "ExpirationDate"));
            DateTime curDate = DateTime.Now;
            TimeSpan tsToExpire = curDate - domExpiration;
            if (tsToExpire.Days > -14)
            { e.Row.Attributes["class"] = "domainRegExpires"; }
            else if (tsToExpire.Days > -30)
            { e.Row.Attributes["class"] = "domainRegExpires30"; }
        }
        else
        { e.Row.Attributes["class"] = "domainRegExpired"; }
    }
}

5. And this is the simple Stored Procedure that runs in the SqlDataSource

ALTER PROCEDURE [dbo].[dom_ViewRegistrations]
	@OrderID int, @nameFilter varchar(50) = null, @tldFilter varchar(5) = '0'
AS
SET NOCOUNT ON;

SELECT * FROM DomainRegistrations 
WHERE OrderID = @OrderID
	AND DomainName LIKE (CASE WHEN @nameFilter = null THEN DomainName 
		ELSE '%' + ISNULL(@nameFilter,'') + '%' END)
	AND DomainTLD = (CASE WHEN @tldFilter = '0' THEN DomainTLD ELSE @tldFilter END)
ORDER BY PrimaryDomain DESC, DomainStatus, ExpirationDate