Need to use function for search

Erfan 0
Viewed 19

I have a Mediator table which is connect to a table for name and last name, and connect to another table for id, it gets value with fk_id and fk_title, how ever now i want to add a function to it's query so i can search name, the query is this but doesn't work: I'm trying to do with this way { if (!string.IsNullOrEmpty(FirstName)) strQuery += " uf__GetAccessGroupAndPersonel(fk_Personel, " + GetDbStringValue(PersonelCode) + " ) "; } but not any result I'm using webform

search webforms function
1 Answers

Ok, the simple (and general) approach to this is to build a query that joins in the "text" description from the other table.

So, say we have a simple list of People, and they are booked to a hotel.

So, we have this:

SELECT ID, FirstName, LastName, Hotel_id 
FROM People

And the output is this:

ID FirstName LastName Hotel_id
2 Alex Smith 77
3 Bob Phoenix 102
4 Correy Lollas 102
6 Ronz Howerd 102
7 Scott Stevens 77
8 Marianne Tucker 77
9 Bob Hamilton 77

etc.

Ok, now of course Hotel_ID is not much of use.

I in MOST cases strong suggest you build a View in sql server, and use that. The major reason is you get a nice GUI builder for the query, and ALSO then don't have to place a boatload of messy SQL "in-line" in your code.

So, lets use the query builder in sql server, do this graphical, and then I can just use the mouse, keep sipping my coffee while making this post!!!

Ok, so we want HotelName (and might as well toss in description).

So, in the query (new view) builder in SQL server, then we have this:

enter image description here

So lets save this view - say vPeopleHotels

The sql the above wrote was of course this, but, really, using drag + drop and the mouse is a lot less efforts here. Remember to make the child join what we call a left join (in case our people don't yet have a hotel_ID - I still want them to display.

the SQL the view builder wrote for me is this:

SELECT dbo.People.ID, dbo.People.Firstname, dbo.People.LastName,
     dbo.tblHotelsA.HotelName, dbo.tblHotelsA.Description
FROM dbo.People LEFT OUTER JOIN
    dbo.tblHotelsA ON dbo.People.Hotel_ID = dbo.tblHotelsA.ID

And LEFT join, or LEFT outer join - the same. (I tend to leave out the word "outer" here.

Ok, so, lets save this query.

so, now our markup.

We need a text box (for the search), and search button, and then grid view.

        <asp:Label ID="Label1" runat="server" Text="Enter Hotel" Font-Size="Large"></asp:Label>
        <asp:TextBox ID="txtHotel" runat="server" Font-Size="Large" Style="margin-left:20px"></asp:TextBox>
        <asp:Button ID="cmdSearch" runat="server" Text="Search" Style="margin-left:20px" CssClass="btn" />

        <br />
        <br />

        <asp:GridView ID="GridView1" runat="server" CssClass="table table-striped">

        </asp:GridView>

So, now on page-load (only first time), we will load up the grid.

Say this code:

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

    If Not IsPostBack Then
        ' optional  - load grid with all data 
        LoadGrid()
    End If

End Sub

Sub LoadGrid()

    Dim strSQL As String =
        "SELECT * FROM vPeopleHotels WHERE HotelName like @Hotel + '%'
         ORDER BY FirstName"
    Dim cmdSQL As New SqlCommand(strSQL)
    cmdSQL.Parameters.Add("@Hotel", SqlDbType.NVarChar).Value = txtHotel.Text

    GridView1.DataSource = MyRstP(cmdSQL)
    GridView1.DataBind

End Sub

Protected Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click

    LoadGrid()

End Sub

So, the results are this:

enter image description here

But, if I type in say Jas, then I get this:

enter image description here

So, I am able to search by the text hotel name. We really don't write much code, but just make a query that includes the text columns from that table, and thus we don't have to search by some "Hotel_id" that the user of course would never know about.

Also, I used a helper routine - one that is global to my app, since I became VERY tired very fast having to setup a SQL command, so I have this bit of code (MyRstP)

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
Albert D. Kallal0
Related