Input string was not in a correct format in SqlCommand

Question

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;

Looks like table.LayTeamId is an empty string or contains non digit characters. — ekad
– ekad, Commented Feb 6, 2014 at 4:07
BTW you should not concat strings to form your query. That's a good way to make yourself vulnerable to SQL Injection attacks — Jason Roell
– Jason Roell, Commented Feb 6, 2014 at 4:08

Jason Roell · Accepted Answer · 2014-02-06 04:17:44Z

2

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.

edited Feb 6, 2014 at 4:17

answered Feb 6, 2014 at 4:10

Jason Roell

6,8474 gold badges23 silver badges30 bronze badges

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

Comments

Vignesh Kumar A · Accepted Answer · 2014-02-06 04:11:53Z

0

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);

answered Feb 6, 2014 at 4:11

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Collectives™ on Stack Overflow

Input string was not in a correct format in SqlCommand

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related