如何插入正确的ID字段与自动递增的数字？ [英] How do I insert the correct ID field vs an auto-incremented number?

问题描述

在我的数据集中，我有一个RecID字段，该字段是从查询结果中填充的。我使用数据适配器将记录插入数据库的本地副本。但是，RecID字段从1开始并自动递增，而不是实际的记录号。如何将正确的RecID添加到我的本地数据库表中？



这是我用来将数据集中的数据插入本地数据库表的代码。 br />

 private bool LoadRecs（DataTable tbl）
 {
 //此函数将记录插入本地db 
 $ b中的相应表中$ b 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博士; 
 
 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（）修剪（）; 
} 
 catch 
 {
 dr [k] = System.DBNull.Value; 
} 
} 
 dsCe.Tables [tbl.TableName] .Rows.Add（dr）; 
} 
 
 SqlCeCommandBuilder cb = new SqlCeCommandBuilder（daCe）; 
 //似乎需要强制分配insert命令。 
 daCe.InsertCommand = cb.GetInsertCommand（）; 
 try 
 {
 //调用update将所有数据从sampletable DataTable移动到数据库表
 daCe.Update（dsCe，tbl.TableName）; 
 daCe.Dispose（）; 
 blnUploaded = true; 
} 
 catch 
 {
 blnUploaded = false; 
 MessageBox.Show（错误）; 
} 
 return（blnUploaded）; 
} 
} 
 

解决方案

在插入ID值之前使用以下查询：

  SET   IDENTITY_INSERT    + tableName +  ON  

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);
    }
}

解决方案

Use following query before you insert ID values:

SET IDENTITY_INSERT " + tableName + " ON

这篇关于如何插入正确的ID字段与自动递增的数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！