Trying to compare a given ID with one stored on the DB

Question

can you help me with this? I really can't undertand how to do it.

What I'm trying to do, is that when I give it an id, compare it with another id stored in the database.

public Rubro GetRubroById(int GivenId)
{

    Rubro rubro = new Rubro();
    SqlCommand command = new SqlCommand

    ("SELECT * FROM Rubro WHERE GivenId = Id", con);
    try
    {
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            rubro.Id = reader.GetInt32(0);
            rubro.Name = reader.GetString(1);
        }
        con.Close();
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }

    return rubro;
}

you didn't add your `SqlParameter` – Jonesopolis May 08 '17 at 12:57 — Jonesopolis, May 08 '17 at 12:57

Tim Schmelter · Answer 1 · 2017-05-08T13:12:28.520

You have to pass the value to the sql query parameter:

using(var command = new SqlCommand("SELECT * FROM Rubro WHERE Id = @GivenId", con))
{
    try
    {
        command.Parameters.Add("@GivenId", SqlDbType.Int).Value = GivenId;
        con.Open();
        using(var reader = command.ExecuteReader())
        while (reader.Read())
        {
            rubro.Id = reader.GetInt32(0);
            rubro.Name = reader.GetString(1);
        }
        con.Close();
    }
    catch (SqlException ex)
    {
       MessageBox.Show(ex.Message);
    }
}

score 1 · Accepted Answer · answered May 08 '17 at 13:00

The problem is that GivenId variable in the SQL that you pass has no relationship to GivenId variable of GetRubroById method. Despite their common name, the two exist in different parts of the system.

You need to pass the value to the statement using parameters:

SqlCommand command = new SqlCommand("SELECT * FROM Rubro WHERE @GivenId = Id", con);
command.Parameters.Add("@GivenId", SqlDbType.Int);
command.Parameters["@GivenId"].Value = GivenId; // rename to givenId

You should rename GivenId to givenId in compliance with .NET naming conventions.

Trying to compare a given ID with one stored on the DB

2 Answers2