none
Search in a database using an textbox and listview

    Question

  • Hello,

    This application its new for me so maybe my question its not so accurate.
    I'm using visual studio 2008 ee and C# code.
    This is what I've done till now.
    I have created a database and populated with some records, i also created an Stored Procedure to retrieve data from the database.
    The procedure its like this:

    ALTER PROCEDURE spSearchByString
    @SearchString varchar(50)
    AS
    SELECT *
    FROM [Table_Descriptions]
    WHERE ([Table_Descriptions].Table_Name LIKE '%' + @SearchString + '%' OR [Table_Descriptions].Field_Name LIKE '%' + @SearchString + '%')
    RETURN

    And now the C# code, here its my problem:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Data;
    using System.Configuration;
    using System.Web.Security;

    public partial class _Default : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["SearchString"] != null)
            {
                ListView1_SelectedIndexChanged(Request.QueryString["SearchString"]);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Redirect("Info.aspx?SearchString=" + Server.UrlEncode(STextBox.Text));
              
                   
        }
       
        public void ListView1_SelectedIndexChanged(string strString)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("spSearchByString", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            try
            {
                int recordsAffected = dataAdapter.Fill(dataTable);
                if (recordsAffected > 0)
                {
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        System.Console.WriteLine(dr[0]);
                    }
                }
            }
            catch (SqlException e)
            {
                string msg = "";
                for (int i = 0; i < e.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + e.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }
         }

    What i want to do its searching in the database using a textbox a button and show the result in an Listview, but i can't find anything on the internet about how i can do this.
    Please help me solve this issue.
    Monday, February 23, 2009 1:41 PM

Answers

  • in this line, it seems you are assigning the listview to the search string.

    cmd.Parameters.AddWithValue("@SearchString",ListView1);

    You need to assign the selected value
    • Proposed as answer by Harry Zhu Friday, February 27, 2009 7:25 AM
    • Marked as answer by Alex.Basarab Friday, February 27, 2009 7:57 AM
    Thursday, February 26, 2009 12:13 PM

All replies

  • In your example, the code to fetch the data is written in ListView's SelectedIndexChange event handler, instead it has to be written in the button click event.

    One thing you are missing is, you need to pass the string from the textbox as a parameter to the SqlCommand.

    Monday, February 23, 2009 2:55 PM
  • OK I put it in Button1_Click and i have added a parameter and i got the error message

    "The SelectCommand property has not been initialized before calling 'Fill'."

    The code locks like this.
    protected void Button1_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("spSearchByString", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SearchString",ListView1);
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            try
            {
                int recordsAffected = dataAdapter.Fill(dataTable);
                if (recordsAffected > 0)
                {
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        System.Console.WriteLine(dr[0]);
                    }
                }
            }
            catch (SqlException ee)
            {
                string msg = "";
                for (int i = 0; i < ee.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ee.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }

            }
        }

    Why i get this error ?
    Wednesday, February 25, 2009 1:26 PM
  • i have resolved that issue and now i got a new error.
    ahh
    No mapping exists from object type System.Web.UI.WebControls.ListView to a known managed provider native type.
    Wednesday, February 25, 2009 2:14 PM
  • in this line, it seems you are assigning the listview to the search string.

    cmd.Parameters.AddWithValue("@SearchString",ListView1);

    You need to assign the selected value
    • Proposed as answer by Harry Zhu Friday, February 27, 2009 7:25 AM
    • Marked as answer by Alex.Basarab Friday, February 27, 2009 7:57 AM
    Thursday, February 26, 2009 12:13 PM