Inserting multiple Values into Microsoft SQL Server via C#

Question

I have a database made up of 2 (more, actually, but only 2 im working with) tables.

The Material table consists solely of the material-number and the description

DPMatNr
DPBezeichnung

The Eigenschaften table is there to hold the properties of the materials.

It uses the columns:

EigenschaftenBezeichnerID
Wert (value)

My problem is: each entry in the Material table needs to have multiple entries in the Eigenschaften table.

For example:

"Material": 
      DPMatNr = 001, 
      DPBezeichnung = "Description"

"Eigenschaften": 
      EigenschaftenBezeichnerID = 1, 
      Wert = "A4"

      EigenschaftenBezeichnerID = 3, 
      Wert = "80" and so on.

My code currently looks like this:

public static void InsertData(string connectionstring, string matnummer, string bezeichnung, string format, string grammatur, string gewicht, string eform, string kuvertierung, string altkuvert)
{
    string query = @"Insert INTO dbo.Material (DPMatNr, DPBezeichnung)
                     VALUES (@matnummer, @bezeichnung)";
    string query2 = @"Insert INTO dbo.Eigenschaften 
                      (EigenschaftenBezeichnerID, Wert)
                      VALUES (@1, @format, @2, @grammatur, @3, @gewicht, 
                              @4, @eform, @5, @kuvertierung, 
                              @6, @altkuvert)";

    using (SqlConnection cn = new SqlConnection(connectionstring))
    using (SqlCommand cmd = new SqlCommand(query, cn))
    {
        cmd.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
        cmd.Parameters.Add("@bezeichnung", SqlDbType.VarChar, 50).Value = bezeichnung;

        cn.Open();
        cmd.ExecuteNonQuery();

        using (SqlCommand cmd2 = new SqlCommand(query2, cn))
        {
            cmd2.Parameters.Add("@1", SqlDbType.Int).Value = 1;
            cmd2.Parameters.Add("@format", SqlDbType.VarChar, 50).Value = format;
            cmd2.Parameters.Add("@2", SqlDbType.Int).Value = 2;
            cmd2.Parameters.Add("@grammatur", SqlDbType.VarChar, 50).Value = grammatur;
            cmd2.Parameters.Add("@3", SqlDbType.Int).Value = 3;
            cmd2.Parameters.Add("@gewicht", SqlDbType.VarChar, 50).Value = gewicht;
            cmd2.Parameters.Add("@4", SqlDbType.Int).Value = 4;
            cmd2.Parameters.Add("@eform", SqlDbType.VarChar, 50).Value = eform;
            cmd2.Parameters.Add("@5", SqlDbType.Int).Value = 5;
            cmd2.Parameters.Add("@kuvertierung", SqlDbType.VarChar, 50).Value = kuvertierung;
            cmd2.Parameters.Add("@6", SqlDbType.Int).Value = 6;
            cmd2.Parameters.Add("@altkuvert", SqlDbType.VarChar, 50).Value = altkuvert;
            cmd2.ExecuteNonQuery();

        }
        cn.Close();
    }
}

Now I currently get an error that says:

System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Material' with unique index 'IX_MatNrUnique'

What am I doing wrong?

@TomTom thats why i am here, the error tells me, I can not do the thing the way I do it at the moment, and as I am a newcomer to coding, I am in need of help from people who know what to do in this situation. Probably should have added, that i don't know what VS means with IX_MatNrUnique, as its nothing i've written — Olaf Klausson
– Olaf Klausson, Commented Jun 2, 2017 at 8:12
The table "Material" has a constraint on the column "DPMatNr" that doesn't allow two records with the same "DPMatNr" value. If you get this error then you are trying to insert a second record with the same "DPMatNr" value. — Steve
– Steve, Commented Jun 2, 2017 at 8:14
Your "Eigenschaften" table needs to reference somehow the "Material" table. In order to do that, you should add a Foreign Key to the "Eigenschaften" table referencing 'Material.DPMatNR'. Now you are trying to insert a row in "Material" with an ID which exists already — D Ie
– D Ie, Commented Jun 2, 2017 at 8:16
Another observation is that in query2 you specify 2 columns in INSERT statement, while number of values being inserted is 12. They should match. — the_joric
– the_joric, Commented Jun 2, 2017 at 8:18
Have you thought about creating a linking table? stackoverflow.com/questions/14978244/… — Flexicoder
– Flexicoder, Commented Jun 2, 2017 at 8:22

Romano Zumbé · Accepted Answer · 2017-06-02 08:21:47Z

The Problem here is, that for every "Eigenschaft" you insert into the table you also try to create an entry in the "Material" table. But since every material should only be inserted once (therefore the primary key) you get the error.

Edit: You could adjust your method like the following:

public static void InsertData(string connectionstring, string matnummer, string bezeichnung, string format, string grammatur, string gewicht, string eform, string kuvertierung, string altkuvert)
{
    string check = "Select COUNT(*) FROM dbo.Material where DPMatNr = @matnummer";
    string query = "Insert INTO dbo.Material (DPMatNr, DPBezeichnung)" + "VALUES (@matnummer, @bezeichnung)";
    string query2 = "Insert INTO dbo.Eigenschaften (EigenschaftenBezeichnerID, Wert)" + "VALUES (@1, @format, @2, @grammatur, @3, @gewicht, @4, @eform, @5, @kuvertierung, @6, @altkuvert)";


    using (SqlConnection cn = new SqlConnection(connectionstring))
    using (SqlCommand chkCom = new SqlCommand(check, cn))
    {
        cn.Open();
        chkCom.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
        int? matCnt = chkCom.ExecuteScalar() as int?;

        if (matCnt == 0 || matCnt == null)
        {
             using (SqlCommand cmd = new SqlCommand(query, cn))
             {
                  cmd.Parameters.Add("@matnummer", SqlDbType.VarChar, 50).Value = matnummer;
                  cmd.Parameters.Add("@bezeichnung", SqlDbType.VarChar, 50).Value = bezeichnung;

                  cmd.ExecuteNonQuery();
             }
        }

        using (SqlCommand cmd2 = new SqlCommand(query2, cn))
        {
             cmd2.Parameters.Add("@1", SqlDbType.Int).Value = 1;
             cmd2.Parameters.Add("@format", SqlDbType.VarChar, 50).Value = format;
             cmd2.Parameters.Add("@2", SqlDbType.Int).Value = 2;
             cmd2.Parameters.Add("@grammatur", SqlDbType.VarChar, 50).Value = grammatur;
             cmd2.Parameters.Add("@3", SqlDbType.Int).Value = 3;
             cmd2.Parameters.Add("@gewicht", SqlDbType.VarChar, 50).Value = gewicht;
             cmd2.Parameters.Add("@4", SqlDbType.Int).Value = 4;
             cmd2.Parameters.Add("@eform", SqlDbType.VarChar, 50).Value = eform;
             cmd2.Parameters.Add("@5", SqlDbType.Int).Value = 5;
             cmd2.Parameters.Add("@kuvertierung", SqlDbType.VarChar, 50).Value = kuvertierung;
             cmd2.Parameters.Add("@6", SqlDbType.Int).Value = 6;
             cmd2.Parameters.Add("@altkuvert", SqlDbType.VarChar, 50).Value = altkuvert;
             cmd2.ExecuteNonQuery();

         }
         cn.Close();
     }
}

Collectives™ on Stack Overflow

Inserting multiple Values into Microsoft SQL Server via C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related