none
Can't perform Insert operation using command.ExecuteNonQuery() > 0 RRS feed

  • Question

  • Hello,

     

    Can someone tell me what is wrong with the following code?  All it does is to do an Insert query.  However, it always throw an exception after running

    "return command.ExecuteNonQuery() > 0;"

     

    Any ideas why?  Thanks.

     

        public class dBInsert
        {
            private string connection = "";
            private string queryProperty = @"insert     into    Property
                                                                    ([name], dateAcquired, notes, landLordId, propertyTypeCode)
                                                                    values
                                                                    ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";

            public dBInsert()
            { }

     

            // Constructor called with connection string

            public dBInsert(string connString)
            {
                this.connection = connString;
            }

     

           // Called by other objects to populate the Insert statement with the appropriate variables

            public void insertProperty(string name, string dateAcquired, string notes, int landLordId, int propertyTypeCode)
            {
                string insertString = string.Format(queryProperty, name, dateAcquired, notes, landLordId, propertyTypeCode);
                insertDb(insertString);
            }

     

            // Generic insert method called by other local methods
            public bool insertDb(String insertString)
            {
                using (SqlConnection connection = new SqlConnection(this.connection))
                {
                    try
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter();

                        SqlCommand command = new SqlCommand(insertString, connection);

                        return command.ExecuteNonQuery() > 0;
                    }
                    catch (Exception ex)
                    {
                        throw new ApplicationException("Cannot perform query with " + insertString);
                    }
                }
            }

        }
    }

    Tuesday, August 28, 2007 2:38 PM

All replies

  • Since you've caught the exception, and your handler throws the exception information away, you don't know what kind of exception you're getting, and so diagnosing the problem is going to be impossible.  The first thing you need to do is remove your exception handler and let the method throw the exception it's actually throwing.

     

    No, that's the second thing you need to do.  The first thing you need to do is replace your use of String.Format to construct a SQL via string operations with a parameter query.  Never construct SQL via string operations.  It makes your application incredibly vulnerable to both deliberate attacks and inadvertant mistakes.  (For instance:  are you sure that none of the string values you're trying to insert contains a quotation mark?  If they do, your query breaks.)

     

    Never construct SQL via string operations.  Always use parameters.

     

    Tuesday, August 28, 2007 4:12 PM
  • - You have like 5 column names, and are giving 10 values?

    - Btw, your approach with string.format is unsecure and broken...  Use parameter binding instead.
    More info: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
    Tuesday, August 28, 2007 6:36 PM
  • What they said.

     

    And your method is also likely to give you problems with dates and some other datatypes. Plus what if one of your parameters has a null value?

     

    We're not trying to gang-up on you, just pointing you in a direction that will help you to avoid many future problems.

     

    Sean

    Tuesday, August 28, 2007 11:38 PM

  • Hi

      In insert statement u mentioned 5 columns but u r passing 10 values.It may be the error.Plz check out

    Thank u
    Baba
    Wednesday, August 29, 2007 3:08 AM
  • Hello,

     

    Hey thanks guys for the many replies.

    1) Sorry about the confusion on the extra parameters passing in.  There were actually that many parameters but I deleted the column names just to clear up the clutter and forgot to delete also the parameters.

    2) I did remove the exception handler to see what happen (thanks for the suggestion), and then it says:

    ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

     

    Now this is strange as the statement is being run inside:

    using (SqlConnection connection = new SqlConnection(this.connection)

     

    Isn't this going to enable the connection?  I have other codes that does query (not Insert) using the same method and they all work fine (ie I don't use connection.open())

     

    Wednesday, August 29, 2007 3:13 PM
  • With the code that you provide, we can be sure that your insertDb method does NOT open a connection to the database, which results in an Exception (can't say anything about your other methods, and why they would work, because you didn't show us that code).



    Code Snippet

    using (SqlConnection connection = new SqlConnection(this.connection))

    {
     connection.Open();

     using (SqlCommand command = connection.CreateCommand(insertString))
     {
     return command.ExecuteNonQuery() > 0;
     }

    }


    (And i'll repeat the suggestion to not build your insertString manually and use Parameter binding instead)


    Wednesday, August 29, 2007 3:21 PM