Updating tables while Inner Joined

[Bladerunner]

Hey everyone,

I'm doing a C# program, the situation is a clothing shop with tables for Clients, Items, and Transactions.

The first part is easy: just creating a C# front end for the Clients and Items.

For the transactions part of the front-end however, fields from both the transactions table as well as the items table should be imported.

All of this works fine, except my third table in the front-end (the custom transactions one), which can't UPDATE due to the fact that it contains an INNER JOIN.

Any ideas on how to circumvent this? I've been trying since saturday, to no avail.

Here's my latest shot at it:

Code: Select all

        private void toolBtnSaveTrans_Click(object sender, EventArgs e)   //Custom Save
        {
            string sTransDate = "", sClientID = "", sDescription = "", sRetailPrice = "";
            bsTrans.EndEdit();

            for (int iRow = 0; iRow <= dgTrans.RowCount - 2; iRow++)
            {
                for (int iCol = 0; iCol <= dgTrans.ColumnCount - 1; iCol++)
                {                   
                    if (iCol == 0)
                    {
                        sTransDate = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else if (iCol == 1)
                    {
                        sClientID = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else if (iCol == 2)
                    {
                        sDescription = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else //iCol == 3
                    {
                        sRetailPrice = dgTrans[iCol, iRow].Value.ToString();
                    }                
                }                
                daShop.UpdateCommand = cnShop.CreateCommand();

                daShop.UpdateCommand.CommandText = "UPDATE Transactions "
                                                 + "SET ClientID='" + sClientID + "', TransactionDate='" + sTransDate + "' "
                                                 + "WHERE TransactionNumber='" + iRow + "' ";

                daShop.UpdateCommand.Parameters.Add("ClientID", OleDbType.Char, 0, "ClientID");
                daShop.UpdateCommand.Parameters.Add("TransactionDate", OleDbType.Char, 0, "TransactionDate");
                daShop.Update(dstShop, "Transactions");
            }
        } 

Sloppy I know, but right now I'm just desperate to get it to save.

[Ron2K]

Please post the SQL statement that contains the INNER JOIN (I only saw one statement in your code, and it doesn't have a join), as well as your database schema. That will give me something to work with.

[Bladerunner]

Please post the SQL statement that contains the INNER JOIN (I only saw one statement in your code, and it doesn't have a join), as well as your database schema. That will give me something to work with.

Code: Select all

SELECT        Transactions.TransactionNumber, Transactions.TransactionDate, Transactions.ClientID, Items.Description, Items.RetailPrice
FROM            (Transactions INNER JOIN Items ON Transactions.ItemID = Items.ItemID)

[Bladerunner]

If this will be of any help as well, this is the custom table:

[doo_much]

Try this

Code: Select all

        private void toolBtnSaveTrans_Click(object sender, EventArgs e)   //Custom Save
        {
            string sTransDate = "", sClientID = "", sDescription = "", sRetailPrice = "";
            bsTrans.EndEdit();

            for (int iRow = 0; iRow <= dgTrans.RowCount - 2; iRow++)
            {
                for (int iCol = 0; iCol <= dgTrans.ColumnCount - 1; iCol++)
                {                   
                    if (iCol == 0)
                    {
                        sTransDate = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else if (iCol == 1)
                    {
                        sClientID = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else if (iCol == 2)
                    {
                        sDescription = dgTrans[iCol, iRow].Value.ToString();
                    }

                    else //iCol == 3
                    {
                        sRetailPrice = dgTrans[iCol, iRow].Value.ToString();
                    }                
                }                
                daShop.UpdateCommand = cnShop.CreateCommand();

//                daShop.UpdateCommand.CommandText = "UPDATE Transactions "
//                                                 + "SET ClientID='" + sClientID + "', TransactionDate='" + sTransDate + "' "
//                                                 + "WHERE TransactionNumber='" + iRow + "' ";
//
                   daShop.UpdateCommand.CommandText = "UPDATE Transactions"
                                                    + "INNER JOIN Items  ON Transactions.ItemID = Items.ItemID"
                                                    + "SET ClientID='" + sClientID + "', TransactionDate='" + sTransDate + "' "
                                                    + "WHERE TransactionNumber='" + iRow + "' ";

                daShop.UpdateCommand.Parameters.Add("ClientID", OleDbType.Char, 0, "ClientID");
                daShop.UpdateCommand.Parameters.Add("TransactionDate", OleDbType.Char, 0, "TransactionDate");
                daShop.Update(dstShop, "Transactions");
            }
        } 

Sloppy I know, but I can't program worth a damn!

[Bladerunner]

Try this

Code: Select all

code

Sloppy I know, but I can't program worth a damn!

Thanks for the reply. The program still runs normally, and I know the for-loops work, but for some reason the data isn't updated. I think the SQL is probably okay now, but the update method is still giving me problems.