none
how to have user input and query a sql Database using c#

    Question

  • I am having difficulties with getting rows for data. I am currently using the SQLDataAdapter to get the results and put them into a database. I figured out how to get one column but I want a row instead of just a column. How I am going to have it is if a user inputs the primary key then it will dispaly the results of that 1 item. However, if the user inputs several different options I want to query the sql database to give me all relavant data from the selected items. For example, if someone enters the primary key number and fills out several other data then the database will ignore all information except the primary key. However, if someone inputs the serial number, cost (with a feature to select a range that is equal to greater than or less than the amount typed), and owner name, then I want to display everything by that owner with that serial number and with the cost (or range of cost). But what I want is for the table to display the entire row of everything that has those particular items in it (like a filter). So items 1-4 and 7 all have Harddrive stored in the items column, I want the table to display only rows 1-4 and 7 (everything in them like the owner, cost, etc.).

    Here is my code now, it just displays the first column when you enter the data:

    private void SearchButton_Click(object sender, EventArgs e) { string numberCheck = InputTextBox.Text.Trim(); string costCheck = LookUpCostTextBox.Text.Trim(); string sortByCost = LookUpCostComboBox.Text; bool isNotNumber = true; for (int i = 0; i < numberCheck.Length; i++) { if (!char.IsNumber(numberCheck[i])) { isNotNumber = true; break; } else { isNotNumber = false; } } // verify that cost is valid for (int i = 0; i < costCheck.Length; i++) { if (!char.IsNumber(costCheck[i])) { isNotNumber = true; break; } else { isNotNumber = false; } } if (numberCheck.Trim() != "" && isNotNumber == true) { MessageBox.Show("You must enter a valid integer", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (costCheck.Trim() != "" && isNotNumber == true) { MessageBox.Show("You must enter a valid integer", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { // close the form and return to the previous event handler with ID being changed LookUpForm.Close(); string myRequest = "SELECT "; string serialNumber = LookUpSerialTextBox.Text; string partNumber = LookUpPartNumberTextBox.Text; string otherNumber = LookUpOtherNumberTextBox.Text; string generation = LookUpGenerationComboBox.Text; string owner = LookUpOwnerComboBox.Text; string productCategory = LookUpItemComboBox.Text; int cost = 0; int gdrNumber = 0; if (InputTextBox.Text.Trim() != "") gdrNumber = Convert.ToInt32(InputTextBox.Text); if (LookUpCostTextBox.Text.Trim() != "") cost = Convert.ToInt32(LookUpCostTextBox.Text); if (InputTextBox.Text.Trim() != "") myRequest += "GDR_ID#[" + gdrNumber + "] from Items"; string myConn = "Persist Security Info=False; User ID=joe;Initial Catalog=Inventory;" + "Password=inventory;" + "Data Source=servername"; try { SqlConnection myConnection = new SqlConnection(myConn); SqlCommand myQuery = new SqlCommand(myRequest, myConnection); SqlDataAdapter dataAdapter = new SqlDataAdapter(myQuery); DataTable table = new DataTable(); dataAdapter.Fill(table); OutputTable.DataSource = new BindingSource(table, null); } catch (Exception ie) { MessageBox.Show(ie.Message); } OutputTable.RowHeadersVisible = false; Table.ShowDialog(); } }
    I know there are some issues though I am not sure how you only select a row instead of columns, any ideas?

     



    • Edited by John_916 Wednesday, April 27, 2011 7:30 PM Update
    Monday, April 25, 2011 11:08 PM

Answers

  • Thank you everyone, I was able to figure it out from a different thread for something totally different but it worked. My code is as follows for anyone who is interested or needs something similar. It took a while to get all the bugs and kinks out but I finally got it:

            LookUpForm.Close();
            string myRequest = "SELECT * FROM Items WHERE";
    
            string serialNumber; 
            string partNumber = LookUpPartNumberTextBox.Text;
            string otherNumber = LookUpOtherNumberTextBox.Text;
            string generation = LookUpGenerationComboBox.Text;
            string owner = LookUpOwnerComboBox.Text;
            string productCategory = LookUpItemComboBox.Text;
    
            SqlParameter myParam = new SqlParameter("@Param1", SqlDbType.VarChar, 20);
    /*        SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.VarChar, 11);
            SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.VarChar, 11);
            SqlParameter myParam4 = new SqlParameter("@Param4", SqlDbType.VarChar, 11);
            SqlParameter myParam5 = new SqlParameter("@Param5", SqlDbType.VarChar, 11);
            SqlParameter myParam6 = new SqlParameter("@Param6", SqlDbType.VarChar, 11);
    */
            int cost;
            int gdrNumber;
    
    
            if (InputTextBox.Text.Trim() != "")
            {
              gdrNumber = Convert.ToInt32(InputTextBox.Text);
              myParam.Value = gdrNumber;
              myRequest += " GDR_ID# = @Param1";
            }
            else
            {
              if (LookUpSerialTextBox.Text.Trim() != "")
              {
                serialNumber = LookUpSerialTextBox.Text;
                myParam.Value = serialNumber;
                myRequest += " Serial_Number = @Param1";
              }
              if (LookUpCostTextBox.Text.Trim() != "")
              {
                cost = Convert.ToInt32(LookUpCostTextBox.Text);
                myParam.Value = Convert.ToString(cost);
                if (LookUpCostComboBox.Text.Trim() == "=")
                  myRequest += " Cost = " + cost;
                else if (LookUpCostComboBox.Text.Trim() == ">")
                  myRequest += " Cost > " + cost;
                else if (LookUpCostComboBox.Text.Trim() == "<")
                  myRequest += " Cost < " + cost;
                else
                  myRequest += " Cost = " + cost;
              }
              if (LookUpPartNumberTextBox.Text.Trim() != "")
              {
                partNumber = LookUpPartNumberTextBox.Text;
                myParam.Value = partNumber;
                myRequest += " Part_Number = @Param1";
              }
              if (LookUpOtherNumberTextBox.Text.Trim() != "")
              {
                otherNumber = LookUpOtherNumberTextBox.Text;
                myParam.Value = otherNumber;
                myRequest += " Other_Number = @Param1";
              }
              if (LookUpGenerationComboBox.Text != "")
              {
                generation = LookUpGenerationComboBox.Text;
                myParam.Value = generation;
                myRequest += " Generation = @Param1";
                MessageBox.Show(generation);
                MessageBox.Show(myParam.Value.ToString());
              }
              if (LookUpItemComboBox.Text != "")
              {
                productCategory = LookUpItemComboBox.Text;
                myParam.Value = productCategory;
                myRequest += " Product_Category = @Param1";
              }
              if (LookUpOwnerComboBox.Text != "")
              {
                owner = LookUpOwnerComboBox.Text;
                myParam.Value = owner;
                myRequest += " Owner = @Param1";
              }
            }
            string myConn = "Persist Security Info=False; User ID=joe;Initial Catalog=Inventory;" +
                                    "Password=inventory;" +
                                    "Data Source=servername";
    
            try
            {
              SqlConnection myConnection = new SqlConnection(myConn);
              SqlCommand myQuery = new SqlCommand(myRequest, myConnection);
              myQuery.Parameters.Add(myParam);          
              SqlDataAdapter dataAdapter = new SqlDataAdapter(myQuery);
              DataTable table = new DataTable();
              dataAdapter.Fill(table);
              OutputTable.DataSource = new BindingSource(table, null);
            
            }
            catch (Exception ie)
            {
              MessageBox.Show(ie.Message);
            }
            myConn = null;        
            myRequest = null;
            OutputTable.RowHeadersVisible = false;
            Table.ShowDialog();
          }
    I will make more changes to it to make it look better and more functionality for what I am going to do that is not important to this thread. Thanks again everyone and I hope the above helps out for your needs.
    • Marked as answer by John_916 Wednesday, April 27, 2011 7:28 PM
    Wednesday, April 27, 2011 7:28 PM

All replies

  • Hi,

    Look at here mate. This is how you can use the values from a specifc columns:

     

          DataTable table = new DataTable("myTable");
          DataColumn[] columns = new DataColumn[] { new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)) };
          table.Columns.AddRange(columns);
          //set Primary key:
          table.PrimaryKey = new DataColumn[] { table.Columns["Id"] };
          table.Rows.Add(1, "name 1");
          table.Rows.Add(2, "name 2");
          table.Rows.Add(3, "name 3");
    
          //show only names in the controls:
          //1.
          textBoxName.Text = table.Rows[0]["Name"].ToString();
          //2.1
          foreach (DataRow dr in table.Rows)
            listBox1.Items.Add(dr[1]);
          //2.2
          for (int i = 0; i < table.Rows.Count; i++)
            listBox1.Items.Add(table.Rows[i]["Name"].ToString());
    
          //the results will be now double, becuase I add them twice!!
    


    Mitja
    Tuesday, April 26, 2011 8:18 AM
  • I was able to figure out from my code above where I went wrong but now I am having difficulties with other fields. For example, I want all data in a row to appear based on the filter of the column. My code looks something like this:
    string myRequest = "SELECT * FROM Items WHERE ";
    
    if (gdr_id.textbox.trim() != "") // if there is something in the textbox that is not a blank space then do something
    
    {
    
       gdrNumber = convert.toint32(gdr_id.textbox);
    
       myRequest += "GDR_ID# = " + gdrNumber;
    
    }
    
    else
    
    {
    
       if (serialNumber.textbox.text.trim() != "")
    
       {
    
          serialNumber = serialNumber.textbox;
    
          myRequest += "Serial_Number = " + serialNumber;
    
       }
    
    }
    
        string myConn = "Persist Security Info=False; User ID=joe;Initial Catalog=Inventory;" +
    
                    "Password=inventory;" +
    
                    "Data Source=severname";
    
    
    
        try
    
        {
    
         SqlConnection myConnection = new SqlConnection(myConn);
    
         SqlCommand myQuery = new SqlCommand(myRequest, myConnection);
    
         SqlDataAdapter dataAdapter = new SqlDataAdapter(myQuery);
    
         DataTable table = new DataTable();
    
         dataAdapter.Fill(table);
    
         OutputTable.DataSource = new BindingSource(table, null);
    
    
    
        }
    
        catch (Exception ie)
    
        {
    
         MessageBox.Show(ie.Message);
    
        }
    
     
    
    
    But it is giving me an invalid column name and it displays what ever was in the text box. The variable for the textbox is different than that of the column name in the database. What am I doing wrong?
    • Edited by John_916 Wednesday, April 27, 2011 7:28 PM Update
    Tuesday, April 26, 2011 4:00 PM
  • first, to get value from textbox, there is a .text property, for ex- yourtextboxname.text 

    second, if you are using string value in sql, it has to be within single quotes. 

    Check code below, i have modified your code with above scenarios. If its something else you want, please explain it.

     

     

    string myRequest = "SELECT * FROM Items WHERE ";
    if (gdr_id.text.trim() != "") // if there is something in the textbox that is not a blank space then do something
    {
      gdrNumber = convert.toint32(gdr_id.text);
      myRequest += "GDR_ID# = " + gdrNumber;
    }
    else
    {
      if (serialNumber.text.trim() != "")
      {
       serialNumber = serialNumber.text;
       myRequest += "Serial_Number = '" + serialNumber + "'";
      }
    }
     
    

     

     


    Amit Govil(amit.govil@hotmail.com)
    Tuesday, April 26, 2011 5:25 PM
  • Thank you everyone, I was able to figure it out from a different thread for something totally different but it worked. My code is as follows for anyone who is interested or needs something similar. It took a while to get all the bugs and kinks out but I finally got it:

            LookUpForm.Close();
            string myRequest = "SELECT * FROM Items WHERE";
    
            string serialNumber; 
            string partNumber = LookUpPartNumberTextBox.Text;
            string otherNumber = LookUpOtherNumberTextBox.Text;
            string generation = LookUpGenerationComboBox.Text;
            string owner = LookUpOwnerComboBox.Text;
            string productCategory = LookUpItemComboBox.Text;
    
            SqlParameter myParam = new SqlParameter("@Param1", SqlDbType.VarChar, 20);
    /*        SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.VarChar, 11);
            SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.VarChar, 11);
            SqlParameter myParam4 = new SqlParameter("@Param4", SqlDbType.VarChar, 11);
            SqlParameter myParam5 = new SqlParameter("@Param5", SqlDbType.VarChar, 11);
            SqlParameter myParam6 = new SqlParameter("@Param6", SqlDbType.VarChar, 11);
    */
            int cost;
            int gdrNumber;
    
    
            if (InputTextBox.Text.Trim() != "")
            {
              gdrNumber = Convert.ToInt32(InputTextBox.Text);
              myParam.Value = gdrNumber;
              myRequest += " GDR_ID# = @Param1";
            }
            else
            {
              if (LookUpSerialTextBox.Text.Trim() != "")
              {
                serialNumber = LookUpSerialTextBox.Text;
                myParam.Value = serialNumber;
                myRequest += " Serial_Number = @Param1";
              }
              if (LookUpCostTextBox.Text.Trim() != "")
              {
                cost = Convert.ToInt32(LookUpCostTextBox.Text);
                myParam.Value = Convert.ToString(cost);
                if (LookUpCostComboBox.Text.Trim() == "=")
                  myRequest += " Cost = " + cost;
                else if (LookUpCostComboBox.Text.Trim() == ">")
                  myRequest += " Cost > " + cost;
                else if (LookUpCostComboBox.Text.Trim() == "<")
                  myRequest += " Cost < " + cost;
                else
                  myRequest += " Cost = " + cost;
              }
              if (LookUpPartNumberTextBox.Text.Trim() != "")
              {
                partNumber = LookUpPartNumberTextBox.Text;
                myParam.Value = partNumber;
                myRequest += " Part_Number = @Param1";
              }
              if (LookUpOtherNumberTextBox.Text.Trim() != "")
              {
                otherNumber = LookUpOtherNumberTextBox.Text;
                myParam.Value = otherNumber;
                myRequest += " Other_Number = @Param1";
              }
              if (LookUpGenerationComboBox.Text != "")
              {
                generation = LookUpGenerationComboBox.Text;
                myParam.Value = generation;
                myRequest += " Generation = @Param1";
                MessageBox.Show(generation);
                MessageBox.Show(myParam.Value.ToString());
              }
              if (LookUpItemComboBox.Text != "")
              {
                productCategory = LookUpItemComboBox.Text;
                myParam.Value = productCategory;
                myRequest += " Product_Category = @Param1";
              }
              if (LookUpOwnerComboBox.Text != "")
              {
                owner = LookUpOwnerComboBox.Text;
                myParam.Value = owner;
                myRequest += " Owner = @Param1";
              }
            }
            string myConn = "Persist Security Info=False; User ID=joe;Initial Catalog=Inventory;" +
                                    "Password=inventory;" +
                                    "Data Source=servername";
    
            try
            {
              SqlConnection myConnection = new SqlConnection(myConn);
              SqlCommand myQuery = new SqlCommand(myRequest, myConnection);
              myQuery.Parameters.Add(myParam);          
              SqlDataAdapter dataAdapter = new SqlDataAdapter(myQuery);
              DataTable table = new DataTable();
              dataAdapter.Fill(table);
              OutputTable.DataSource = new BindingSource(table, null);
            
            }
            catch (Exception ie)
            {
              MessageBox.Show(ie.Message);
            }
            myConn = null;        
            myRequest = null;
            OutputTable.RowHeadersVisible = false;
            Table.ShowDialog();
          }
    I will make more changes to it to make it look better and more functionality for what I am going to do that is not important to this thread. Thanks again everyone and I hope the above helps out for your needs.
    • Marked as answer by John_916 Wednesday, April 27, 2011 7:28 PM
    Wednesday, April 27, 2011 7:28 PM