CODEWITHSUNDEEP
c#asp.netsql-server
Syed Haris Ali Ghaznavi
Syed Haris Ali Ghaznavi

Reputation: 787

SQL Server query not returning any result in asp.net

I am trying to check if a person has a past crime history or not by looking up his NIC number in criminal database. But the query is not fetching any data although there is no exception and relevant data is present in the database but still data reader is empty.

Please see the following code:

SqlCommand cmd5 = new SqlCommand("select * from criminal where NIC ="+nic, conn);
string nic = "null";

foreach (var person in allInvolved)
{
    conn.Open();
    nic = person.NIC;

    dr3 = cmd5.ExecuteReader();

    if (dr3.HasRows)
    { do something }
    else if (!dr3.HasRows)
    { do something else}
}

Variable NIC has the correct value in it, I checked it while debugging. I don't know where am I going wrong. If you need any other info regarding the code please ask.

Thanks in advance.

Upvotes: 1

Views: 1727

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062550

When you do:

 SqlCommand cmd5 = new SqlCommand("select * from criminal where NIC ="+nic, conn);

the current value of nic is used at that moment; it doesn't matter what you change nic to afterwards:

nic = person.NIC;
dr3 = cmd5.ExecuteReader();

since that value is not used. Ideally, you should parameterize:

SqlCommand cmd5 = new SqlCommand(
    "select * from criminal where NIC = @nic", conn);
var param = cmd5.Paramters.Add("nic", SqlDbType.SomethingRelevant);
// ^^^ note: I don't know what the data type is, you'll need to pick that

foreach(...) {
    param.Value = ((object)person.NIC) ?? DBNull.Value;
    using(var dr3 = cmd5.ExecuteReader()) {
      // ...
    }
}
...

Additionally, note that a lot of these objects are IDisposable and should be in using blocks; alternatively, look at tools like "dapper" so you can do things like:

var criminals = conn.Query<Criminal>(
    "select * from criminal where NIC = @nic",
    new { nic = person.NIC}).AsList();

Upvotes: 2

neer
neer

Reputation: 4082

Try this

SqlConnection con = new SqlConnection(connectionString);  
SqlCommand cmd = new SqlCommand();  
cmd.Connection = con;  
con.Open();  

foreach (var person in allInvolved)
{
nic = person.NIC;
cmd.CommandText = "select * from criminal where NIC ="+nic;  
dr3 = cmd.ExecuteReader();

while (dr3.Read())  
{ 
      if (dr3.HasRows)
      { do something}
      else if (!dr3.HasRows)
      { do something else}
}

} con.Close();

Upvotes: 1

Related Questions