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

peter liles 556 Reputation points

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 ='' ">
                        <asp:ControlParameter ControlID="DispatchTextBox" Name="Email_MessageId" PropertyName="Text"  Type="String"       />
            <asp:TextBox ID="DispatchTextBox" runat="server" Columns="60" MaxLength="50" Text="" ></asp:TextBox>
            <asp:Button ID="MsgButton" runat="server" Text="Find" />

A set of technologies in the .NET Framework for building web applications and XML web services.
3,404 questions
{count} votes

Accepted answer
  1. Albert Kallal 5,226 Reputation points

    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:TextBox ID="txtCity" runat="server"
            Style="margin-left: 15px" Font-Size="Large">
        <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"
                <h4>No Hotels Found</h4>
                <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" />

    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
            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)
        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
                End Using
            End Using
            Return rstData
        End Function
    0 comments No comments

0 additional answers

Sort by: Most helpful