none
Data Search RRS feed

  • Question

  •  

    Hi,

    Can anyone out there help me out with this.  I have a Database called db1, with a table called customer, a prinary key column called customerID, a column called CustomerName and a foreign key column called called DepartmentID.  I then have a second table called Department with a primary key column of DepartmentID and another column of Department name.  The 2 tables are linked via the DepartmentID column.  I have been able to add edit and delete without a problem.  What i am trying to do is search the department table for departments under a specific cutstomer.  I have the binding source filtering working correctly via a combobox i am using to select the customer.  However now i am trying to find a specific department via use of a textbox and so far have been unable to crack it.

    Any help would be much appreciated.

     

    Thanks

    Brett  

    Tuesday, November 6, 2007 3:17 AM

Answers

  • If all you need to do is loop through your DataTable and look for the appropriate Departments, then you probably want to try something like this:

     

    Code Block

    // First, get the DepartmentID

    DataRow[] rows = db1DataSet1.Department.Select("DepartmentName = '" +  DepTextbox1.Text + "'");

    if (rows.Length > 0)

        DepartmentID = rows[0]["DepartmentID"];

     

    // Then if you simply set up a View with the appropriate

    // filter, you don't have much else to do:

    // Set it to filter with the existing BindingSource

    // Filter and add the DepartmentID to it.

    DataView deptView = new DataView(db1DataSet1.Customer);

    deptView.RowFilter = this.DepbindingSource1.Filter + "DepartmentID = '" + DepartmentID + "'";

     

    // Now spin through the View's rows

    DataRow row;

    DataRowView rowView;

    for (int i = 0; i < deptView.Count; i++)

    {

        // do something with the data, using DataRow

        row = deptView[i].Row;

     

        // or using DataRowView, one or the the other

        // you don't need both

        rowView = deptView[i];

    }

     

     

     

    Does that help some?

    Wednesday, November 7, 2007 12:03 AM

All replies

  • There are many ways to skin a cat.

     

    Could you post the relevant bits of code that you already have so that we can make suggestions based on what you're already doing.

    Tuesday, November 6, 2007 5:07 AM
  • I am using visual c# 2008 and the database is a local database so a compact sql.  What i have so far is:

     

    public partial class EditDepart : Form

    {

    public EditDepart()

    {

    InitializeComponent();

    }

    //Fills the table adapters and filters appropriately

    private void EditDepart_Load(object sender, EventArgs e)

    {

    this.customerTableAdapter1.Fill(db1DataSet1.Customer);

    this.departmentTableAdapter1.Fill(db1DataSet1.Department);

    this.DepbindingSource1.Filter = "CustomerID=" + CstcomboBox1.SelectedValue;

    }

    //Filters the bindingsource on combobox change

    private void CstcomboBox1_SelectedIndexChanged(object sender, EventArgs e)

    {

    this.DepbindingSource1.Filter = "CustomerID=" + CstcomboBox1.SelectedValue;

    }

    private void button1_Click(object sender, EventArgs e)

    {

    ?

    ? data search

    ?

    ?

    }

    }

    have tried using foreach loops to look through the data to to try and find it but all i can get it to do is find if the column name exists so may have my syntax wrong.  have also tried the following

     

    bool x = this.DepbindingSource1.Contains(DepTextbox1.Text);

    if (x = true)

    {

    //Deal with data

    }

    else

    {

    Messagebox.Show("No Data Found");

    }

    The contains method is supposed to see if the list contains the specified data however all results come back as false.  Also when i try to add to the value section of the contains statement CstcomboBox1.SelectedValue i get an error stating that i am probably missing a definition.  Have not been able to find any useful data on how the contains statement is supposed to work so may have incorrect syntax in using this, or may be using it in a way its not meant to be used.

    Any suggestions

    Thanking You

    Brett 

    Tuesday, November 6, 2007 9:43 PM
  • If all you need to do is loop through your DataTable and look for the appropriate Departments, then you probably want to try something like this:

     

    Code Block

    // First, get the DepartmentID

    DataRow[] rows = db1DataSet1.Department.Select("DepartmentName = '" +  DepTextbox1.Text + "'");

    if (rows.Length > 0)

        DepartmentID = rows[0]["DepartmentID"];

     

    // Then if you simply set up a View with the appropriate

    // filter, you don't have much else to do:

    // Set it to filter with the existing BindingSource

    // Filter and add the DepartmentID to it.

    DataView deptView = new DataView(db1DataSet1.Customer);

    deptView.RowFilter = this.DepbindingSource1.Filter + "DepartmentID = '" + DepartmentID + "'";

     

    // Now spin through the View's rows

    DataRow row;

    DataRowView rowView;

    for (int i = 0; i < deptView.Count; i++)

    {

        // do something with the data, using DataRow

        row = deptView[i].Row;

     

        // or using DataRowView, one or the the other

        // you don't need both

        rowView = deptView[i];

    }

     

     

     

    Does that help some?

    Wednesday, November 7, 2007 12:03 AM
  • Thanks will give it a go and let you know

    Wednesday, November 7, 2007 2:19 AM
  • Thanks for help, however it isn't exactly what im looking for.  I don't want to use dataviews all i want to do basiclly is type a name in a textbox and if it exists as a department under that customer display a messagebos to say so and then if not deal with the data as required.  I need to search this way because i need unique data but can't use the unique property as that aplies to the whole column not just for specified foreign keys.  Then later in the program i have to search for data and deal with it using a serial port.  Thus i can't use a data view as later in the program there will be no human interaction. 

    Any help would be much appreciated.

    Thanks Brett

    Wednesday, November 7, 2007 4:14 AM
  • Using a DataView will not hinder your requirement that there be no human interaction.

     

    And I'm still not sure, given what you've just said, why you can't use what I posted to determine if the department exists.

     

    I must be missing some key point.

    Wednesday, November 7, 2007 4:22 AM
  • The following is the majority of the code and i have added in your suggested additional code

     

    DataRow[] rows = mndwnDataSet1.Department.Select("DepartmentName = '" + DeptextBox1.Text + "'");

    if (rows.Length > 0)

    {

    departmentID = rows[0]["DepartmentID"];

    DataView deptView = new DataView(mndwnDataSet1.Customer);

    deptView.RowFilter = this.DepbindingSource1.Filter + "DepartmentID = '" + departmentID + "'";

    DataRow row;

    DataRowView rowView;

    for (int i = 0; i < deptView.Count; i++)

    {

    if (?)

    {

    mndwnDataSet.DepartmentRow Depart = mndwnDataSet1.Department.NewDepartmentRow();

    Depart.CustomerID = int.Parse(CustcomboBox1.SelectedValue.ToString());

    Depart.DepartmentName = DeptextBox1.Text;

    mndwnDataSet1.Department.AddDepartmentRow(Depart);

    DeptextBox1.Clear();

    }

    else

    {

    MessageBox.Show("Data Exists");

    }

    }

    }

    else

    {

    mndwnDataSet.DepartmentRow Depart = mndwnDataSet1.Department.NewDepartmentRow();

    Depart.CustomerID = int.Parse(CustcomboBox1.SelectedValue.ToString());

    Depart.DepartmentName = DeptextBox1.Text;

    mndwnDataSet1.Department.AddDepartmentRow(Depart);

    DeptextBox1.Clear();

    }

     

    What i don't undestand with this is as you can see in the if statement with the ? in it is once i count through the rows how i find if a possitive result or even a negative result is returned.

     

    Thanks Brett

    Wednesday, November 7, 2007 5:00 AM
  • Brett,

     

    If there are any rows at all in the deptView, then the Data exists (since you are filtering the View for the combination of CustomerID and DepartmentID).

     

    So, you really don't need to loop through the View, you just need to know if there are any rows (sorry, I assumed you wanted to do something with the existing rows).

     

    Use the .Select again to determine that and forget what I said about using the DataView :

    Code Block

    DataRow[] rows = mndwnDataSet1.Department.Select("DepartmentName = '" + DeptextBox1.Text + "'");

    if (rows.Length > 0)

    {

        departmentID = rows[0]["DepartmentID"];

        rows = mndwnDataSet1.Customer.Select(this.DepbindingSource1.Filter + " AND DepartmentID = '" + departmentID + "'");

        if (rows.Length > 0)

            MessageBox.Show("Data Exists");

        else

        {

            mndwnDataSet.DepartmentRow Depart = mndwnDataSet1.Department.NewDepartmentRow();

            Depart.CustomerID = int.Parse(CustcomboBox1.SelectedValue.ToString());

            Depart.DepartmentName = DeptextBox1.Text;

            mndwnDataSet1.Department.AddDepartmentRow(Depart);

            DeptextBox1.Clear();

        }

    }

     

     

    This should do it for ya.

    Wednesday, November 7, 2007 5:18 AM
  • Whenecer i try to complie this i keep getting an error saying that departmentID does not exist in current content.

    is it meant to be bool departmentID = rows[0]["DepartmentID"]; , or as an int or am i possibly missing a pointer.

    Thanks Brett

    Wednesday, November 7, 2007 5:56 AM
  • Well, you have to define it first of course and no it's not a bool.

     

    If your DepartmentID in your database is an int, make it an int ... if it's a string,make it a string. All you're doing here is setting a variable, departmentID, equal to the DepartmentID in the Table of the row you just found. I thought you understood that. Sorry.

    Wednesday, November 7, 2007 2:24 PM
  • It now is sort of working, i had to change the line rows = mndwnDataSet1.Customer.Select(this.DepbindingSource1.Filter + " AND DepartmentID = '" + departmentID + "'"); to

    rows = mndwnDataSet1.Customer.Select(this.DepbindingSource1.Filter + " AND CustomerID = '" + departmentID + "'");

    However it will only check the first entry and compare that.  What i need to do is check it against every entry in the filtered view to see if it exists.  Any thoughts

    Thanking You

    Brett

    Wednesday, November 7, 2007 10:55 PM
  • Now I am totally lost. You're supposed to be getting (with the Customer.Select() method) all the rows that have both the CustomerID (which is what your DepbindingSource.Filter does) AND the DepartmentID. What you switched it to makes no sense.

     

    You said earlier that you just wanted to know if the department (entered in a textbox by the user) was contained in your initially filtered set of rows based on the CustomerID. What I posted should do that. In other words, the rows array will contain all the rows from your Customer table that satisfies that criteria. What am I missing?

    Wednesday, November 7, 2007 11:19 PM
  • What is happening under testing is that it is only checking it against the data in the first ro of the filtered source.  Is it in the line

    departmentID = rows[0]["DepartmentID"];

    where it sets it up with looking only at the first row or is my understanding of this incorrect.

    Thanks

    Brett

    Wednesday, November 7, 2007 11:35 PM
  • WRT why i had to change it to CustomerID instead o DepartmentID was that under testing it keeps throwing an exemption saying that there is no DepartmentID column in the Customer table which is correct.

    Brett

    Thursday, November 8, 2007 12:01 AM
  • Still lost here.

     

    You said, in your very first post:

     

    I have a Database called db1, with a table called customer, a prinary key column called customerID, a column called CustomerName and a foreign key column called called DepartmentID.  I then have a second table called Department with a primary key column of DepartmentID and another column of Department name.

     

    According to the above quote, a foreign key column called DepartmentID exists in your Customer table  ... and now you say there isn't one.

     

    What I was attempting to do with the following code, was to get an array of DataRows corresponding to all Departments in the Department table with the DepartmentName equal to whatever the user put in the TextBox:

     

    Code Block

    DataRow[] rows = mndwnDataSet1.Department.Select("DepartmentName = '" + DeptextBox1.Text + "'");

     

     

    OK, so now, if there are any rows at all, then that means you need to get the DepartmentID (and I only get the ID from the first [0], since I'm assuming that the same DepartmentName will have the same DepartmentID.

    Code Block

    if (rows.Length > 0)

    {

        departmentID = rows[0]["DepartmentID"];

     

     

    And now we need to see if any rows in the Customer table (filtered by CustomerID) have that DepartmentID ... assuming that there is a DepartmentID foreign key in that Customer table, like you originally said there was:

    Code Block

        rows = mndwnDataSet1.Customer.Select(this.DepbindingSource1.Filter + " AND DepartmentID = '" + departmentID + "'");

     

     

    If there are any, then rows.Length will be greater than zero ... etc.etc.etc.

     

    Now, what did I miss?

     

     

    Thursday, November 8, 2007 12:38 AM
  • Sorry about that there was a typo made a the start and i think that is what has caused all the confussion.  The databas is as follows:

    Table 1 is the called Customer, its columns are CustomerID(Primary Key), CustomerName and RadioID.  Table 2 is called Department, its columns are DepartmentID(Primary Key), DepartmentName and CustomerID.  The Foreign key relationship between the 2 is done via the CustomerID column of both tables.  The reason i need to do this search is because 2 seperate customers say CustomerX and CustomerY may both have a department of the same name.  For example they may both have a Sales Department.  However what i am trying to eliminate is when someone enters a Department name more than once under the same Customer as this will cause issues later on down the track.  Again sorry for ant confussion i may have caused back at the start.

    Brett

    Thursday, November 8, 2007 12:54 AM
  • Thanks for your help on all this now i understand where the confussion was i now have it working.  Once i changed the line

    departmentID = rows[0]["DepartmentID"];

    to

    departmentID = rows[0]["CustomerID"];

    it all works fine.  Again sorry for the confussion and thanks for your help on this.  I am new to all this have only ever done DOS based applications and have never had to deal with databases before.

    Thanking you

    Brett

    Thursday, November 8, 2007 1:13 AM
  • Brett,

     

    You're welcome!!! At least now it's working. Hope I didn't sound too frustrated with my posts (it's been one of those days ... most of my day has been spent explaining stuff to my co-workers who don't seem to get it ... they're all newbies to .NET).

     

    I'd change that line one more time to:

    customerID = rows[0]["CustomerID"];

     

    Less likely to be confusing!

    Thursday, November 8, 2007 2:01 AM
  • Roger will do and once again thanks for everything, like i said i am new to databasing with c# and had most of it worked out but this just had me thrown for about 2 weeks now.  Thanks againg youy really saved me and hope i didn't stress you too much.

    Brett

    Thursday, November 8, 2007 2:24 AM
  • Nah,  not stressed too much.

     

    You're welcome!

    Thursday, November 8, 2007 3:32 AM