Select all data when control parameter is blank in sqldatasource for uniqueidentifier column

Question

When working with Uniqueidentifier as the filter property in where clause i cannot seem to retrieve any records? only when i remove the WHERE statement and also the <SelectPararaters> do i retrieve records!

I find it impossible working with these type.

The Email_Messageid is the primary key column of the table.

   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                SelectCommand="SELECT * FROM [DispatchEmail] WHERE Email_MessageId  LIKE LOWER(@Email_MessageId) + '%' or @Email_MessageId ='' ">
               
                <SelectParameters>
                       
                        <asp:ControlParameter ControlID="DispatchTextBox" Name="Email_MessageId" PropertyName="Text"  Type="String"       />
                </SelectParameters>
            </asp:SqlDataSource>
            <asp:TextBox ID="DispatchTextBox" runat="server" Columns="60" MaxLength="50" Text="" ></asp:TextBox>
            <asp:Button ID="MsgButton" runat="server" Text="Find" />

Answer 1

Well, as suggest in comments, (and I agree), it is likely less effort to just write out the code.

the wizard generated Sql data source on a page can be great if you keep things simple. However, over time, you find for special cases like making the search optional, and other types of code logic?

You find it less effort to build the sql in code, and with just a bit of practice, you find increased flexibility, and really not much more effort.

I don't have your data, but lets make a simple search. If we don't enter anything for the search, then we return all records. However, if we type in say a few characters, then we match with a sql like statement.

So, what I do is "remove" the data source from the Gridview, and then use code behind to fill out the grid view.

So, say this markup:

    <asp:Label ID="Label1" runat="server"
        Text="Search for City" Font-Size="Large">
    </asp:Label>
    <asp:TextBox ID="txtCity" runat="server"
        Style="margin-left: 15px" Font-Size="Large">
    </asp:TextBox>

    <asp:Button ID="cmdSearch" runat="server"
        Text="Search" CssClass="btn" Style="margin-left: 15px"
        OnClick="cmdSearch_Click" />
    <br />
    <br />


    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="ID" CssClass="table table-hover"
        ShowHeaderWhenEmpty="true">
        <EmptyDataTemplate>
            <h4>No Hotels Found</h4>
        </EmptyDataTemplate>
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" />
            <asp:BoundField DataField="City" HeaderText="City" />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName" ItemStyle-Width="180" />
            <asp:BoundField DataField="Description" HeaderText="Description" />
        </Columns>
    </asp:GridView>

Note how the gridview does NOT have a sql data source. And note how rather nice and clean the markup is (no sql data source).

So, now we feed the gridview using code. So, "yes" it is some code, but we then get full control over the sql, and better yet, we can have that optional search criteria.

So, say this code:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            LoadGrid()
        End If

    End Sub

    Sub LoadGrid()

        Dim cmdSQL As New SqlCommand("SELECT * FROM tblHotelsA")

        If txtCity.Text <> "" Then
            cmdSQL.CommandText &= " WHERE City like @City + '%'"
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = txtCity.Text
        End If

        cmdSQL.CommandText &= " ORDER BY HotelName"

        GridView1.DataSource = MyRstP(cmdSQL)
        GridView1.DataBind()

    End Sub

Not a lot of code, but now the results are this:

And since I often want to "code" some SQL, then I used a helper routine, and one that I made global to my project (so I can use it over and over). That routine was MyRstP and it returns a data table.

This code:

    Public Function MyRstP(cmdSQL As SqlCommand) As DataTable

        Dim rstData As New DataTable
        Using conn As New SqlConnection(My.Settings.TEST4)

            Using (cmdSQL)
                cmdSQL.Connection = conn
                conn.Open()
                rstData.Load(cmdSQL.ExecuteReader)
            End Using
        End Using

        Return rstData

    End Function