count sql查询总是会导致记录退出，但记录不在数据库中 [英] count sql query is alway excuting that the record exits but record is not in database

问题描述

我不知道wot是计数查询的问题.它总是说reocord存在于数据库中...

但记录并不严谨.

什么是计数SQL查询中的问题..

i don''t know wot is the problem with the count query. its always saying that reocord exists in the database...

but record is not dere.

what is the problem in count sql query..

<pre>MAcmd.CommandText = "SELECT Count(*) FROM roles Select userid,groupid from users,groups WHERE username = '" + TextBox1.Text + "' AND name = '" + TextBox2.Text + "'";
            int count =(int)MAcmd.ExecuteScalar();
            if(count>0)
            {
                Label1.Text="record exits";
            }
            else
            {
            MAcmd.CommandText = "INSERT INTO ROLES (UserId,GroupId) SELECT UserId,GroupId FROM Users, Groups WHERE username= '" + TextBox1.Text + "' AND name='" + TextBox2.Text + "'";

            MAcmd.ExecuteNonQuery();
            //MessageBox.Show("Inserted Sucessfully");
            Label1.Text = "inserted";
            }
            MAconn.Close();

推荐答案

您的查询错误.试试这个:

Your query is wrong. Try this:

MAcmd.CommandText = "SELECT Count(*) FROM roles,users,groups WHERE roles.UserId=users.UserID and roles.GroupID=groups.GroupID and username = ''" + TextBox1.Text + "'' AND name = ''" + TextBox2.Text + "''";

就是说，在不清理输入(TextBox1和TextBox2值)的情况下，将值连接到CommandText可能不是一个好主意.查找SQL注入以了解为什么这是不理想的.

为什么会有名为TextBox1和TextBox2的控件?

That said, concatenating values into the CommandText is probably not a good idea without sanitizing inputs (TextBox1 and TextBox2 values). Look up SQL Injection on why this is not desirable.

Why do you have controls named TextBox1 and TextBox2??

您的第一个选择语句count(*)完全错误，因为有两个sql查询，而您正在根据用户名和名称来过滤第二个sql查询的数据，因此您的第一个查询

Hi,
your first select statement count(*) is totally wrong because there is two sql quires and your are filtering the data based on username and name for 2nd sql query your first query

SELECT Count(*) FROM roles

始终返回结果.请在您的第一个sql查询上应用过滤器，如果您需要其他表中的数据，则无需添加第二个sql查询，然后使用

always return result. please apply filter on your 1st sql query and there is no need to add 2nd sql query if you need data from another table then use joins[^]

如前所述，您在同一查询中有两个不同的查询CommandText.执行此操作时，实际上两个查询都将执行，并返回2个结果集.但是，由于使用ExecuteScalar，因此只能从第一个查询中获取结果.

根据ChandraRam的建议，从头开始删除select count查询.

然后是另一件重要的事情.不要将文字值直接从UI对象连接到SQL语句.而是始终使用 SqlParameter [

As pointed out you have two different queries in the same CommandText. When this is executed, actually both queries are executed and the 2 result sets are returned. However since you use ExecuteScalar, you only get the result from the first query.

As suggested by ChandraRam, remove the select count query from the beginning.

Then another important thing. Don''t concatenate literal values from UI objects directly to a SQL statment. Instead always use SqlParameter[^]. This applies to the query but also to the insert statement.

By using parameters you have at least two benefits:
- you''re more safe from SQL injections
- you don''t have troubles with data type conversions (at least not so much)

这篇关于count sql查询总是会导致记录退出，但记录不在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！