Click here to Skip to main content
15,946,672 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
In my dataset, I have a RecID field which is populated from query results. I am inserting the records to a local copy of the database using a data adapter. however, instead of the actual record number, the RecID field is starting at 1 and auto-incrementing. How do I get the correct RecID into my local database table?

This is the code I am using to insert the data from the dataset to the local database tables.
   private bool LoadRecs(DataTable tbl)
    {
        // this function inserts the records into the respective tables in the local db

        bool blnUploaded = false;

         if (tbl.Rows.Count == 0)
              return (blnUploaded);

        SqlCeDataAdapter daCe = new SqlCeDataAdapter("select * from " + tbl + " ", strCEConnection);
        DataSet dsCe = new DataSet();
        daCe.FillSchema(dsCe, SchemaType.Mapped, tbl.TableName);
        DataRow dr;

        for (int i = 0; i < tbl.Rows.Count; i++)
        {
            dr = dsCe.Tables[tbl.TableName].NewRow();
            for (int k = 0; k < tbl.Columns.Count; k++)
            {
                try
                {
                    dr[k] = tbl.Rows[i].ItemArray.GetValue(k).ToString().Trim();
                }
                catch
                {
                    dr[k] = System.DBNull.Value;
                }
            }
            dsCe.Tables[tbl.TableName].Rows.Add(dr);
        }

        SqlCeCommandBuilder cb = new SqlCeCommandBuilder(daCe);
        //seems this is needed to force the insert command to be assigned.
        daCe.InsertCommand = cb.GetInsertCommand();
        try
        {
            //call update to move all the data from sampletable DataTable into the database table
            daCe.Update(dsCe, tbl.TableName);
            daCe.Dispose();
            blnUploaded = true;
        }
        catch
        {
            blnUploaded = false;
            MessageBox.Show("Error");
        }
        return (blnUploaded);
    }
}
Posted
Updated 4-Apr-13 11:14am
v2
Comments
Richard C Bishop 4-Apr-13 17:16pm    
You will need to change the (Is Identity) property of the table to No. Do that by right clicking the table and click design. Scroll down in the "Column Properties" and change the property mentioned above.

1 solution

Use following query before you insert ID values:
SQL
SET IDENTITY_INSERT " + tableName + " ON
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900