访问数据库 - 自动插入\更新 - Qt4
在使用 Qt4 制作的注册软件中，我打开 Access .mdb 数据库，使用用户提供的字段更新它。
它当前有一个包含六个字段的表 clients：
CustomerNumber, FullName, CNICNumber, ResidentialAddress, ResidentialPhoneNumber, MobileNumber
其中 CustomerNumber 是主键和一个数字，而所有其他字段都是文本。有一些记录存在。但是，当尝试插入具有相同 CustomerNumber 的另一条记录时，会出现错误：
QODBCResult::exec：无法执行 声明：“[Microsoft][ODBC Microsoft 访问驱动程序]改变你 要求表没有 成功是因为他们会创造 索引中的重复值，主索引 关键或关系。更改数据 在包含的一个或多个字段中 重复数据，删除索引，或者 重新定义索引以允许重复 条目并重试。”
“[Microsoft][ODBC Microsoft Access Driver] 您要求的更改 该表没有成功，因为 他们会在中创建重复的值 索引、主键或 关系。更改中的数据 包含重复项的一个或多个字段 数据，删除索引，或重新定义 允许重复条目的索引 并重试。” “QODBC3：无法 执行语句”
然后我找到了 UPDATE 查询，但以下代码给出了另一个错误：
query.exec("UPDATE clients"
"SET FullName='"+cname+"', CNICNumber='"+cnic+"', ResidentialAddress='"+caddress+"', ResidentialPhoneNumber='"+cphone+"', MobileNumber='"+cmobile+"'"
"WHERE CustomerNumber="+cnumber+";");
变量 cname、cninc、caddresss、cphone、cmobile、cnumber 是带有值的字符串。但上面代码的错误是：
QODBCResult::exec：无法执行 声明：“[Microsoft][ODBC 驱动程序 经理]功能顺序错误” “[Microsoft][ODBC 驱动程序管理器] 函数顺序错误" "QODBC3: 无法执行语句”
解决方案是什么，即如何在主键不存在时插入新记录，但使用相同的主键更新现有记录？
In a registration software made with Qt4, I open an Access .mdb database, update it with the user-provided fields.
It has currently a table clients with six fields:
CustomerNumber, FullName, CNICNumber, ResidentialAddress, ResidentialPhoneNumber, MobileNumber
where CustomerNumber is primary key and a number, while all others are text. There are a few records present. But when another record with same CustomerNumberis tried to be inserted, there is an error:
QODBCResult::exec: Unable to execute
statement: "[Microsoft][ODBC Microsoft
Access Driver] The changes you
requested to the table were not
successful because they would create
duplicate values in the index, primary
key, or relationship. Change the data
in the field or fields that contain
duplicate data, remove the index, or
redefine the index to permit duplicate
entries and try again.""[Microsoft][ODBC Microsoft Access
Driver] The changes you requested to
the table were not successful because
they would create duplicate values in
the index, primary key, or
relationship. Change the data in the
field or fields that contain duplicate
data, remove the index, or redefine
the index to permit duplicate entries
and try again." "QODBC3: Unable to
execute statement"
I then found the UPDATE query, but the following code gives another error:
query.exec("UPDATE clients"
"SET FullName='"+cname+"', CNICNumber='"+cnic+"', ResidentialAddress='"+caddress+"', ResidentialPhoneNumber='"+cphone+"', MobileNumber='"+cmobile+"'"
"WHERE CustomerNumber="+cnumber+";");
The variables cname, cninc, caddresss, cphone, cmobile, cnumber are strings with values. But the error with the above code is:
QODBCResult::exec: Unable to execute
statement: "[Microsoft][ODBC Driver
Manager] Function sequence error"
"[Microsoft][ODBC Driver Manager]
Function sequence error" "QODBC3:
Unable to execute statement"
What is the solution to this, i.e. how to insert a new record when the primary key is not present but update existing record with same primary key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你缺少空格。您正在执行的实际命令相当于
不存在clientsSET这样的表。
这就是您想要的
如果这些是用户输入值，您很容易受到 SQL 注入的攻击。您需要小心地清除所有字符串值（例如，将所有 ' 替换为 ''）。
You are missing spaces. The actual command you are executing is equivalent to
there is no such table as clientsSET.
Here is what you intended
If these are user-input values you are vulnerable to SQL injection. You need to be careful to scrub all the string values (eg. replace all ' with '').
这被非正式地称为
UPSERT。请参阅与 Access 相关的此答案。This is known informally as an
UPSERT. See this answer that relates to Access.我的猜测是 CNICNumber 不应该有单引号。
但如果不知道你的表结构，就不可能确定。
My guess would be that CNICNumber shouldn't have single quotes around it.
But it's impossible to tell for sure without knowing your table structure.
你可以尝试：
在第二排和第三排吗？
Can you try with:
in the second and third row?