If Primary Key auto Increment added data not inserting otherwise its inserting RRS feed

  • Question

  • cmd.CommandText = (@"insert into accounts values('" + comboBox2.Text + "','" + bunifuMaterialTextbox3.Text + "','" + bunifuMaterialTextbox4.Text + "','" + bunifuMaterialTextbox5.Text + "','" + textBox5.Text + "','" + comboBox1.Text + "','" + bunifuMaterialTextbox9.Text + "','" + label2.Text + "','" + bunifuMaterialTextbox6.Text + "','" + bunifuMaterialTextbox7.Text + "','" + bunifuMaterialTextbox8.Text + "')");                    

    Monday, December 2, 2019 1:42 PM

All replies

  • Hello,

    Your question doesn't make sense. If you want to insert a new record you should do this in a class method. In the example below which is short to assist in understanding a better way to do the insert with command parameters and a secondary query to get the new primary key.

    Rather than passing all values each as a parameter a class instance is used e.g.

    public class Audits 
        public string AccountNumber { get; set; }
        public string CreatedBy { get; set; }

    In a class a insert method.

    public int AddNewAudit(Audits audit)
        int newPrimaryKey = -1;
            using (var cn = new SqlConnection() { ConnectionString = "Your connection string" })
                using (var cmd = new SqlCommand() { Connection = cn })
                    cmd.CommandText = string.Format("INSERT INTO Audits (AccountNumber, CreatedBy) " + 
                                                    "VALUES (@AccountNumber, @CreatedBy)"
                                                  + "SELECT CAST(scope_identity() AS int)");
                    cmd.Parameters.AddWithValue("@AccountNumber", audit.AccountNumber);
                    cmd.Parameters.AddWithValue("@CreatedBy", audit.CreatedBy);
                    newPrimaryKey = (int)cmd.ExecuteScalar();
        catch (Exception dbex)
            // ignored - add what you want here
        return newPrimaryKey;

    Note the try-catch, you should either write the error to the IDE output windows e.g. Console.WriteLine(dbex.Message) or some other way but don't leave it as it is now. If there is an error report back here with it.

    If I have this all wrong then provide specific details.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Monday, December 2, 2019 2:27 PM
  • Hi syed shabeer,
    According to your description, I don't know what your requirement is. Could you please explain it in detail?
    Best Regards,
    Daniel Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Tuesday, December 3, 2019 8:32 AM
  • Karen's advice is the right advice here, but I think I can interpret your question.

    If you have an auto-increment primary key, then you can't use INSERT INTO xxx VALUE (... ); directly.  You must specify all of the column names and leave out the primary key column.  The database server will fill in that column, so you can't provide a value.

    So, taking Karen's example, if AccountNumber is an auto-increment field, then you'd want

    "INSERT INTO Audits (CreatedBy) VALUES (@CreatedBy)"

    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Wednesday, December 4, 2019 8:10 PM