OR statement not working

[primagic]

I have the following sql statement:

SELECT GuestID, Title, Firstname, Surname, AddressLine1, AddressLine2, AddressLine3, AddressLine4, CityTown, Postcode, Country, PhoneHome, PhoneBusiness, PhoneMobile, EmailAddress, Nationality, Sex, Notes FROM Guests WHERE (Firstname = @Firstname) OR (Surname = @Surname)

@Firstname is a textbox on my page - txtFirstname
@Surname is a textbox on my page - txtSurname

I am trying to bind the results to a grid by using the following code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.grdGuestList.DataSourceID = GuestSearchResults.ID

    End Sub

My datasource reads:

<asp:SqlDataSource ID="GuestSearchResults" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MyHotelConnectionString %>" 
        
        SelectCommand="SELECT GuestID, Title, Firstname, Surname, AddressLine1, AddressLine2, AddressLine3, AddressLine4, CityTown, Postcode, Country, PhoneHome, PhoneBusiness, PhoneMobile, EmailAddress, Nationality, Sex, Notes FROM Guests WHERE (Firstname = @Firstname) OR (Surname = @Surname)">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtFirstName" Name="Firstname" 
                PropertyName="Text" Type="String" />
            
            <asp:ControlParameter ControlID="txtSurname" Name="Surname" 
                PropertyName="Text" Type="String" />
            
        </SelectParameters>
    </asp:SqlDataSource>

If I enter in information in both textboxes it returns the correct results, however if I enter information in one of the boxes, no records appears. If I run the query when configuring the datasource and input only say the firstname it returns the correct results.

What I am trying to achieve is display all records in the database that match any records whose firstname is like/contains txtFirstname or whose Surname is like/contains txtSurname

 

[jbenson001]

This is why you shouldn't use the DataSource controls. I would suggest writing the SQL yourself, using an SP if possible.

 

[primagic]

Ok I will try your idea. Is there a reason why you wouldnt use datasource controls. I am using quite a few of them throughout my application and this is the only one that is not producing the correct results

 

[jbenson001]

They will not produce the best SQL possible for one, but most importantly, once you need to do something complex, they will break or not produce the correct results and you cannot debug them. That is the major problem. look at the the problem you are having now. If you wrote the sql yourself, you could easily track down the issue, with the DataSource controls, you cannot do that.

 

[Qik3Coder]

Change your SQL to this:

SELECT GuestID, Title, Firstname, Surname, AddressLine1, AddressLine2, AddressLine3, AddressLine4, CityTown, Postcode, Country, PhoneHome, PhoneBusiness, PhoneMobile, EmailAddress, Nationality, Sex, Notes FROM Guests WHERE (Firstname [b]LIKE '%'+@Firstname+'%') OR (Surname LIKE '%'+@Surname+'%')[/b]

JBenson makes a valid point on the debugging, but this issue you can resolve by running the SQL in a SQL editor. There are some where the problem is in the dynamically created code that you can't see.

Lodlaiden

A lack of experience doesn't prevent you from doing a good job.