How to Create Search using Three Tier Architechture

Question

So I'm trying to create a search using three tier and there aren't many good examples online so i tried it out to see if I can make it work but it just won't work for some reason. Can you please look at my code and see what I'm doing wrong?

Stored Procedure:

CREATE PROCEDURE [dbo].[GuestSearch]
    @FirstName varchar(20),
    @LastName varchar(20),
    @Phone varchar(15)
AS
    SELECT FirstName, Lastname, Phone
    FROM Guest
    WHERE FirstName LIKE '%'+@FirstName+'%'
    AND LastName LIKE '%'+@LastName+'%' AND
    Phone LIKE '%'+@Phone+'%'

BEL

public class GuestBEL
    {
        public int GuestID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
    }

BLL

public DataSet FindGuest()
        {
            GuestDAL objDAL = new GuestDAL();
            return objDAL.FindGuest();
        }

DAL

public DataSet FindGuest()
        {
            DataSet dts = new DataSet();
            SqlCommand cmd = new SqlCommand("GuestSearch", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            con.Open();
            adapt.Fill(dts);
            cmd.Dispose();
            dts.Dispose();
            con.Close();
            return dts;
        }

UI

protected void btnSearch_Click(object sender, EventArgs e)
        {
            DataSet data = new DataSet();
            data = objBLL.FindGuest();
            gvGuest.DataSource = data;
            gvGuest.DataBind();
            readGrid();
        }

So When I type something in the search the gridview should display what i put in the text box but for some reason, the error i get is that 'Procedure or function 'GuestSearch' expects parameter '@FirstName', which was not supplied.'

you have to add parameters to your `SqlCommand`. – Daniel A. White May 02 '19 at 14:39 — Daniel A. White, May 02 '19 at 14:39

score 0 · Answer 1 · answered May 02 '19 at 14:42

0

You need to add the parameters to the Command in public DataSet FindGuest() function:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = "somename";
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = "somelastname";
cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = "somephone";

answered May 02 '19 at 14:42

Chakian

119
1
3
16

Or you can make the stored procedure's parameters optional: `@FirstName varchar(20) = ''` and so on. – Chakian May 02 '19 at 14:44
So i tried out that method but i get a return of this error in my UI DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'GuestID'.' – Jed May 02 '19 at 16:06
It seems like your `gvGuest` grid is expecting a GuestID field but the datasource doesn't have it. Can you add `GuestID` to the stored procedure's select statement: `SELECT GuestID, FirstName, Lastname, Phone` – Chakian May 03 '19 at 06:32
This is usually caused if the column is not present as answered in [this question](https://stackoverflow.com/questions/6736052/databinding-system-data-datarowview-does-not-contain-a-property-with-the-name). Can you show us your GridView implementation? – Chakian May 14 '19 at 06:09

How to Create Search using Three Tier Architechture

1 Answers1