How to use COUNT funcation in asp .net with sql

Question

1.00/5 (1 vote)

See more:

string select = "select COUNT(Bookid) from book1 where Regid='" + TextBox8 + "'";

Posted 19-May-15 6:46am

PreetamYadav

Add a Solution

Comments

Mathi Mani 19-May-15 13:00pm

Are you trying to get the count of items with Regid entered by the user in TextBox8? Use TextBox8.Text to get the text instead of passing the TextBox control as input.

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)

Richard Deeming · Answer 1 · 2015-05-19T07:10:00

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Once you've fixed that vulnerability, you'll see that you're trying to pass the TextBox control to the query, rather than its .Text property.

C#

using (var connection = new SqlConnection("YOUR CONNECTION STRING"))
using (var command = new SqlCommand("SELECT Count(BookId) FROM book1 WHERE RegId = @RegId"))
{
    command.Parameters.AddWithValue("@RegId", TextBox8.Text);
    
    connection.Open();
    int count = Convert.ToInt32(cmd.ExecuteScalar());
    lblCount.Text = count.ToString();
}

ZurdoDev · Answer 2 · 2015-05-19T07:00:00

Use a SqlCommand object. And since there is only 1 value being returned you can use ExecuteScalar().

C#

...
cmd.CommandText = select;
Int32 count = Int32.Parse(cmd.ExecuteScalar().ToString());
lblCount.Text = count.ToString();
...

Also, as Richard mentions in Solution 2, change your SQL so that you do not have Sql injection issues.

Sergey Alexandrovich Kryukov · Answer 3 · 2015-05-19T07:56:00

Your approach is wrong from the very beginning. Your query is composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

The user can add anything in UI elements, including… the fragment of SQL code. Are you getting the idea?

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA

How to use COUNT funcation in asp .net with sql

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0