Clever Geek Handbook

Retrieving data from a query in multiple tables in C #

Ok, so I have a C # program that I am working on and I am having a problem while trying to query my database.

I have three tables (don't think about names, they are generalized for the purpose of this question) - CUSTOMER, CUSTORD and ORDERS

CLIENT

  • Enter the custID field

ORDERS

  • A field named orderID

CUSTORD

  • Many to many relationships - one customer can have many orders and one order can have many customers.
  • Contains ...
    • custID (related to CUSTOMER.custID)
    • orderID (associated with ORDERS.orderID)

Basically, I need a way to insert the custID and get a list of all the orders the customer has and display them in the dataGridView.

What i tried

I tried to add queries to various table adapters using the Query Configuration Wizard in the DataSet Designer. The main problem is that when I try to use JOIN, it gives me a "JOIN expression not supported" error. JOIN is the only way I know how to bind data using SQL.

I also tried adding the code manually, but I can never get the information in the right format to display in the dataGridView. If I can order all the orderIDs associated with the custID into an array, maybe that's where I can find a solution. I can figure out how to iterate over this information, querying according to each sibID, but how can I compile all this information into one tableAdapter that will be displayed in the dataGridView?

To be honest, something is helping and if you need more information just let me know.

Specifications

  • IDE: Visual Studio Ultimate 2012
  • Database created using: Microsoft Access 2010
  • Language: C #

Edit

So, I hit the road to put everything into an array that I can parse later. still getting some errors, but maybe it sheds some more light on what I am trying to do. The error I receive is "Failed to enable constraints. One or more rows contain values ​​that violate non-empty, unique or foreign key constraints." I double checked and nothing happened and nothing repeated. It could be a foreign key, but how can I solve this? Here's the code ...

  • GetSelectedCustOrders takes a custID and requests a CUSTORD to match the orderID
    • SELECT orderID FROM CUSTORD WHERE custID = selectedCust

DBDataSetTableAdapters.CUSTORDTableAdapter CUSTORDTableAdapter = new DBDataSetTableAdapters.CUSTORDTableAdapter(); 

    string relationString = "-1";
    DataTable relationResult = new DataTable();

    relationResult = CUSTORDTableAdapter.GetSelectedCustOrders(Convert.ToInt16(custID));

    foreach (DataRow row in relationResult.Rows)
    {
        foreach (DataColumn column in relationResult.Columns)
        {
            relationString += Convert.ToString(row[column]);
            testLabel.Text = relationString;
        }
    }

Edit 2

So the reason I was getting the error above was because I was trying to change the table schema. If I change my SQL query query to ...

SELECT * FROM CUSTORD WHERE custID= selectedCust

... then it worked great! I have injected the index in my foreach, so I could only select the orderIDs and add them to the String relation. Now, just parse the information, convert it to int and run a query in ORDERS for the orderID.

+3


source to share


1 answer


So, I decided to solve the second query. At first I just populated my DataGridView with the entire ORDER table. By putting my orderID on a comma delimited string, I split them into an array. I disabled all rows in my DataGridView and checked the orderID of the row against the entire orderID in my sRelationArray. If it matched, I included the line again. The code looks like this:



private void setOrderTable()
    {
        string relationString = getRelatedOrders(); //gives the comma delimited string
        string[] sRelationArray = relationString.Split(new[] {','}, System.StringSplitOptions.RemoveEmptyEntries);
        CurrencyManager currencyManager1 = (CurrencyManager)BindingContext[dataGridView1.DataSource];

        try
        {
            this.oRDERSTableAdapter.Fill(this.DBDataSet.ORDERS);
        }
        catch (System.Exception ex)
        {
            System.Windows.Forms.MessageBox.Show("Error finding orders. \n" + ex.Message);
        }

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            currencyManager1.SuspendBinding();
            row.Visible = false;

            for (int i = 0; i < sRelationArray.Length; i++)
            {
                if (sRelationArray[i] == row.Cells[0].Value.ToString())
                {
                    row.Visible = true;
                }
            }
        }
        currencyManager1.ResumeBinding();
    }//end set orders table
+1


source






More articles:


All Articles