locked
cmd.ExecuteNonQuery() doesn't execute RRS feed

  • Question

  • Hi,

    I have this code

    1; j++)/ { Console.WriteLine(@"Please enter" + fields[j]); switch (types[j]) { case "int": cmd.Parameters.Add("@inputs" + j, SqlDbType.Int); break; case "float": cmd.Parameters.Add("@inputs" + j, SqlDbType.Float); break; default: cmd.Parameters.Add("@inputs" + j, SqlDbType.VarChar); break; }

    cmd.Parameters[j].Value = fields[j]; } cmd.Prepare(); cmd.ExecuteNonQuery();


    but it stops (throughs an error ) on the last sentence, the bolded.

    Can anybody help me please where I go wrong?

    Thanks






    • Edited by pet06 Wednesday, April 2, 2014 12:52 AM
    Wednesday, April 2, 2014 12:48 AM

Answers

  • Hi pet06,

    According to your code, we need to verify if you want to use ExecuteNonQuery method will return number of rows effected with INSERT by VB.NET or C#.NET. If yes,  the issue regards VB.NET, I suggest you post the question in the VB.NET forums at http://social.msdn.microsoft.com/Forums/en-US/home?forum=vbgeneral. It is appropriate and more experts will assist you.

    There is detail about executenonquery example in asp.net using C#.net and VB.NET, you can review the following article.
    http://www.aspdotnet-suresh.com/2012/09/executenonquery-example-in-aspnet-using.html

    Hope it can help.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 2:03 AM
  • In addition to the latest reply about profiler, put a break on the ExecuteNonQuery line and capture all information about cmd in the debugger. In particular, CommandText and parameters will be interesting to see.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 2:51 AM
    Answerer
  • I have sold it. Thank you everybody
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 3:12 AM

All replies

  • but it stops (throughs an error ) on the last sentence, the bolded.

    Hello,

    And what's the error message you are getting; we can't guess that? How does the complete SQL Statement look like?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 2, 2014 5:11 AM
    Answerer
  • it is says, fail to convert parameter value from string to a int32
    • Edited by pet06 Wednesday, April 2, 2014 11:54 AM
    Wednesday, April 2, 2014 11:48 AM
  • So, you a string value assigned to a parameter that accepts only integer values. Or you change the datatype of the parameter to string (aka VARCHAR) or you pass a integer value to that command parameter (on line cmd.Parameters[j].Value = fields[j];)

    http://sqlhint.com


    • Edited by MihBjn Wednesday, April 2, 2014 12:08 PM
    • Proposed as answer by C0mmander Wednesday, April 2, 2014 12:11 PM
    • Unproposed as answer by C0mmander Wednesday, April 2, 2014 2:07 PM
    Wednesday, April 2, 2014 12:08 PM
  • Now it says that parameter @input1 is not declared, but I declare it in

    string insertString="insert into " + line + " values(";
                for (int i = 1; i <= number; i++)
                {
                    insertString += "@inputs" + i; // +",";
                    if (i != number)
                    {
                        insertString += ", ";
                    }
                    else
                    {
                        insertString += ")";
                    }
                }

    before the for loop where the switch is

    Wednesday, April 2, 2014 1:23 PM
  • This is not the declaration. Declaration means the definition of the variables which is done in the switch (cmd.Parameters.Add).

    Maybe the error is due to the fact that you are executing an insert statement against the database with variables that have no value;

    Can you try using

    cmd.Parameters.Add("@inputs" + j, SqlDbType.Int) = enteredValue;

    in the switch as defined here

    http://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2

    Wednesday, April 2, 2014 1:35 PM
  • now it says "can not implicitly convert type 'string' to 'System.Data.SqlClient.SqlParameter'

    This is the case statemen for int now:

     case "int": cmd.Parameters.Add("@inputs" + j, SqlDbType.Int) = fields[j];
                               //  cmd.Parameters[j].Value = int.Parse(fields[j]);
                                 break;


    • Edited by pet06 Wednesday, April 2, 2014 1:57 PM
    Wednesday, April 2, 2014 1:54 PM
  • 
    
    case "int": cmd.Parameters.Add("@inputs" + j, SqlDbType.Int);
                cmd.Parameters["@inputs"+j].Value = int.Parse(fields[j]);
                                 break;

    Wednesday, April 2, 2014 2:02 PM
  • Again I get error "must declare the scalar variable @input1 "
    Wednesday, April 2, 2014 2:32 PM
  • Hi pet06,

    According to your code, we need to verify if you want to use ExecuteNonQuery method will return number of rows effected with INSERT by VB.NET or C#.NET. If yes,  the issue regards VB.NET, I suggest you post the question in the VB.NET forums at http://social.msdn.microsoft.com/Forums/en-US/home?forum=vbgeneral. It is appropriate and more experts will assist you.

    There is detail about executenonquery example in asp.net using C#.net and VB.NET, you can review the following article.
    http://www.aspdotnet-suresh.com/2012/09/executenonquery-example-in-aspnet-using.html

    Hope it can help.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 2:03 AM
  • In fact, you posted your question in wrong thread.  As Sofiya Li suggested, you may post it at VB forums, you might get better help.

    My try is as below: The error message is looking a SQL Server error. You will be able to get better idea if you profile and find whats the query is being fired during the execution. Could you please share the code captured in the profiler to help you better?

    • Proposed as answer by Naomi NEditor Friday, April 4, 2014 2:49 AM
    Friday, April 4, 2014 2:25 AM
    Answerer
  • In addition to the latest reply about profiler, put a break on the ExecuteNonQuery line and capture all information about cmd in the debugger. In particular, CommandText and parameters will be interesting to see.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 2:51 AM
    Answerer
  • I have sold it. Thank you everybody
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:44 AM
    Friday, April 4, 2014 3:12 AM