• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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.
0
l3m0n
Asked:
l3m0n
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now