SQL Server error - Missing Data

Question

I have a problem with this function...

public Veiculo getVehiclebyMatricula(string MAT)
{
     Veiculo V = new Veiculo();
     SqlCommand command = new SqlCommand("select * from Veiculo where matricula =@mat;",CNManager.getConnection());
     command.Parameters.AddWithValue("@mat", MAT);
     SqlDataReader dr = command.ExecuteReader();

     if (dr.HasRows)
     {
         V._id = dr.GetInt32(0); //HERE
         V.marca = dr.GetString(1);
         V.modelo = dr.GetString(2);
         V.matricula = dr.GetString(3);
         V.estado = dr.GetString(4);
         V.preco = dr.GetInt32(5);
         V.cilindrada = dr.GetInt32(6);
         V.image = dr.GetString(7);
         V.id_tipo = dr.GetInt32(8);

         dr.Close();
         return V;
      }
      return V;
   }

the error that spawns says "Reading not valid when data is not present.."

You are closing two times, dr.Close()!, try to put in try catch finally{ dr.Close();} — Adel, May 20 '14 at 13:21

score 3 · Answer 1 · answered May 20 '14 at 13:19

3

You should really use parametrized queries and not just concatenate strings like this, because this opens doors for SQL injection. Try instead:

SqlCommand command = new SqlCommand("select * from Veiculo where matricula =@mat;",CNManager.getConnection());
command.Parameters.Add("@mat", MAT);
command.Execute...

Beside that you are closing your connection twice.

I would suggest to put your SQL connection object into a using block to make sure it's getting closed and drop your lines where you are closing your connection instead.

answered May 20 '14 at 13:19

user3596113

868
14
32

Didnt Work... but thnks for the query syntax :) – Maverick May 20 '14 at 13:27
@Maverick Can you tell me why it didnt work, maybe I can help you. – user3596113 May 20 '14 at 13:28

score 2 · Answer 2 · answered May 20 '14 at 13:23

2

You must call dr.Read() ( SqlDataReader.Read() ) and check if it returns true before accessing your dr properties.

answered May 20 '14 at 13:23

Perfect28

11,089
3
25
45

score 1 · Answer 3 · answered May 20 '14 at 13:23

1

You are closing two times, dr.Close()!, the message is saying that cannot read while data not present, since you closed it, try to put in this way:

try{
 ...
 } catch(){
 } finally
 { 
     dr.Close();
 }

answered May 20 '14 at 13:23

Adel

1,468
15
18

Olivier Jacot-Descombes · Accepted Answer · 2014-05-20T13:57:22.123

C# has a handy statement that closes and releases resources automatically: the using command. It does so, even if the using block is left prematurely because of an error or because of a return or break statement. You were closing the connection twice. In my example below, I use the using-statement in order to close the connection, the command and the reader.

You should not keep the connection open. This potentially consumes resources over a long time. Open it and close it (with using) each time you need a connection. The connection pool automatically keeps connections open for some time, so that the "physical" connection will not be constantly closed and reopened.

Before a record can be accessed you must move to the first (or next) record with dr.Read(). This method also returns a Boolean value telling you whether a record was there and whether the operation could be performed. Use dr.Read() instead of dr.HasRows.

I am also using a command parameter, which is both easier and safer as stitching the command together with string operations.

public Veiculo getVehiclebyMatricula(string MAT)
{
    Veiculo V = new Veiculo();
    query = "select * from Veiculo where matricula = @m";

    using (SqlConnection conn = CNManager.getConnection())
    using (var command = new SqlCommand(query, conn)) {
        command.Parameters.AddWithValue("@m", MAT);
        conn.Open();
        using (SqlDataReader dr = command.ExecuteReader()) {
            if (dr.Read()) {
                V._id = Convert.ToInt32(dr["_id"]);
                V.marca = dr["marca"].ToString();
                V.modelo = dr["modelo"].ToString();
                V.matricula = dr["matricula"].ToString();
                V.estado = Convert.ToChar(dr["estado"]);
                V.preco = Convert.ToInt32(dr["preco"]);
                V.cilindrada = Convert.ToInt32(dr["cilindrada"]);
                V.image = dr["_src"].ToString();
                V.id_tipo = Convert.ToInt32(dr["id_tipo"]);
            }
        }
    }
    return V;
}

score 0 · Answer 5 · edited May 23 '17 at 12:05

As well as replacing string concatenation with SQLParameters as other answers. The return data you need to call Read() on the DataReader.

Take a look at this question for an example of accessing the resultset: Read data from SqlDataReader

Quick snippet:

using(SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        var myString = rdr.GetString(0); //The 0 stands for "the 0'th column", so the first column of the result.
        // Do somthing with this rows string, for example to put them in to a list
        listDeclaredElsewhere.Add(myString);
    }
}

score 0 · Answer 6 · answered May 20 '14 at 13:39

I've had some problems handling SQL in C#/ASP.NET, so I always add use the Entity Framework. It is way easier to manipulate.

Here's how your problem would look with the Entity Framework...

public Veiculo getVehiclebyMatricula(string MAT)
{
    Veiculo V = new Veiculo();
    var context = new Entities(); //The name 'Entities' is up to you
    var dr = context.Veiculo.Where(m => m.matricula = MAT);
    foreach (var r in dr) {
        V._id = r._id;
        V.marca = r.marca;
        V.modelo = r.modelo;
        V.matricula = r.matricula
        V.estado = r.estado
        V.preco = r.preco;
        V.cilindrada = r.cilindrada;
        V.image = r._src
        V.id_tipo = r.id_tipo
    }
    context.Close();
}

Lemme know if this works out for you.

SQL Server error - Missing Data

6 Answers6