CODEWITHSUNDEEP
c#sqlsql-serverinsert
BastianBuhrkall
BastianBuhrkall

Reputation: 348

SQL INSERT weirdly skipping first record

I am getting a list of objects from another class, and using it in my insert query.

Somehow I am getting a weird "error", because the first object in the list is not inserted.

If anyone can spot my mistake I'd be really happy! I have been banging my head against the keyboard for some time now...

        var ActNoParam = new SqlParameter();
        ActNoParam.ParameterName = "@ActNo";
        ActNoParam.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(ActNoParam);

        var CustNoParam = new SqlParameter();
        CustNoParam.ParameterName = "@CustNo";
        CustNoParam.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(CustNoParam);

        var DelpriParam = new SqlParameter();
        DelpriParam.ParameterName = "@DelPri";
        DelpriParam.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(DelpriParam);

        var CustPrg3Param = new SqlParameter();
        CustPrg3Param.ParameterName = "@CustPrg3";
        CustPrg3Param.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(CustPrg3Param);

        var NmParam = new SqlParameter();
        NmParam.ParameterName = "@Nm";
        NmParam.SqlDbType = SqlDbType.VarChar;
        cmd.Parameters.Add(NmParam);

        var Gr6Param = new SqlParameter();
        Gr6Param.ParameterName = "@Gr6";
        Gr6Param.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(Gr6Param);

        var CreUsrParam = new SqlParameter();
        CreUsrParam.ParameterName = "@CreUsr";
        CreUsrParam.SqlDbType = SqlDbType.VarChar;
        cmd.Parameters.Add(CreUsrParam);

        var CreDtParam = new SqlParameter();
        CreDtParam.ParameterName = "@CreDt";
        CreDtParam.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(CreDtParam);

        for (int i = 1; i < customers.Count; i++)
        {
            cmd.CommandText =
            @"IF NOT EXISTS (SELECT 1 FROM dbo.Actor WHERE DelPri = @DelPri AND CustPrg3 = @CustPrg3) 
                     INSERT INTO dbo.Actor(ActNo, CustNo, DelPri, CustPrg3, Nm, Gr6, CreDt, CreUsr) 
                     VALUES(@ActNo, @CustNo, @DelPri, @CustPrg3, @Nm, @Gr6, @CreDt, @CreUsr);";


            ActNoParam.Value = customers[i].ActNo; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 
            CustNoParam.Value = customers[i].CustNo; //   <<< THIS IS WH ERE YOUR NUMERIC VALUE GOES. 
            DelpriParam.Value = Int32.Parse(customers[i].DelPri); //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES.
            CustPrg3Param.Value = customers[i].CustPrg3; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES.
            NmParam.Value = customers[i].Nm; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 
            Gr6Param.Value = customers[i].Gr6; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 

            CreDtParam.Value = customers[i].CreDt; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 
            CreUsrParam.Value = customers[i].CreUsr; //   <<< THIS IS WHERE YOUR NUMERIC VALUE GOES. 

           cmd.ExecuteNonQuery();
    }

I know the first object is passed to the class, and i have tried printing out the values of the current object in the loop, so the data is there, yet skipped in the insert.

Also, I'm pretty sure it is not due to the if not exists query, since it is only 12 customers I'm checking with and I ran them over to spot any conflicts.

What the hell am I doing wrong ?

Appreciate any help! :)

Upvotes: 3

Views: 133

Answers (2)

Maddy
Maddy

Reputation: 123

Can you try/confirm these things:

  1. Does the table actor have records? Is it possible that the first record is already there in the table (i.e with Delpri and Custprg3 value)?

  2. As you are saying that it's not because of IF NOT EXIST, can you try running it by removing that?

  3. Your for-loop starts with i = 1. Are you sure you have got the sequence right?

Upvotes: 3

Steve
Steve

Reputation: 9583

You're starting your for loop at index 1, while c# is a 0-indexed language. This means you're actually starting at the second record.

Change your for loop to:

for (int i = 0; i < customers.Count; i++)

Upvotes: 7

Related Questions