C# inserting GUID in MSSQL from one table to another

Hello, I need help with inserting GUID from one table to another in C# using MSSQL database, below is method that I wanted to use:

private void ImportInvoicesToDb()
        {
            for (int i = 0; i < Converter.InvoicesList.Count; i++)
            {
                SqlCommand Command = new SqlCommand();
                Command.Connection = DbConnection.Connection;

                Command.CommandText = @"INSERT INTO invoices(IdSeller)
                                        SELECT Id
                                        FROM sellers
                                        INSERT INTO invoices(IdBuyer)
                                        SELECT Id
                                        FROM buyers";

                Command.ExecuteNonQuery();

                Command.CommandText = @"INSERT INTO invoices(Date, Currency) VALUES ('"
                    + (Converter.InvoicesList[i] as InvoiceData).InvoiceMonth + "','"
                    + (Converter.InvoicesList[i] as InvoiceData).Currency + "')";

                int checkInsert = Command.ExecuteNonQuery();

                Console.WriteLine(checkInsert.ToString());
            }
        }

Open in new window


It says while debugging:

Cannot insert the value NULL into column 'IdBuyer', table 'Malendowicz--Invoices.dbo.invoices'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'IdSeller', table 'Malendowicz--Invoices.dbo.invoices'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The statement has been terminated.
l3m0nAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
What that means is, your table invoices has foreign keys on IdSeller and IdBuyer.  Trying to CREATE NEW RECORDS each time, each with only one field (IdSeller or IdBuyer) is not going to get you far.

INSERT INTO invoices(IdSeller)
SELECT Id
FROM sellers

This means - create new records in invoices table, using the Id from the Sellers table for the IdSeller column.  ALL OTHER columns are NULL.  This is a very bad way to write a query.

Not only that, your last insert create all-new records again, completely ignoring the two prior inserts, and also missing both idBuyer and idSeller columns..
0
l3m0nAuthor Commented:
Can you tell me then, how to make that only IdSeller inserts and rest are updated ? Was trying something like:
Command.CommandText = @"INSERT INTO invoices(IdSeller)
                                        SELECT IdSeller
                                        FROM sellers
                                        UPDATE invoices
                                        SET Date='" + (Converter.InvoicesList[i] as InvoiceData).InvoiceMonth + "'" + "," +
                                            "Currency='" + (Converter.InvoicesList[i] as InvoiceData).Currency + "'";

Open in new window


But it creates 4 records, while should create 2... and IdBuyer is still not updated.
0
cyberkiwiCommented:
I would suggest a book, or some online tutorial.  The question will only lead to 10 more questions.

You must insert into idseller and idbuyer in the same statement.  You simply cannot insert one and leave the other blank - not with the foreign keys present.

As for updating, you need a WHERE clause somewhere.  You don't ever want to update an entire table with just one set of date and currency values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.