此子查询最多可以返回一条记录 - 请帮忙！ [英] At most one record can be returned by this subquery-please help!

问题描述

我有这个查询，它显示了这条消息 -

这个子查询最多可以返回一条记录

i with c＃和access，有人可以请帮助我？



我尝试了什么：



command.CommandText =选择id_women为תעודת_זהות_אישה，fname为שם_פרטי，lname为שם_משפחה，图片为תמונה，从women2会面为במפגש（age =（从men_expectations2选择age_min）和height =（从men_expectations2中选择height_min）和body_structure =（从中选择body_structure men_expectations2）和health =（从men_expectations2中选择健康状况）和status =（从men_expectations2中选择状态））和（origin =（从men_expectations2选择origin）或hashkafa =（从men_expectations2中选择hashkafa）或living_area =（从men_expectations2中选择living_area）或吸烟=（从men_expectations2中选择吸烟）或tfila =（从men_expectations2中选择tfila））和Change_residence =（选择Change_来自men_expectations2）和headdress =（从men_expectations2选择头饰WHERE id_men ='+ textBox1.Text +'）;;

I have this query and it's shows this message-
"at most one record can be returned by this subquery"
i work with c# and access, can someone please help me?

What I have tried:

command.CommandText = "select id_women as תעודת_זהות_אישה,fname as שם_פרטי,lname as שם_משפחה,picture as תמונה,meeting as במפגש from women2 where (age=(select age_min from men_expectations2) and height=(select height_min from men_expectations2) and body_structure=(select body_structure from men_expectations2) and health=(select health from men_expectations2) and status=(select status from men_expectations2)) and (origin=(select origin from men_expectations2) or hashkafa=(select hashkafa from men_expectations2) or living_area=(select living_area from men_expectations2) or smoking=(select smoking from men_expectations2) or tfila=(select tfila from men_expectations2)) and Change_residence=(select Change_residence from men_expectations2) and headdress=(select headdress from men_expectations2 WHERE id_men = '" + textBox1.Text + "');";

推荐答案

当您使用SELECT作为条件的一部分，子条款只能返回一个项目，因为这就是比较运算符如=的工作方式：如果将一个项目与另外两个项目进行比较，结果就不会被定义，就像它不在现实世界中一样。这个橙子是否等于此（一个苹果，两个香蕉，一个橙子）或不？你不知道，我不知道 - 只是因为没有真正的答案。



可能你需要做一个JOIN，但是因为我们不知道你的表的结构和它们之间的关系，我们不能肯定地说，或推荐你可以尝试的任何代码。因此，在开始尝试编写复杂查询代码之前，请考虑表中的数据，以及您尝试检索的内容！或者给我们提供样本输入和输出并解释为什么会这样...



但是你有一个更严重的问题！

从不连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

When you use SELECT as part of a condition, the subclause can only return a single item because that is how comparison operators like "=" work: if you compare one item with two others, the result is not defined, just as it isn't in the real world. Is this orange equal to this (one apple, two bananas, one orange) or not? You don' know, I don't know - simply because there is no "real" answer.

Probably, you need to do a JOIN instead, but since we have no idea of the structure of your tables and the relationships between them, we can't say for sure, or recommend any code you could try. So think about the data in your tables, and exactly what you are trying to retrieve before you start trying to code a complex query! Or give us sample input and output and explain why it's like that ...

But you have a much more serious problem!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。您是否定期进行备份，不是吗？

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

在此上下文中，您的子查询应该只返回一条记录。你需要某种where语句加入一条记录。



这是一个非常长的查询。一旦它工作，这许多子查询将表现不佳。我会考虑使用CTE和连接

Your subqueries should only return one record, in this context. You need some sort of where statement that joins to one record.

This is a terribly long query. This many subqueries will perform badly, once it's working. I'd look at using CTEs and joins instead

这篇关于此子查询最多可以返回一条记录 - 请帮忙！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！