Search engine by Drop Down List selection and text box entry searches Database for the entry and displays in aspx

Question

I am trying to create a search from ProductDB(database), the main columns I would like the user to search is Material_No and Product_Line.

So far, I have the following:

Drop Down List:

<asp:DropDownList ID="DropDownList" runat="server" Height="16px" 
        onclick="SearchButton_Click" Width="144px" 
        AutoPostBack="True">
        <asp:ListItem>Please select...</asp:ListItem>
        <asp:ListItem Value="0">Material No</asp:ListItem>
        <asp:ListItem Value="1">Product Line</asp:ListItem>
    </asp:DropDownList>

TextBox:

<asp:TextBox ID="TextBox1" runat="server" ontextchanged="TextBox1_TextChanged">
</asp:TextBox>

Search Button:

<asp:Button ID="SearchButton" runat="server" Text="Search" 
onclick="SearchButton_Click" />

So I am trying to do is when the user chooses either Material No or Product Line when he types the Material No or Product Line after clicking the search button, the result should show either in grid format or something similar, and if he just clicks search without choosing anything all the result should show.

Here is what I have done so far.

Old Code:

protected void SearchButton_Click(object sender, EventArgs e)
    {
        string  Selectedvalue = DropDownList.SelectedItem.Value;
        if (DropDownList.SelectedItem.ToString() == "Material No")
        {
            MessageBox.Show("Material No selected");
            string textbox = TextBox1.Text;
            MessageBox.Show(textbox.ToString());

            SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");

            DataSet dsData = new DataSet();

            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "SELECT * FROM ProductDB WHERE Material_No ='" + TextBox1.Text + "'";
            cmd.Connection = conn;

            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter("", conn);

            SqlCommandBuilder cmdBldr = new SqlCommandBuilder(da);
            da.Fill(dsData, TextBox1.Text);

            MessageBox.Show("Connection Successful");
            conn.Close(); 
        }
        else
        {
            MessageBox.Show("Product Line selected");
        }


    }

New Code:

private SqlConnection conn;
private SqlDataAdapter daMaterial;
private SqlDataAdapter daProduct;

private SqlCommand cmdMaterial;
private SqlCommand cmdProduct;

private SqlParameter paramMaterial;
private SqlParameter paramProduct;

private DataSet dsMaterial;
private DataSet dsProduct;
private DataGrid dgMaterial;
private DataGrid dgProduct;

private const string tableNameMaterial = "Material_No";
private const string tableNameProduct = "Product_Line";
enter code here
protected void SearchButton_Click(object sender, EventArgs e)
    {
        string Selectedvalue = DropDownList.SelectedItem.Value;
        if (DropDownList.SelectedItem.ToString() == "Material No")
        {
            //MessageBox.Show("Material No. Selected");
            string textbox = TextBox1.Text;
            //MessageBox.Show(textbox.ToString());

            conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");
            dsMaterial = new DataSet(); 
            daMaterial = new SqlDataAdapter("SELECT * FROM ProductDB WHERE Material_No = @Material_No", conn);
            daMaterial.SelectCommand.CommandText = "SELECT * FROM ProductDB WHERE Material_No = @Material_No";
            paramMaterial = new SqlParameter();
            paramMaterial.ParameterName = "@Material_No";
            paramMaterial.Value = TextBox1.Text;

            daMaterial.SelectCommand = cmdMaterial;
            cmdMaterial.Parameters.Add("@Material_No", SqlDbType.BigInt).Value = TextBox1.Text;

            daMaterial.Fill(dsMaterial, tableNameMaterial);              

            //MessageBox.Show("Connection Successful");
            conn.Close();
        }
        else
        {
            //MessageBox.Show("Product Line selected");
            string textbox = TextBox1.Text;
            //MessageBox.Show(textbox.ToString());


            conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");
            dsProduct = new DataSet();
            daProduct = new SqlDataAdapter("SELECT * FROM ProductDB WHERE Product_Line = @Product_Line", conn);
            daProduct.SelectCommand.CommandText = "SELECT * FROM ProductDB WHERE Product_Line = @Product_Line";
            paramProduct = new SqlParameter();
            paramProduct.ParameterName = "@Product_Line";
            paramProduct.Value = TextBox1.Text;

            daProduct.SelectCommand = cmdProduct;
            cmdProduct.Parameters.Add("@Product_Line", SqlDbType.VarChar, 50).Value = TextBox1.Text;
            conn.Open();
            daProduct.Fill(dsProduct, tableNameProduct);

            //MessageBox.Show("Connection Successful");
            conn.Close();
        }


    }

I am getting an error "Object reference not set to an instance of an object."

Can someone check whether the Parameter use is correct with the SqlDataAdapter

Answer 1

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string Query = string.Empty;
        try
        {
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            if (DropDownList1.SelectedValue.ToString() == "Name")
            {
                Query = "select * from tbl_Employee where Name Like '" + txtSearchName.Text +  "%'";
            }
            else if (DropDownList1.SelectedValue.ToString() == "Designation")
            {
                Query = "select * from tbl_Employee where Designation Like '" + txtSearchName.Text + "%'";
            }
            SqlDataAdapter sqlDa = new SqlDataAdapter(Query, sqlCon);
            DataSet Ds = new DataSet();
            sqlDa.Fill(Ds);
            dgvEmployee.DataSource = Ds;
            dgvEmployee.DataBind();
        }
        catch (Exception ex)
        {

            HttpContext.Current.Response.Write("<script>alert('wfrmGrid: 11')</script>" + ex.Message);
        }

    }

Answer 2

The following code is working:

private const string tableNameMaterial = "Material_No";
private const string tableNameProduct = "Product_Line";

protected void SearchButton_Click(object sender, EventArgs e)
    {
        string Selectedvalue = DropDownList.SelectedItem.Value;
        if (DropDownList.SelectedItem.ToString() == "Material No")
        {
            //MessageBox.Show("Material No. Selected");
            string textbox = TextBox1.Text;
            //MessageBox.Show(textbox.ToString());

            SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            cmd.CommandText = "SELECT * FROM ProductDB WHERE Material_No = @Material_No";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@Material_No", SqlDbType.BigInt);
            cmd.Parameters["@Material_No"].Value = TextBox1.Text;

            SqlDataAdapter daMaterial = new SqlDataAdapter();
            daMaterial.SelectCommand = cmd;

            DataSet dsMaterial = new DataSet();
            conn.Open();
            daMaterial.Fill(dsMaterial, tableNameMaterial);

            //MessageBox.Show("Connection Successful");
            GridView1.DataSource = dsMaterial;
            GridView1.DataBind();

            conn.Close();
        }
        else
        {
            //MessageBox.Show("Product Line selected");
            string textbox = TextBox1.Text;
            //MessageBox.Show(textbox.ToString());

            SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            cmd.CommandText = "SELECT * FROM ProductDB WHERE Product_Line = @Product_Line";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@Product_Line", SqlDbType.VarChar);
            cmd.Parameters["@Product_Line"].Value = TextBox1.Text;

            SqlDataAdapter daProduct = new SqlDataAdapter();
            daProduct.SelectCommand = cmd;

            DataSet dsProduct = new DataSet();
            conn.Open();
            daProduct.Fill(dsProduct, tableNameProduct);

            //MessageBox.Show("Connection Successful");
            GridView1.DataSource = dsProduct;
            GridView1.DataBind();

            conn.Close();
        }


    }

Now, I need the search box to autocomplete the letters from the database... if anyone has any suggestion, please do share it.

Thank you

Answer 3

Some things:

daProduct = new SqlDataAdapter("SELECT * FROM ProductDB WHERE Product_Line = @Product_Line", conn);

Creates a new SqlDataAdapter and initializes it's SelectCommand with the CommandText and Connection, hence following lines are redundant and inherently error-prone:

1. daProduct.SelectCommand.CommandText = "SELECT * FROM ProductDB WHERE Product_Line = @Product_Line";
2. daProduct.SelectCommand = cmdProduct;

The second instruction even overrides the first with a new CommandText and Connection without ever having used.

paramProduct = new SqlParameter();

Instead of using this parameterless constructor i would use AddWithValue or Parameters.Add which are less error-prone(e.g. you haven't provide a type).

cmdProduct.Parameters.Add( ....

Now you are using the method i've suggested without ever having used paramProduct. You're also never disposing unmanaged resources(e.g. connections are staying open in case of errors), use the using-statement therefore. Btw, if you use a DataAdapter you don't even need to open/close the connection, that is done implicitely on DataAdapter.Fill.

Sorry, but your code is a mess.Instead of fiddling around with yours, i'll provide a clean version that should work.

....
else
{
    using(var con =  new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True"))
    using (var daProduct = new SqlDataAdapter("SELECT * FROM ProductDB WHERE Product_Line = @Product_Line", con))
    {
        daProduct.SelectCommand.Parameters.Add("@Product_Line", SqlDbType.VarChar, 50).Value = TextBox1.Text;
        dsProduct = new DataSet();
        daProduct.Fill(dsProduct, "Product_Line");
    }
}