CODEWITHSUNDEEP
c#.netsqlsql-server
Ameer Adel
Ameer Adel

Reputation: 152

SqlDataReader Execution error

i m trying to retrieve the Specialization ID from a table called Specializationtbl, using C# MSVS 2008 and the table includes SpecializationName and SpecializationID beside some other rows and my question is related to some error " No Data to present ", the command goes as bellow: 

SqlCommand READSpecID = new SqlCommand("SELECT * FROM Specializationtbl WHERE SpecializationName='" + comboBox1.Text + "'" , DBcnction);
DBcnction.Open();

SqlDataReader ReadSpecID_ = READSpecID.ExecuteReader();
ReadSpecID_.Read();
int SpecID_ = Convert.ToInt16(ReadSpecID_["SpecID"].ToString());

DBcnction.Close();

i also tried to Select the "SpecID" instead of all the rows, but cant seem to seal the query correctly and keep receiving "No data present " error, any idea where am i making the mistake?

Upvotes: 3

Views: 140

Answers (3)

paparazzo
paparazzo

Reputation: 45106

Need to first test if there are any rows. I suspect the query is returning zero rows.

if (ReadSpecID_.HasRows) { ReadSpecID_.Read(); }

Upvotes: 1

p.campbell
p.campbell

Reputation: 100637

Refactor to solve your TWO problems:

  1. Your SQL injection problem when building your SQL statement.
  2. Use ExecuteScalar if you only need one value.
  3. Implement using blocks.
 string retVal;        
 using (var conn = new SqlConnection(SomeConnectionString))
 using (var cmd = conn.CreateCommand())
 {
   cmd.CommandText = "SELECT SpecID FROM Specializationtbl WHERE SpecializationName= @Name";
   cmd.Parameters.AddWithValue("@Name", comboBox1.Text);
   conn.Open();
   retVal = cmd.ExecuteScalar().ToString();
}
int specID = int.Parse(retVal);

If you really needed more than one value from your statement:

 using (var conn = new SqlConnection(SomeConnectionString))
 using (var cmd = conn.CreateCommand())
 {
   cmd.CommandText = "SELECT SpecID, Value2 FROM Specializationtbl WHERE SpecializationName= @Name";
   cmd.Parameters.AddWithValue("@Name", comboBox1.Text);
   conn.Open();
   var dr = cmd.ExecuteReader();
   while (dr.Read())
   {
      Customer c = new Customer { 
             ID = dr["SpecID"].ToString(),
             Value = dr["Value2"].ToString(),
       };
   }
}

Upvotes: 1

Darren
Darren

Reputation: 70786

1) Try opening DBcnction before assigning the value to READSPecID

DBcnction.Open();
SqlCommand READSpecID = new SqlCommand("SELECT * FROM Specializationtbl WHERE     SpecializationName='" + comboBox1.Text + "'" , DBcnction);

2) Run the command in SSMS:

 SELECT * FROM Specializationtbl WHERE SpecializationName ='yourvalue'

and see if any results are returned

3) Check comboBox1.Text has a value in it

4) Validate the contents of comboBox1.Text (Or use paremetrised queries or a stored procedure) to ensure you do not become a victim of SQL Injection: http://en.wikipedia.org/wiki/SQL_injection

Upvotes: 1

Related Questions