count sql query is alway excuting that the record exits but record is not in database

Question

0.00/5 (No votes)

See more:

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..

C#

<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();

Posted 8-Sep-11 2:52am

codegeekalpha

Updated 8-Sep-11 3:08am

MaulikDusara

v2

Add a Solution

Comments

nagendrathecoder 8-Sep-11 8:59am

Why are you appending second query with Count(*) query? Any specific need?

codegeekalpha 8-Sep-11 15:45pm

to see if the record exits in db then don't insert.. otherwise insert record in db..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ChandraRam · Accepted Answer · 2011-09-08T03:15:00

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 + "'";

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??

MaulikDusara · Accepted Answer · 2011-09-08T03:13:00

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

SQL

SELECT Count(*) FROM roles

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[^]

Wendelius · Accepted Answer · 2011-09-08T07:31:00

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 query is alway excuting that the record exits but record is not in database

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0