Click here to Skip to main content
15,032,177 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 8-Sep-11 3:08am
v2
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..

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900