Searching through a database

d5t · October 26th, 2006, 01:50 PM

I'm trying to bring up data for a specific person when I search for their first and last name displayed in table. I'm getting a blank data table when I search-- only the rows names appear. What am I doing incorrectly?

Code:

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script language="C#" runat="server" >

    void Page_Load()
    {

     if (Page.IsPostBack)
     {
    OleDbConnection objConnection;
    OleDbDataAdapter objCommand;
    String strConnect;
    String strCommand;
    DataSet DataSet1 = new DataSet ();

    strConnect = @"Provider=Microsoft.Jet.OLEDB.4.0;";
    strConnect += @"Data Source= D:\Reservations.mdb;";
    FirstName.Text = "";
    LastName.Text = "";
    strCommand = "SELECT Date, RoomNo, TimeIn, TimeOut 
FROM Contacts, Reserves WHERE Contacts.ContactID 
= Reserves.ContactID AND FirstName 
= ' " + FirstName.Text + 
" ' AND LastName = ' " + LastName.Text + " ' ";

    objConnection = new OleDbConnection(strConnect);
    objCommand = new OleDbDataAdapter(strCommand, objConnection);
    objCommand.Fill(DataSet1, "Reserves");
    DataGrid1.DataSource = DataSet1.Tables["Reserves"].DefaultView;
    DataGrid1.DataBind();

    }
    }
</script>
<html>
<head><title></title>
<form runat="server">

   Enter first name: <asp:TextBox id="FirstName" runat="server"></asp:TextBox>
   Enter last name: <asp:TextBox id="LastName" runat="server"></asp:TextBox><br />
   <br />
   <input type="submit" value="Search" /><br /><br />
   <td> </td>
   <asp:DataGrid id="DataGrid1" EnableViewState="False" runat="server"></asp:DataGrid>

</head>
<body>
</form>
</body>
</html>

dparsons · October 26th, 2006, 02:20 PM

This is a SQL problem. *I think*

SELECT Date, RoomNo, TimeIn, TimeOut
FROM Contacts, Reserves WHERE Contacts.ContactID
= Reserves.ContactID AND FirstName
= ' " + FirstName.Text +
" ' AND LastName = ' " + LastName.Text + " ' ";

Which of the above columns exist in the Contacts and Reserves table? I am making an assumption that all the columns belong to the Reserves table.

SELECT
r.Date, r.RoomNo, r.TimeIn, r.TimeOut
FROM
Reserves r
INNER JOIN Contacts c
ON c.ContactID = r.ContactID
WHERE
c.FirstName = ' " + FirstName.Text +
" ' AND c.LastName = ' " + LastName.Text + " ' ";

On a more personal note, I like my where clauses to be based on an ID as opposed to a name (You can have duplicate names in your database, IDs are unique)

In anycase that should work for you.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

gbianchi · October 26th, 2006, 02:47 PM

hi there..

firstname and lastname are empty strings, do you have contacts with no name in the table?? (not null, just empty strings)...

HTH

Gonzalo

d5t · October 26th, 2006, 06:14 PM

No, all the contacts have names--nothing is empty. Reserves holds the LastName and FirstName, maybe I'm doing something wrong with the DataSource and Bind. I'll try the different SQL command and see if it works.

I have three tables in the database Reserves:

Reserves
Contacts <--- has first and last name
ROOMS

peace95 · October 26th, 2006, 09:24 PM

d5t:
Matching firstname and lastname has always been a headache unless you have control over how the names are created and maintained in the database. The first- and last- names much match exactly. For example if the name fields are in all caps in the database, i.e., M T PROMISES, and in the prompts the user enters, mt Promises or m t Promises, you will not get a match. There is also a problem with the name MC Call or McCall. Then also if you have several names that are the same, when this happens an initial is thrown in with the first name. A possible solution: Match on the primary and foreign key fields and display the info accordingly.

Hope this helps.

d5t · October 27th, 2006, 12:11 AM

Thanks for the tip peace95, I'll try debugging it tomorrow. Throw in any other advice.

dparsons · October 27th, 2006, 07:10 AM

As I said, I prefer searching based on ID as opposed to name since an ID is (or should) be unique.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature