1 Replies - 5570 Views - Last Post: 02 June 2010 - 09:00 AM Rate Topic: -----

#1 Dinosaurad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 30-May 10

Search database through textbox? (edited)

Posted 01 June 2010 - 05:36 AM

Hi.
I want to be able to search in my database (Superdata) through my textbox (txtsøg), and then i want it to be shown in my listbox (lstresult).
I'm not sure how i define which rows to read etc.
I'm very lost, please help me on my way :(

[btw, I have made made connection to the db another place in the coding]

public string resultat, searchresult;

        private SqlCommand objCmd;
        private SqlDataAdapter objDataAdapter;
        private SqlConnection objConn;

        public int vareantal, varepris, varesum;
        public string Summeretværdi;
        public string input;



private void btnsøg_Click(object sender, EventArgs e)
        {
            
            openconnection();
            lstresult.Items.Clear(); //the resultlist.
            txtsøg.Text = input; //the searchbar.
            FetchSearch(objConn);
            closeconnection();
        }



 public void FetchSearch(SqlConnection objConn)
        {
            string searchresult;
            // Opret hjælpevariabel (Reference-variabel). 
            System.Data.DataRowCollection objRows;

            // Opret DataTable, som skal indeholde data fra vores SELECT-statement
            System.Data.DataTable dtResult = new System.Data.DataTable();

            // Opret command-objekt, som fortæller hvilken type SQL-command vi
            // vil foretage og den konkrete kommando.
            objCmd = objConn.CreateCommand();
            objCmd.CommandType = System.Data.CommandType.Text;                  //Type: SQL Statement 
            objCmd.CommandText = string.Concat("select Ydelser.Navn, Konsulenter.Navn, Varer.Varetekst from Konsulenter JOIN Ydelser ON Ydelser.Navn LIKE '%{0}%' AND Konsulenter.Navn LIKE '%{0}%' JOIN Varer ON Varer.Varetekst LIKE '%{0}%'",input);    //the SELECT 

            // Opret data-adapter, som vi anvender til at eksekvere SQL-forespørgslen.
            // Data lagres i tabellen "dtResult"
            objDataAdapter = new SqlDataAdapter(objCmd);
            try
            {
                objDataAdapter.Fill(dtResult);
            }
            catch (Exception Exc)
            {
                Console.WriteLine(Exc.Message + "You Failed");
            }


            // Udskriv navnene på attributterne i tabellen dtResult. 
            string Result1 = Convert.ToString(dtResult.Columns[0]);
            string Result2 = Convert.ToString(dtResult.Columns[1]);
            string Result3 = Convert.ToString(dtResult.Columns[2]);


            int intNuOfRows = dtResult.Rows.Count;
            for (int i = 0; i < intNuOfRows; i++)
            {
                 objRows = dtResult.Rows;
               string sr1 = Convert.ToString(objRows[i][1]);
               string sr2 = Convert.ToString(objRows[i][2]);
               string sr3 = Convert.ToString(objRows[i][3]);

               searchresult = sr1 + sr2 + sr3;
                lstresult.Items.Add(searchresult);
            }
            
        }

    }
}


This post has been edited by Dinosaurad: 02 June 2010 - 12:47 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Search database through textbox? (edited)

#2 Dinosaurad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 30-May 10

Re: Search database through textbox? (edited)

Posted 02 June 2010 - 09:00 AM

i did it, but probably in the worst (most complicated) way.

    private void btnsøg_Click(object sender, EventArgs e)
        {

            openconnection();
            lstresult.Items.Clear();
            input = txtsøg.Text;
            FetchSearch(objConn);
            FetchSearch2(objConn);
            FetchSearch3(objConn);
            closeconnection();
        }

        public void FetchSearch(SqlConnection objConn)
        {
            string searchresult;
            // Opret hjælpevariabel (Reference-variabel). 
            System.Data.DataRowCollection objRows;

            // Opret DataTable, som skal indeholde data fra vores SELECT-statement
            System.Data.DataTable dtResult = new System.Data.DataTable();

            // Opret command-objekt, som fortæller hvilken type SQL-command vi
            // vil foretage og den konkrete kommando.
            objCmd = objConn.CreateCommand();
            objCmd.CommandType = System.Data.CommandType.Text;                  //Type: SQL Statement 


            objCmd.CommandText = string.Concat("select Ydelser.Navn from superdata.dbo.Ydelser where Navn like '%" + input + "%'");


            // Opret data-adapter, som vi anvender til at eksekvere SQL-forespørgslen.
            // Data lagres i tabellen "dtResult"
            objDataAdapter = new SqlDataAdapter(objCmd);
            try
            {
                objDataAdapter.Fill(dtResult);
            }
            catch (Exception Exc)
            {
                MessageBox.Show(Exc.Message + "You Failed");
            }

            int intNuOfRows = dtResult.Rows.Count;

            foreach (DataRow row in dtResult.Rows)
            {
                objRows = dtResult.Rows;
                string sr1 = row[0].ToString();

                searchresult = sr1;

                lstresult.Items.Add(searchresult);
            }
        }

        public void FetchSearch2(SqlConnection objConn)
        {
            string searchresult2;
            // Opret hjælpevariabel (Reference-variabel). 
            System.Data.DataRowCollection objRows;

            // Opret DataTable, som skal indeholde data fra vores SELECT-statement
            System.Data.DataTable dtResult = new System.Data.DataTable();

            // Opret command-objekt, som fortæller hvilken type SQL-command vi
            // vil foretage og den konkrete kommando.
            objCmd = objConn.CreateCommand();
            objCmd.CommandType = System.Data.CommandType.Text;                  //Type: SQL Statement 


            objCmd.CommandText = string.Concat("select Konsulenter.Navn from superdata.dbo.Konsulenter where Navn like '%" + input + "%'");



            // Opret data-adapter, som vi anvender til at eksekvere SQL-forespørgslen.
            // Data lagres i tabellen "dtResult"
            objDataAdapter = new SqlDataAdapter(objCmd);
            try
            {
                objDataAdapter.Fill(dtResult);
            }
            catch (Exception Exc)
            {
                MessageBox.Show(Exc.Message + "You Failed");
            }

            int intNuOfRows = dtResult.Rows.Count;

            foreach (DataRow row in dtResult.Rows)
            {
                objRows = dtResult.Rows;
                string sr2 = row[0].ToString();

                searchresult2 = sr2;
                lstresult.Items.Add(searchresult2);
            }
        }

        public void FetchSearch3(SqlConnection objConn)
        {
            string searchresult3;
            // Opret hjælpevariabel (Reference-variabel). 
            System.Data.DataRowCollection objRows;

            // Opret DataTable, som skal indeholde data fra vores SELECT-statement
            System.Data.DataTable dtResult = new System.Data.DataTable();

            // Opret command-objekt, som fortæller hvilken type SQL-command vi
            // vil foretage og den konkrete kommando.
            objCmd = objConn.CreateCommand();
            objCmd.CommandType = System.Data.CommandType.Text;                  //Type: SQL Statement 


            objCmd.CommandText = string.Concat("select Varetekst from superdata.dbo.Varer where Varetekst like '%" + input + "%'");



            // Opret data-adapter, som vi anvender til at eksekvere SQL-forespørgslen.
            // Data lagres i tabellen "dtResult"
            objDataAdapter = new SqlDataAdapter(objCmd);
            try
            {
                objDataAdapter.Fill(dtResult);
            }
            catch (Exception Exc)
            {
                MessageBox.Show(Exc.Message + "You Failed");
            }

            int intNuOfRows = dtResult.Rows.Count;

            foreach (DataRow row in dtResult.Rows)
            {
                objRows = dtResult.Rows;
                string sr3 = row[0].ToString();

                searchresult3 = sr3;

                lstresult.Items.Add(searchresult3);
            }
        }
        private void lstresult_SelectedIndexChanged(object sender, EventArgs e)
        {

        }


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1