CODEWITHSUNDEEP
c#stringstring-formattingsqlcommand
Tharinda Wicky
Tharinda Wicky

Reputation: 388

Input string was not in a correct format in SqlCommand

Here is the code that i used and It will popup an Exception at the 3rd command.CommandText assignment but it is the same way that i used in 2nd command.CommandText assignment,

  SqlCommand command = conn.CreateCommand();
  conn.Open();
  //1st
  command.CommandText = query;
  SqlDataReader reader = command.ExecuteReader();
  ArrayList alMainGrid = new ArrayList();

  while (reader.Read())
   {

     SupportTable table = new SupportTable();
     table.LaySheetNo = reader.GetValue(0).ToString();
     table.PlnLayStartTime = reader.GetDateTime(1).ToString();
     table.PlnLayEndTime = reader.GetValue(2).ToString();
     table.LayTableId = reader.GetValue(3).ToString();// reader.GetValue(3).ToString();
     table.LayTeamId = reader.GetValue(4).ToString();
     alMainGrid.Add(table);
   }
   reader.Close();

   foreach (SupportTable table in alMainGrid)
     {
       //2nd 
      command.CommandText = String.Format("SELECT CTDesc FROM CutTable WHERE CTId ={0}", int.Parse(table.LayTableId));
      string tableDesc = (string)command.ExecuteScalar();
      table.LayTeamId = tableDesc;

     //3rd-In this command.CommandText
      command.CommandText = String.Format("SELECT TeamDesc FROM Team WHERE TeamId ={0}", int.Parse(table.LayTeamId));
      string teamDesc = (string)command.ExecuteScalar();
      table.LayTeamId = teamDesc;
     }
     dgvMain.DataSource = alMainGrid;

Upvotes: 1

Views: 893

Answers (2)

Jason Roell
Jason Roell

Reputation: 6809

When you assign table.LayTeamId in the line a couple of lines above where you are seeing the exception:

table.LayTeamId = tableDesc;

I expect that tableDesc is assigning a value to table.LayTeamId that cannot be parsed to an Int and then blows up when you try to parse it here:

command.CommandText = String.Format("SELECT TeamDesc FROM Team WHERE TeamId ={0}", int.Parse(table.LayTeamId));

NOTE:

This is a bad way to form queries by concatenating strings. This will leave you vulnerable to SQL Injection attacks if you aren't careful. Use parameterized queries to sanitize your queries before you execute them on your database.

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

command.CommandText = String.Format("SELECT CTDesc FROM CutTable WHERE CTId ={0}", (table.LayTableId == "") ? -1 : Convert.ToInt32(table.LayTableId);


command.CommandText = String.Format("SELECT TeamDesc FROM Team WHERE TeamId ={0}", (table.LayTeamId == "") ? -1 : Convert.ToInt32(table.LayTeamId);

Upvotes: 0

Related Questions