SqlDataReader Execution error

Question

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?

Darren · Accepted Answer · 2012-04-28 13:29:22Z

1

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

edited Apr 28, 2012 at 13:29

answered Apr 28, 2012 at 13:20

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

p.campbell · Accepted Answer · 2012-04-28 13:43:07Z

Refactor to solve your TWO problems:

Your SQL injection problem when building your SQL statement.
Use ExecuteScalar if you only need one value.
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(),
       };
   }
}

paparazzo · Accepted Answer · 2012-04-29 02:37:57Z

1

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

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

edited Apr 29, 2012 at 2:37

answered Apr 28, 2012 at 23:20

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

SqlDataReader Execution error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related