locked
Line gives me Input string was not in a correct format RRS feed

  • Question

  • User1048792302 posted

    myCon = new SqlConnection(@"myconnectionstring");
    myCon.Open();
    string id_trender = "update b set b.id_trender = a.id_trender FROM trenders a JOIN adjustments b ON a.dim_name_trender = b.name";
    //var cmd1 = new SqlCommand(id_trender, myCon);
    var cmd1 = new SqlCommand(id_trender, myCon);
    int recordsAffected = cmd1.ExecuteNonQuery();
    cmd1 = new SqlCommand("INSERT INTO adjustments(id_trender) VALUES " + "(@id_trender)", myCon);
    // cmd1.Parameters.AddWithValue("@id_trender", SqlDbType.Int).Value = id_trender;
    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, Convert.ToInt32(id_trender));

    cmd1.ExecuteNonQuery();
    myCon.Close();

    I get error at: cmd1.Parameters.Add("@id_trender", SqlDbType.Int, Convert.ToInt32(id_trender));

    Tuesday, August 7, 2018 10:34 AM

All replies

  • User-369506445 posted

    hi

    The error means that the string you're trying to parse an integer from doesn't actually contain a valid integer.

    please try this

    int val=0;
    
    if (int.TryParse(id_trender, out val))
      cmd1.Parameters.Add("@id_trender", SqlDbType.Int, val);

    Tuesday, August 7, 2018 10:44 AM
  • User1048792302 posted

    That doesn't seem to work unfortunately, I get the error:

    The parameterized query '(@id_trender int)INSERT INTO adjustments(id_trender) VALUES (@id' expects the parameter '@id_trender', which was not supplied.'

    Tuesday, August 7, 2018 10:51 AM
  • User-369506445 posted

    please try below

               int val;
    
                if (int.TryParse(id_trender, out val))
                    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, val);
                else
                    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, 0);

    Tuesday, August 7, 2018 10:56 AM
  • User1048792302 posted

    It says cmd1 is null

    Tuesday, August 7, 2018 11:02 AM
  • User-369506445 posted

    what's your mean exactly?

    did you get <g class="gr_ gr_37 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="37" data-gr-id="37">erro</g>? where<g class="gr_ gr_59 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" id="59" data-gr-id="59">?what</g>?

    Tuesday, August 7, 2018 11:24 AM
  • User753101303 posted

    Hi,

    You put that too early or the previous "fix" would skip adding  a mandatory parameter. Anyway you see id_trender is the SQL statement string you are trying to run and later you try to convert this string to an integer. More likely you mistakenly reused a variable for a SQL statement when you should have maybe created another string value ?

    IMO it's always worth to take few more minutes to really understand the issue rather than trying to rush for a fix especially if it would make the application to run under a situation that shouldn't happen anyway as here.

    Edit:

    myCon = new SqlConnection(@"myconnectionstring");
    myCon.Open();
    string id_trender = "update b set b.id_trender = a.id_trender FROM trenders a JOIN adjustments b ON a.dim_name_trender = b.name";
    //var cmd1 = new SqlCommand(id_trender, myCon);
    var cmd1 = new SqlCommand(id_trender, myCon);
    int recordsAffected = cmd1.ExecuteNonQuery();
    cmd1 = new SqlCommand("INSERT INTO adjustments(id_trender) VALUES " + "(@id_trender)", myCon);
    // cmd1.Parameters.AddWithValue("@id_trender", SqlDbType.Int).Value = id_trender;
    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, Convert.ToInt32(id_trender)); // Try to convert a SQL statement string to an int ??
    
    cmd1.ExecuteNonQuery();
    myCon.Close();

    Edit 2 you likely want something such as:

    myCon = new SqlConnection(@"myconnectionstring");
    myCon.Open();
    string sql= "update b set b.id_trender = a.id_trender FROM trenders a JOIN adjustments b ON a.dim_name_trender = b.name";
    var cmd1 = new SqlCommand(sql, myCon);
    int recordsAffected = cmd1.ExecuteNonQuery();
    cmd1 = new SqlCommand("INSERT INTO adjustments(id_trender) VALUES " + "(@id_trender)", myCon);
    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, Convert.ToInt32(id_trender)); // Now left to its original value
    cmd1.ExecuteNonQuery();
    myCon.Close();

    and id_trender is left unchanged and hopefully is a string that can be converted to an integer ? It seems also a bit weird that you have to do this kind of id copy based on a name but I don't know the details of your process

    Tuesday, August 7, 2018 12:03 PM
  • User1048792302 posted

    How would I create a string value from my update statement, out of curiosity?

    The solution doesn't work here:

    cmd1.Parameters.Add("@id_trender", SqlDbType.Int, Convert.ToInt32(id_trender));
    because instead of Convert.ToInt32(id_trender)); it expects sql not id_trender
    Tuesday, August 7, 2018 12:59 PM
  • User753101303 posted

    As this is as string it would be something such as (or VarChar depending on your db side declaration) :

    cmd1.Parameters.Add("@id_trender", SqlDbType.NVarChar,id_trender);

    Your naming scheme is really confusing as it seems you really have an numeric id_trender column but the C# variable is used to store a SQL statement. You really want to save this SQL statement e SQL command you just ran into the adjustments table ???

    Tuesday, August 7, 2018 1:35 PM
  • User-1171043462 posted

    string id_trender = "update b set b.id_trender = a.id_trender FROM trenders a JOIN adjustments b ON a.dim_name_trender = b.name";

    Above you have set a string value i.e. a SQL Query. Then how can you convert it to Integer later?

    var cmd1 = new SqlCommand(id_trender, myCon);

    You have also used it for creating SqlCommand

    Tuesday, August 7, 2018 2:09 PM