Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Web Development
  3. ASP.NET
  4. Search box

Search box

Scheduled Pinned Locked Moved ASP.NET
databasesysadminalgorithmshelp
9 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    mccarthy111
    wrote on last edited by
    #1

    I have made a simple page with a text box, a button and a datagrid. The user inputs the drug number or name into the box and clicks submit, and I want the database results to be displayed on the datagrid but when I run it nothing happens. I'm new to using textboxes for searching. I want the button event handler to use the read records method and then display the results on the datagrid. Below is my code: private void Button1_Click(object sender, System.EventArgs e) { if (!IsPostBack) ReadRecords(); } private void ReadRecords() { OleDbConnection conn = null; OleDbDataReader reader = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); string StrUserSearchValue = TextBox1.Text; OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION = StrUserSearchValue OR DrugName.DRUG_INFORMATION = StrUserSearchValue", conn); reader = cmd.ExecuteReader(); DrugSearchDataGrid.DataSource = reader; DrugSearchDataGrid.DataKeyField = "DrugNo"; DrugSearchDataGrid.DataBind(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (reader != null) reader.Close(); if (conn != null) conn.Close(); } } Thanks in advance for any help Rory

    D 1 Reply Last reply
    0
    • M mccarthy111

      I have made a simple page with a text box, a button and a datagrid. The user inputs the drug number or name into the box and clicks submit, and I want the database results to be displayed on the datagrid but when I run it nothing happens. I'm new to using textboxes for searching. I want the button event handler to use the read records method and then display the results on the datagrid. Below is my code: private void Button1_Click(object sender, System.EventArgs e) { if (!IsPostBack) ReadRecords(); } private void ReadRecords() { OleDbConnection conn = null; OleDbDataReader reader = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); string StrUserSearchValue = TextBox1.Text; OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION = StrUserSearchValue OR DrugName.DRUG_INFORMATION = StrUserSearchValue", conn); reader = cmd.ExecuteReader(); DrugSearchDataGrid.DataSource = reader; DrugSearchDataGrid.DataKeyField = "DrugNo"; DrugSearchDataGrid.DataBind(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (reader != null) reader.Close(); if (conn != null) conn.Close(); } } Thanks in advance for any help Rory

      D Offline
      D Offline
      Dan_P
      wrote on last edited by
      #2

      **mccarthy111 wrote:** _private void Button1_Click(object sender, System.EventArgs e) { if (!IsPostBack) ReadRecords(); }_ This section of code is saying, If the code is not posted back read the database. If you think about it, this code can only be run when the page is posted back. Try removing the if statement.

      M 1 Reply Last reply
      0
      • D Dan_P

        **mccarthy111 wrote:** _private void Button1_Click(object sender, System.EventArgs e) { if (!IsPostBack) ReadRecords(); }_ This section of code is saying, If the code is not posted back read the database. If you think about it, this code can only be run when the page is posted back. Try removing the if statement.

        M Offline
        M Offline
        mccarthy111
        wrote on last edited by
        #3

        Cheers, How would I write this line without the if statment? When I take the if out I get errors. I tried taking out the whole of: if (!IsPostBack) When this is done I get the following error: No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error: Line 75: OleDbCommand cmd = Line 76: new OleDbCommand("SELECT * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION = StrUserSearchValue OR DrugName.DRUG_INFORMATION = StrUserSearchValue", conn); Line 77: reader = cmd.ExecuteReader(); Line 78: Line 79: DrugSearchDataGrid.DataSource = reader; Source File: c:\inetpub\wwwroot\gp\drugsearch.aspx.cs Line: 77 Stack Trace: [OleDbException (0x80040e10): No value given for one or more required parameters.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +69 System.Data.OleDb.OleDbCommand.ExecuteReader() +7 GP.DrugSearch.ReadRecords() in c:\inetpub\wwwroot\gp\drugsearch.aspx.cs:77 GP.DrugSearch.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\gp\drugsearch.aspx.cs:58 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277

        O 1 Reply Last reply
        0
        • M mccarthy111

          Cheers, How would I write this line without the if statment? When I take the if out I get errors. I tried taking out the whole of: if (!IsPostBack) When this is done I get the following error: No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error: Line 75: OleDbCommand cmd = Line 76: new OleDbCommand("SELECT * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION = StrUserSearchValue OR DrugName.DRUG_INFORMATION = StrUserSearchValue", conn); Line 77: reader = cmd.ExecuteReader(); Line 78: Line 79: DrugSearchDataGrid.DataSource = reader; Source File: c:\inetpub\wwwroot\gp\drugsearch.aspx.cs Line: 77 Stack Trace: [OleDbException (0x80040e10): No value given for one or more required parameters.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +69 System.Data.OleDb.OleDbCommand.ExecuteReader() +7 GP.DrugSearch.ReadRecords() in c:\inetpub\wwwroot\gp\drugsearch.aspx.cs:77 GP.DrugSearch.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\gp\drugsearch.aspx.cs:58 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277

          O Offline
          O Offline
          onlytech
          wrote on last edited by
          #4

          The SQL query is incorrectly framed, try this: OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION =" + StrUserSearchValue + " OR DrugName.DRUG_INFORMATION = " + StrUserSearchValue, conn); or rather I doubt that your query is correct at all! From the select query it presume that the table name is DRUG_INFORMATION. In that case column names should be qualified as DRUG_INFORMATION.DrugNo and DRUG_INFORMATION.DrugName So I feel that above line should perhaps look like this: OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo =" + StrUserSearchValue + " OR DRUG_INFORMATION.DrugName = '" + StrUserSearchValue + "'", conn); assuming DrugName is a text column and DrugNo is a number in the access database. If all works well, then I have a suggestion to make: Any kind of search page requires that the code is kept optimized right from the beginning. Read some articles on code/data optimization before you get into the habit of what I call "Horrible Programming". Cheers! -geo Nothing is impossible!

          M 1 Reply Last reply
          0
          • O onlytech

            The SQL query is incorrectly framed, try this: OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DrugNo.DRUG_INFORMATION =" + StrUserSearchValue + " OR DrugName.DRUG_INFORMATION = " + StrUserSearchValue, conn); or rather I doubt that your query is correct at all! From the select query it presume that the table name is DRUG_INFORMATION. In that case column names should be qualified as DRUG_INFORMATION.DrugNo and DRUG_INFORMATION.DrugName So I feel that above line should perhaps look like this: OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo =" + StrUserSearchValue + " OR DRUG_INFORMATION.DrugName = '" + StrUserSearchValue + "'", conn); assuming DrugName is a text column and DrugNo is a number in the access database. If all works well, then I have a suggestion to make: Any kind of search page requires that the code is kept optimized right from the beginning. Read some articles on code/data optimization before you get into the habit of what I call "Horrible Programming". Cheers! -geo Nothing is impossible!

            M Offline
            M Offline
            mccarthy111
            wrote on last edited by
            #5

            Thanks for that. Your advice helped me sort out a few other problems too. The sql you provided enables the number search to work okay, but the search for the drugname comes up with the following error: No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error: Line 75: OleDbCommand cmd = Line 76: new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo =" + StrUserSearchValue + " OR DRUG_INFORMATION.DrugName = '" + StrUserSearchValue + "'", conn); Line 77: reader = cmd.ExecuteReader(); Line 78: Line 79: DrugSearchDataGrid.DataSource = reader; Can you figure it out? Cheers again

            O 1 Reply Last reply
            0
            • M mccarthy111

              Thanks for that. Your advice helped me sort out a few other problems too. The sql you provided enables the number search to work okay, but the search for the drugname comes up with the following error: No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error: Line 75: OleDbCommand cmd = Line 76: new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo =" + StrUserSearchValue + " OR DRUG_INFORMATION.DrugName = '" + StrUserSearchValue + "'", conn); Line 77: reader = cmd.ExecuteReader(); Line 78: Line 79: DrugSearchDataGrid.DataSource = reader; Can you figure it out? Cheers again

              O Offline
              O Offline
              onlytech
              wrote on last edited by
              #6

              Well thats quite obvious. A text cannot be compared with a db field of number datatype. There are a number of ways to tackle this problem: 1. You must change your page UI such that search text or number is entered into separate textboxes and on buttonclick, depending on which textbox is filled, it shud call different queries; the one for DrugNo or DrugName. 2. Keep only one textbox and add a combobox with two values; "Search by Drug No." , "Dearch by Drug Name" Now on buttonclick, depending on the selected value of combobox, call the appropriate query. 3. The simplest approach and also the most expensive in performance: just change the datatype of DrugNo in db to text. And then your query should look like: "Select * FROM DRUG_INFORMATION WHERE DrugNo ='" + StrUserSearchValue + "' OR DrugName = '" + StrUserSearchValue + "'"; hth -geo Nothing is impossible!

              M 1 Reply Last reply
              0
              • O onlytech

                Well thats quite obvious. A text cannot be compared with a db field of number datatype. There are a number of ways to tackle this problem: 1. You must change your page UI such that search text or number is entered into separate textboxes and on buttonclick, depending on which textbox is filled, it shud call different queries; the one for DrugNo or DrugName. 2. Keep only one textbox and add a combobox with two values; "Search by Drug No." , "Dearch by Drug Name" Now on buttonclick, depending on the selected value of combobox, call the appropriate query. 3. The simplest approach and also the most expensive in performance: just change the datatype of DrugNo in db to text. And then your query should look like: "Select * FROM DRUG_INFORMATION WHERE DrugNo ='" + StrUserSearchValue + "' OR DrugName = '" + StrUserSearchValue + "'"; hth -geo Nothing is impossible!

                M Offline
                M Offline
                mccarthy111
                wrote on last edited by
                #7

                The thing is i have implemented many other functions which require the DrugNo set as autonumber as datatype. The code you gave initially searched using the DrugNo alright retireving the relevant row, it was the DrugName which wasn't retrieving any data. If the above method is possible using DrugNo as autonumber, how would I add a combobox with these values and how would the sql look. Or else is it possible that something could be changed in the first sql you provided to get the drugname search working as well. Thanks alot for your time. Much appreciated. Rory

                M 1 Reply Last reply
                0
                • M mccarthy111

                  The thing is i have implemented many other functions which require the DrugNo set as autonumber as datatype. The code you gave initially searched using the DrugNo alright retireving the relevant row, it was the DrugName which wasn't retrieving any data. If the above method is possible using DrugNo as autonumber, how would I add a combobox with these values and how would the sql look. Or else is it possible that something could be changed in the first sql you provided to get the drugname search working as well. Thanks alot for your time. Much appreciated. Rory

                  M Offline
                  M Offline
                  mccarthy111
                  wrote on last edited by
                  #8

                  I have tried to parameterize the query to see if that would work. again the Drug No search works but the DrugName search returns the following error: Input string was not in a correct format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format. Here is my updated code: string StrUserSearchValue = TextBox1.Text; OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo = ? OR DRUG_INFORMATION.DrugName = ?", conn); cmd.Parameters.Add(new OleDbParameter("@DrugNo", OleDbType.Integer)).Value = StrUserSearchValue; cmd.Parameters.Add(new OleDbParameter("@DrugName", OleDbType.VarChar)).Value = StrUserSearchValue; reader = cmd.ExecuteReader(); Any ideas? Thanks Rory

                  O 1 Reply Last reply
                  0
                  • M mccarthy111

                    I have tried to parameterize the query to see if that would work. again the Drug No search works but the DrugName search returns the following error: Input string was not in a correct format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format. Here is my updated code: string StrUserSearchValue = TextBox1.Text; OleDbCommand cmd = new OleDbCommand("Select * FROM DRUG_INFORMATION WHERE DRUG_INFORMATION.DrugNo = ? OR DRUG_INFORMATION.DrugName = ?", conn); cmd.Parameters.Add(new OleDbParameter("@DrugNo", OleDbType.Integer)).Value = StrUserSearchValue; cmd.Parameters.Add(new OleDbParameter("@DrugName", OleDbType.VarChar)).Value = StrUserSearchValue; reader = cmd.ExecuteReader(); Any ideas? Thanks Rory

                    O Offline
                    O Offline
                    onlytech
                    wrote on last edited by
                    #9

                    OK , back to square one... Try this and see: Regex isNumb=new Regex("[^0-9]"); string sqlStr= "Select * FROM DRUG_INFORMATION WHERE DrugName = '" + StrUserSearchValue + "'"; if (isNumb.IsMatch(StrUserSearchValue) ==false) sqlStr+= " or DrugNo =" + StrUserSearchValue; OleDbCommand cmd = new OleDbCommand(sqlStr, conn); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); You will have to include the following statement at top: using System.Text.RegularExpressions; Well, what we are doing here is to first find whether the search string is a Number or text. If number, then only we should include the "DrugNo = ?" criteria in the where clause of SQL query. hth -geo Nothing is impossible!

                    1 Reply Last reply
                    0
                    Reply
                    • Reply as topic
                    Log in to reply
                    • Oldest to Newest
                    • Newest to Oldest
                    • Most Votes


                    • Login

                    • Don't have an account? Register

                    • Login or register to search.
                    • First post
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • World
                    • Users
                    • Groups