CODEWITHSUNDEEP
c#sqlnpgsql
Roger_88
Roger_88

Reputation: 477

SELECT query returning exception in C# method

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?

Upvotes: 1

Views: 4156

Answers (2)

styx
styx

Reputation: 1917

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

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

Upvotes: 7

sujith karivelil
sujith karivelil

Reputation: 29006

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
}

Upvotes: 1

Related Questions