asked on

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.

cyberkiwi

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..

l3m0n

ASKER

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.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer