SELECT query returning exception in C# method

Question

I'm newbie in C# and Npgsql and I'm trying to make a search in a certain table but I'm getting an exception.

public User Login(Account c)
{
    User usr = new User();
    using (NpgsqlConnection con = new NpgsqlConnection(strConnection))
    {
        try
        {
            con.Open();
            NpgsqlCommand command = new NpgsqlCommand();
            command.Connection = con;
            command.CommandText = "SELECT name FROM public.user WHERE c_id IN (SELECT id FROM public.account WHERE email=@Email AND password=@Password)";
            command.Parameters.AddWithValue("Email", c.Email);
            command.Parameters.AddWithValue("Password", c.Password);
            NpgsqlDataReader dr = command.ExecuteReader();

            if (dr.HasRows)
            {
                usr.Name = dr["name"].ToString();
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }    
    return usr;
}

Bellow the exception:

System.InvalidOperationException: 'No row is available'

I did the same thing but using PgAdmin4, and i've got one line. Exactly what i want to get.

SELECT name FROM public.user WHERE c_id IN (SELECT id FROM public.account WHERE email='[email protected]' AND password='user05');

How do i fix this?

Use a profiler to examine the exact query that is being executed from your C# project. — EvZ
– EvZ, Commented Dec 13, 2017 at 11:51
In your bottom query you have select nome instead of name, is that a typo? Also password vs senha. — Dan
– Dan, Commented Dec 13, 2017 at 11:54
@EvZ That aint a database related exception but a .net one. Probably due to not calling .Read() in the datareader before accesing its fields. Check styxs answer. — Cleptus
– Cleptus, Commented Dec 13, 2017 at 11:56
Did you expect to get more than one row by that query? You could simply use a JOIN instead of a IN with a subquery and use ExecuteScalar. Also don't catch exceptions just for the fun to rethrow them. — Steve
– Steve, Commented Dec 13, 2017 at 11:59

Equalsk · Accepted Answer · 2017-12-13 11:58:15Z

7

You have to call while dr.Read(); like this:

if (dr.HasRows)
{
    dr.Read();
    usr.Name = dr["name"].ToString();
}

edited Dec 13, 2017 at 11:58

Equalsk

8,2643 gold badges47 silver badges71 bronze badges

answered Dec 13, 2017 at 11:53

styx

1,9751 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cleptus Over a year ago

Havent worked with npgsql, but in other databases after working with IDataReaders .Close() should be called. Looks like another problem.

styx Over a year ago

@bradbury9 no need to close NpgsqlConnection because he used using(NpgsqlConnection ... )

Shay Rojansky Over a year ago

dr.HasRows is quite useless here, as dr.Read() already returns whether a row is available or now...

Cleptus Over a year ago

I was refering to close the Datareader, the using(NpgsqlConnection ... ) would only close the connection is possible. But as long as the datareader is nor closed explicitly nor inside a using (IDataReader dr = ...) the connection would remain opened besides being inside the using.

sujith karivelil · Accepted Answer · 2017-12-13 11:56:05Z

1

I suggest you to use while instead for checking HasRows followed by .Read() so the code would be like this:

while(dr.Read())
{
    usr.Name = dr["name"].ToString();
    // Assign rest of values if there any
}

answered Dec 13, 2017 at 11:56

sujith karivelil

29.1k7 gold badges60 silver badges90 bronze badges

4 Comments

Cleptus Over a year ago

I guess only one record is spected.

Crowcoder Over a year ago

I wouldn't suggest that when it is obvious there should only be one row.

sujith karivelil Over a year ago

@Crowcoder: If you are expecting only one row, then why don't you use TOP 1, I think there is no need to fetch N rows if you are expecting only one row

Crowcoder Over a year ago

If you are setting the same variable over and over in a loop then that is a red flag, it would work here but next time OP writes a query it may bite him/her.

Collectives™ on Stack Overflow

SELECT query returning exception in C# method

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related