sqlite insert or replace datagridview C#

Question

Regarding the Sqlite insert or replace command.

I am doing the insert if does not exist. The inserted/updated values come from the datagridview bound to a datatable. The datatable has a TID column which is the primary key. It also has other columns like Tname,TUtility....

For update statement TID has a value and works well. But for an insert statement I want the serial number(which is the TID column) to be automatically populated.

         private void SaveData(DataGridView dgv)
            {
                using (SQLiteConnection conn = new SQLiteConnection(connString))
                {

                    conn.Open();
                    dt = dt.GetChanges();
                    if (dt.Rows.Count > 0)
                    {
                        int dtlength = dt.Rows.Count;
                        DataRow[] arr = new DataRow[dtlength];

                        dt.Rows.CopyTo(arr, 0);
                        SQLiteCommand upCmd = new SQLiteCommand(
      @"INSERT OR REPLACE
    INTO
      [Table1](
        [TID],
        [TName],
        [TUtility],
        [TType])values(@TID,@Tname,@TUtility,@TType),conn);
       for (int i = 0; i < arr.Length; i++)
                        {
                            upCmd.Parameters.AddWithValue("@TID", Convert.ToInt32(arr[i]["SNo"]));
                            upCmd.Parameters.AddWithValue("@TName", arr[i]["Utility"].ToString());
                            upCmd.Parameters.AddWithValue("@TUtility", arr[i]["Plantname"].ToString());
                            upCmd.Parameters.AddWithValue("@TType", arr[i]["PlantType"].ToString())
                            da.UpdateCommand = upCmd;

                            this.da.UpdateCommand.ExecuteNonQuery();
                        }

How should I add this TID value in such a way that when the users inserts a new row, it should automatically put the serial number. I mean: Suppose there are 4rows currently in the DGV. The DGV is in edit mode(*).I am adding a new row. Once the user tries to enter some data in the new row, there should be a serial number already populated.

Thank you Sun

user850010 · Accepted Answer · 2012-05-10 06:18:34Z

1

I am not sure if I understand your problem but why not just put NULL for TID when you do INSERT. Since TID is primary key, the sqlite will insert value automatically.

According to Sqlite FAQ it says this for primary key

whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty.

answered May 10, 2012 at 6:18

user850010

6,37912 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlite insert or replace datagridview C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related