执行ms-access查询时出错
本文关键字:出错 查询 ms-access 执行 | 更新日期: 2023-09-27 18:17:04
我创建了一个查询,在c#中一次插入两个ms访问表。我得到了异常
{System.Data.OleDb。oledbeexception:在SQL结束后发现字符声明。在System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling (OleDbHResult人力资源)System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult (tagDBPARAMSdbParams Object&executeResult)System.Data.OleDb.OleDbCommand.ExecuteCommandText (Object&executeResult)System.Data.OleDb.OleDbCommand.ExecuteCommand (CommandBehavior行为,Object&executeResult)System.Data.OleDb.OleDbCommand.ExecuteReaderInternal (CommandBehavior行为,字符串方法)System.Data.OleDb.OleDbCommand.ExecuteNonQuery ()CompanyDetails.Model.CompanyDetailsModel.setCompanyDetailsToDB (CompanyDetailsDataList_cmpDetailsList) in E:'Project'PBAttendence' modifyprivilege 'CompanyDetails'Model'CompanyDetailsModel.cs:line62}
我的示例代码如下,请解决我的问题。对不起,我的英语不好。
int companyID = _cmpDetailsList[0].CompanyID;
string companyName = _cmpDetailsList[0].CompanyName;
string contactID = _cmpDetailsList[0].ContactID;
string companyAddress = _cmpDetailsList[0].CompanyAddress;
if (companyID == -1)
{
OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity;" + "); ", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
else
{
OleDbCommand upcmd = new OleDbCommand("update CompanyDetails set [CompanyName] = '" + companyName + "',[CompanyAddress] = '" + companyAddress + "',[ContactID] = '" + contactID + "' where [CompanyID] = @cmpID;", conn);
conn.Open();
upcmd.Parameters.AddWithValue("@cmpID", companyID);
upcmd.ExecuteNonQuery();
conn.Close();
}
现在我分裂成两个插入命令,但我得到了错误{System.Data.OleDb。oledbeexception:语法错误。Select [UserID] from UserDetails;
OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
OleDbCommand cmd1 = new OleDbCommand("Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity" + ");", conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();
问题是这行代码:
OleDbCommand cmd = new OleDbCommand("Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "');Insert into UserCompanyDetails([UserID],[CompanyID]) values (" + "Select [UserID] from UserDetails;" + "," + "Select @@identity;" + "); ", conn);
在同一个OleDbCommand
中有两个insert
语句。试着将它移动到两个不同的步骤中:
- 插入CompanyDetails表
- 插入UserCompanyDetails表
希望这对你有帮助
首先,使用原始sql命令比使用代码生成sql更容易。
您可能会考虑创建一个存储过程,因为您的命令变得有点复杂
如果我是正确的,你目前要做的是:
Insert into table1(x,y,z) values a,b,c;
Insert into table2(x,y) values select * from table3; , @@identity
第二个sql命令在语法和逻辑上都是无效的,您的@@身份将不是静态的,因为您在命令期间插入新记录。
我的建议是这样做:
Insert into table1(x,y,z) values a,b,c;
declare @table1Id int = @@identity
Insert into table2(x,y) select colA, @table1Id from table3;
你不能有;Access中的查询。请参阅http://office.microsoft.com/en-us/access-help/HV080760224.aspx您必须按照@juanreyesv
的建议分别执行两次插入。你将不得不做3个查询,
- 使用sql:
- 获取@@身份使用
Select @@identity
并将其存储在变量idnt
中 - 使用2中获取的identity值。执行第三次插入:
"Insert into UserCompanyDetails([UserID],[CompanyID]) Select UserID, " + idnt.ToString() + " from UserDetails"
"Insert into CompanyDetails([CompanyName],[CompanyAddress],[ContactID]) values ('" + companyName + "','" + companyAddress + "','" + contactID + "')
执行插入参考http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx