How to count number of rows after query?

Question

0.00/5 (No votes)

See more:

I have a new problem.
My proc:

SQL

create proc sp_Login
	@UserName char(10),
	@passW char(10)
as
	select * from User where UserName=@UserName and passw=@passw
go

C#

public string Login(string user, string passw)
    {
        DataContextDataContext dcon=new DataContextDataContext();
        string msg = "";
        var q = dcon.sp_Login("John","123");
        if (q.Count() > 0)
        {
            msg="hi"+"john";
        return kq;
    }

It doesn't work.How to count number of rows and get the login name?

Posted 16-May-12 1:28am

giatuan2011

Add a Solution

Comments

bhagirathimfs 16-May-12 7:35am

You want to count the number of user present in the database????

Sandeep Mewara 16-May-12 8:17am

What is this DataContextdataContext class?

4 solutions

Solution 6

Hi ,
You can use @@ROWCOUNT
http://msdn.microsoft.com/en-us/library/ms188774.aspx[^]
http://technet.microsoft.com/en-us/library/ms187316.aspx[^]
Best Regards
M.Mitwalli

Posted 16-May-12 3:15am

Mohamed Mitwalli

Solution 1

SQL

create proc sp_Login
	@UserName char(10),
	@passW char(10)
as
	select count(*) from User where UserName=@UserName and passw=@passw
go

C#

Collapse | Copy Code

public string Login(string user, string passw)
    {
        DataContextDataContext dcon=new DataContextDataContext();
        string msg = "";
        var q = dcon.sp_Login("John","123");
        if (q.Count() > 0)
        {
            msg="hi"+ user;
        return kq;
    }

Posted 16-May-12 2:02am

Vasim889

Updated 16-May-12 2:03am

v2

Comments

giatuan2011 16-May-12 8:12am

It is successful when passw="1234".I do not think Count (*) returns the number of row.Proc does not return

Vasim889 16-May-12 8:50am

ok.I added another solution below.you try this one.

Solution 5

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create FUNCTION sp_Login
(	
@UserName varchar(100),
@Password int
	
)
RETURNS TABLE 
AS
RETURN 
(
 select count(*) as Counts from User  where ID=@Password and [Name]=@UserName
	
)

GO

you will try this function   select * from sp_Login ("John",123)

Posted 16-May-12 2:46am

Vasim889

Updated 16-May-12 2:48am

v2

Comments

giatuan2011 16-May-12 9:02am

I found:
var c = (from user in context.tblUser where user.username = txtusername.Text and user.password = txtpassword.Text).Count();
if c==1->success.
How to I change it?

giatuan2011 16-May-12 9:18am

Sorry,Vasim889.I get good result from your code.Thank you so much
var q = dcon.sp_Login("John","123");
if (q.Count() ==1)
I have not changed, "John" and "123" to txtuser, txtpass.

Vasim889 16-May-12 9:24am

ok .thank you

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)

Sandeep Mewara · Accepted Answer · 2012-05-16T02:19:00

Solution 2

Just change your SP to:

SQL

select COUNT(*) from User where UserName=@UserName and passw=@passw

You need just the count and nothing else. Further, you should not retrieve the password without any reason.

Handle the change in your code according to what is now retrieved back.

Posted 16-May-12 2:19am

Sandeep Mewara

v2

Comments

giatuan2011 16-May-12 8:32am

select Count(*) is successful in sql server.
DataContextDataContext dcon=new DataContextDataContext();
string msg = "";
var q = dcon.sp_Login("John","1234");
if (q.Count()>0)
{
msg="success";

}
return msg;
not chage.Results as before

Sandeep Mewara 16-May-12 12:31pm

Good to know.:)