更新访问数据库时如何防止主键冲突 [英] How do I prevent primary key violation when updating an access database
问题描述
我可以从一个paradox数据库成功导入两个表，并将数据添加到一个访问表中。从一个paradox表我使用ID字段，因为它是将时钟链接到日期的主键，我进入了访问权限主键等于Clockd.ID的ID字段。
如果从paradox数据库更新时添加新记录，如何附加访问表
我有点难过这个
这是我得到了多远。任何指导都将不胜感激
< pre lang =c＃> private void mtrBtnLoad_Click（ object sender，EventArgs e）
{
string connStringSource = Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c：\\Program Files \\Supertime\\db; Extended Properties = Paradox 5.x; ;
string connStringDest = Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\\Users \\Yellow \\Documents\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
OleDbConnection connSource = new OleDbConnection（connStringSource）;
OleDbConnection connDest = new OleDbConnection（connStringDest）;
OleDbCommand cmdSelect = new OleDbCommand（ @ 选择Clockd.ID，Empmas.EmpNo，Empmas.Name，Clockd.DateTime，Calc0R1，Calc1R1，Calc2R1，Calc3R1，Calc4R1，TotalHours，Target0R1 FROM Empmas INNER JOIN Clockd ON Empmas.EmpNo = Clockd.Empno，connSource）;
OleDbCommand cmdIns = new OleDbCommand（ @ INSERT INTO Clockd（[ID]，EmpNo，[Name]，[DateTime]，Calc0R1，Calc1R1，Calc2R1，Calc3R1，Calc4R1，TotalHours，Target0R1）VALUES（@ID，@ EmpoNo，@ Name，@ DateTime， @ Calc0R1，@ Calc1R1，@ Calc2R1，@ Calc3R1，@ Calc4R1，@ TotalHours，@ Target0R1），connDest）;
cmdIns.Parameters.Add（ new OleDbParameter（ @ ID，OleDbType.VarChar， 11 ， < span class =code-string> ID））;
cmdIns.Parameters.Add（ new OleDbParameter（ @EmpNo，OleDbType.VarChar， 11 ， EMPNO））;
cmdIns.Parameters.Add（ new OleDbParameter（ @Name，OleDbType.VarChar， 11 ， 名称））;
cmdIns.Parameters.Add（ new OleDbParameter（ @DateTime，OleDbType.Date， 4 ， 日期时间））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Calc0R1，OleDbType.Integer， 8 ， Calc0R1））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Calc1R1，OleDbType.Integer， 8 ， Calc1R1））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Calc2R1，OleDbType.Integer， 8 ， Calc2R1））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Calc3R1，OleDbType.Integer， 8 ， Calc3R1））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Calc4R1，OleDbType.Integer， 8 ， Calc4R1））;
cmdIns.Parameters.Add（ new OleDbParameter（ @TotalHours，OleDbType.Integer， 4 ， TotalHours））;
cmdIns.Parameters.Add（ new OleDbParameter（ @ Target0R1，OleDbType.Integer， 4 ， Target0R1））;
OleDbDataAdapter da = new OleDbDataAdapter（connStringDest，connStringDest）;
da.AcceptChangesDuringFill = false ;
da.SelectCommand = cmdSelect;
da.InsertCommand = cmdIns;
System.Data.DataSet ds = new System.Data.DataSet（）;
da.Fill（ds）;
da.Update（ds）;
DataTable dt = new DataTable（）;
BindingSource bs = new BindingSource（）;
bs.DataSource = dt;
dvg1.DataSource = bs;
} 在插入记录之前，您需要获取插入查询以检查ID是否已存在。
不幸的是，为了能够将一个WHERE子句放入Access中的INSERT查询，需要使用INSERT INTO SELECT查询。
这当然意味着你需要拥有一个可供选择的表，而你只想给它一些值来插入。
这使得查询更长一点，并不像SQL Server那样清晰，但是应该仍然可以。
试试这个插入查询：
OleDbCommand cmdIns = new OleDbCommand（ @ INSERT INTO Clockd（[ID]，EmpNo， [名称]，[日期时间]，Calc0R1，Calc1R1，Calc2R1，Calc3R1，Calc4R1，TotalHours，Target0R1）SELECT TOP 1 @ID，@ EmpoNo，@ Name，@ DateTime，@ Calc0R1，@ Calc1R1，@ Calc2R1， @ Calc3R1，@ Calc4R1，@ TotalHours，@ Target0R1 FROM Clockd WHERE NOT EXISTS（SELECT [ID] FROM Clockd WHERE [ID] = @ ID），connDest）;
它会执行一个选择（限制为1行），它将返回所有参数，但仅当ID没有时才会返回表格中已经存在。
I can successfully import two tables from a paradox database and and add the the data into one access table.From the one paradox table I use the ID field as it is the primary key to link clockings to dates and I made my access primary key equal to the ID field of the Clockd.ID.
How do I append the access table when updating from the paradox database adding just new records
I am a little bit stumped on this
This is how far I have gotten.Any guidance would be appreciated
private void mtrBtnLoad_Click(object sender, EventArgs e)
{
string connStringSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Program Files\\Supertime\\db;Extended Properties=Paradox 5.x;";
string connStringDest = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Yellow\\Documents\\paradoxExport.accdb; Persist Security Info=False;";
OleDbConnection connSource = new OleDbConnection(connStringSource);
OleDbConnection connDest = new OleDbConnection(connStringDest);
OleDbCommand cmdSelect = new OleDbCommand(@"Select Clockd.ID, Empmas.EmpNo, Empmas.Name, Clockd.DateTime, Calc0R1, Calc1R1, Calc2R1, Calc3R1, Calc4R1, TotalHours, Target0R1 FROM Empmas INNER JOIN Clockd ON Empmas.EmpNo=Clockd.Empno", connSource);
OleDbCommand cmdIns = new OleDbCommand(@"INSERT INTO Clockd ([ID], EmpNo, [Name], [DateTime], Calc0R1, Calc1R1, Calc2R1, Calc3R1, Calc4R1, TotalHours, Target0R1) VALUES (@ID, @EmpNo, @Name, @DateTime, @Calc0R1, @Calc1R1, @Calc2R1, @Calc3R1, @Calc4R1, @TotalHours, @Target0R1)", connDest);
cmdIns.Parameters.Add(new OleDbParameter("@ID", OleDbType.VarChar, 11,"ID"));
cmdIns.Parameters.Add(new OleDbParameter("@EmpNo", OleDbType.VarChar, 11, "EmpNo"));
cmdIns.Parameters.Add(new OleDbParameter("@Name", OleDbType.VarChar, 11, "Name"));
cmdIns.Parameters.Add(new OleDbParameter("@DateTime", OleDbType.Date, 4, "DateTime"));
cmdIns.Parameters.Add(new OleDbParameter("@Calc0R1", OleDbType.Integer, 8, "Calc0R1"));
cmdIns.Parameters.Add(new OleDbParameter("@Calc1R1", OleDbType.Integer,8, "Calc1R1"));
cmdIns.Parameters.Add(new OleDbParameter("@Calc2R1", OleDbType.Integer, 8, "Calc2R1"));
cmdIns.Parameters.Add(new OleDbParameter("@Calc3R1", OleDbType.Integer, 8, "Calc3R1"));
cmdIns.Parameters.Add(new OleDbParameter("@Calc4R1", OleDbType.Integer, 8, "Calc4R1"));
cmdIns.Parameters.Add(new OleDbParameter("@TotalHours", OleDbType.Integer, 4, "TotalHours"));
cmdIns.Parameters.Add(new OleDbParameter("@Target0R1", OleDbType.Integer, 4, "Target0R1"));
OleDbDataAdapter da = new OleDbDataAdapter(connStringDest,connStringDest);
da.AcceptChangesDuringFill = false;
da.SelectCommand = cmdSelect;
da.InsertCommand = cmdIns;
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds);
da.Update(ds);
DataTable dt = new DataTable();
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dvg1.DataSource = bs;
}You'll need to get your insert query to check if the ID exists already before inserting the record.
Unfortunately to be able to put a WHERE clause into an INSERT query in Access requires that you use an INSERT INTO SELECT query.
This of course means that you need to have a table to select from, whereas you want to just give it some values to insert.
This makes the query a little bit longer and not quite as clear as it would be with something like SQL Server, but should still be possible.
Give this insert query a try:
OleDbCommand cmdIns = new OleDbCommand(@"INSERT INTO Clockd ([ID], EmpNo, [Name], [DateTime], Calc0R1, Calc1R1, Calc2R1, Calc3R1, Calc4R1, TotalHours, Target0R1) SELECT TOP 1 @ID, @EmpNo, @Name, @DateTime, @Calc0R1, @Calc1R1, @Calc2R1, @Calc3R1, @Calc4R1, @TotalHours, @Target0R1 FROM Clockd WHERE NOT EXISTS (SELECT [ID] FROM Clockd WHERE [ID]=@ID)", connDest);
It does a select (limited to 1 row) that will return all the parameters, but only if the ID doesn't already exist in the table.
这篇关于更新访问数据库时如何防止主键冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
