none
Error when inserting data into sql database (SqlException was unhandled by user code)

    Question

  • I have met with this error while trying to input data from a wcf service to a Sql database. I couldn't figure out what went wrong thus i though maybe you pros could find out what is wrong with just one look. Any help would be greatly appreciated.

    Basically i have a table named "Patient" with 6 fields.

    SqlException was unhandled by user code

    The parameterized query '(@Health_insurance_NO nvarchar(4000),@First_name nvarchar(4000),' expects the parameter '@Health_insurance_NO', which was not supplied.

    My code is a below.

    SqlConnection conn = new SqlConnection(sConnString);
            conn.Open();//open database connection
    
            SqlCommand cmd = new SqlCommand("insert into Patient(Health_insurance_NO, First_name,Last_name, Phone_number, Address, Email) values(@Health_insurance_NO,@First_name,@Last_name,@Phone_number,@Address,@Email)", conn);
    
            cmd.Parameters.AddWithValue("@Health_insurance_NO", userInfo.InsuranceNum);
                cmd.Parameters.AddWithValue("@First_name", userInfo.FirstName);
                cmd.Parameters.AddWithValue("@Last_name", userInfo.LastName);
                cmd.Parameters.AddWithValue("@Phone_number", userInfo.PhoneNumber);
                cmd.Parameters.AddWithValue("@Address", userInfo.Address);
                cmd.Parameters.AddWithValue("@Email", userInfo.Email);
                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                {
                    conn.Close();
                    return true;
                }
                else
                {   
                    conn.Close();
                   return false;
                }


    • Edited by ss1w Monday, September 23, 2013 3:30 PM
    Monday, September 23, 2013 3:29 PM

Answers

  • I normally use the SQL Mananger in the Server to confirm that my SQL statment are correct.  The SQL Manager has better error messages than VS application.  I normally put a break point in the code on the ExecuteNonQuery() line in the code.  Then copy from the VS code the variable values into notepad and create a SQL script that exactly matches what you are doing in your application.  The paste the SQL Script into the SQL Manager on the server.

    The AddWithValues method uses a table in the Net Library to cast the Net library variable type to a SQL Data type.  The Add does a cast of the Net Library Type to the SQL type defined in the Add() statement.  So using a AddWithValues method may use for "$123.45" a string, but with a Add method you would specify a type SqlDbType.Money so the Net library would parse the string type to a money type.

    Below are the Net library enumeration for a SqlDbType.  If a mismatch occured, VS would give a better error message when the error occured during the VS cast.  Instead you are getting an error back from the server indicating the server could not parse the command and not specifying the exact cause of the error.

       // Summary:
        //     Specifies SQL Server-specific data type of a field, property, for use in
        //     a System.Data.SqlClient.SqlParameter.
        public enum SqlDbType
        {
            // Summary:
            //     System.Int64. A 64-bit signed integer.
            BigInt = 0,
            //
            // Summary:
            //     System.Array of type System.Byte. A fixed-length stream of binary data ranging
            //     between 1 and 8,000 bytes.
            Binary = 1,
            //
            // Summary:
            //     System.Boolean. An unsigned numeric value that can be 0, 1, or null.
            Bit = 2,
            //
            // Summary:
            //     System.String. A fixed-length stream of non-Unicode characters ranging between
            //     1 and 8,000 characters.
            Char = 3,
            //
            // Summary:
            //     System.DateTime. Date and time data ranging in value from January 1, 1753
            //     to December 31, 9999 to an accuracy of 3.33 milliseconds.
            DateTime = 4,
            //
            // Summary:
            //     System.Decimal. A fixed precision and scale numeric value between -10 38
            //     -1 and 10 38 -1.
            Decimal = 5,
            //
            // Summary:
            //     System.Double. A floating point number within the range of -1.79E +308 through
            //     1.79E +308.
            Float = 6,
            //
            // Summary:
            //     System.Array of type System.Byte. A variable-length stream of binary data
            //     ranging from 0 to 2 31 -1 (or 2,147,483,647) bytes.
            Image = 7,
            //
            // Summary:
            //     System.Int32. A 32-bit signed integer.
            Int = 8,
            //
            // Summary:
            //     System.Decimal. A currency value ranging from -2 63 (or -9,223,372,036,854,775,808)
            //     to 2 63 -1 (or +9,223,372,036,854,775,807) with an accuracy to a ten-thousandth
            //     of a currency unit.
            Money = 9,
            //
            // Summary:
            //     System.String. A fixed-length stream of Unicode characters ranging between
            //     1 and 4,000 characters.
            NChar = 10,
            //
            // Summary:
            //     System.String. A variable-length stream of Unicode data with a maximum length
            //     of 2 30 - 1 (or 1,073,741,823) characters.
            NText = 11,
            //
            // Summary:
            //     System.String. A variable-length stream of Unicode characters ranging between
            //     1 and 4,000 characters. Implicit conversion fails if the string is greater
            //     than 4,000 characters. Explicitly set the object when working with strings
            //     longer than 4,000 characters.
            NVarChar = 12,
            //
            // Summary:
            //     System.Single. A floating point number within the range of -3.40E +38 through
            //     3.40E +38.
            Real = 13,
            //
            // Summary:
            //     System.Guid. A globally unique identifier (or GUID).
            UniqueIdentifier = 14,
            //
            // Summary:
            //     System.DateTime. Date and time data ranging in value from January 1, 1900
            //     to June 6, 2079 to an accuracy of one minute.
            SmallDateTime = 15,
            //
            // Summary:
            //     System.Int16. A 16-bit signed integer.
            SmallInt = 16,
            //
            // Summary:
            //     System.Decimal. A currency value ranging from -214,748.3648 to +214,748.3647
            //     with an accuracy to a ten-thousandth of a currency unit.
            SmallMoney = 17,
            //
            // Summary:
            //     System.String. A variable-length stream of non-Unicode data with a maximum
            //     length of 2 31 -1 (or 2,147,483,647) characters.
            Text = 18,
            //
            // Summary:
            //     System.Array of type System.Byte. Automatically generated binary numbers,
            //     which are guaranteed to be unique within a database. timestamp is used typically
            //     as a mechanism for version-stamping table rows. The storage size is 8 bytes.
            Timestamp = 19,
            //
            // Summary:
            //     System.Byte. An 8-bit unsigned integer.
            TinyInt = 20,
            //
            // Summary:
            //     System.Array of type System.Byte. A variable-length stream of binary data
            //     ranging between 1 and 8,000 bytes. Implicit conversion fails if the byte
            //     array is greater than 8,000 bytes. Explicitly set the object when working
            //     with byte arrays larger than 8,000 bytes.
            VarBinary = 21,
            //
            // Summary:
            //     System.String. A variable-length stream of non-Unicode characters ranging
            //     between 1 and 8,000 characters.
            VarChar = 22,
            //
            // Summary:
            //     System.Object. A special data type that can contain numeric, string, binary,
            //     or date data as well as the SQL Server values Empty and Null, which is assumed
            //     if no other type is declared.
            Variant = 23,
            //
            // Summary:
            //     An XML value. Obtain the XML as a string using the System.Data.SqlClient.SqlDataReader.GetValue(System.Int32)
            //     method or System.Data.SqlTypes.SqlXml.Value property, or as an System.Xml.XmlReader
            //     by calling the System.Data.SqlTypes.SqlXml.CreateReader() method.
            Xml = 25,
            //
            // Summary:
            //     A SQL Server 2005 user-defined type (UDT).
            Udt = 29,
            //
            // Summary:
            //     A special data type for specifying structured data contained in table-valued
            //     parameters.
            Structured = 30,
            //
            // Summary:
            //     Date data ranging in value from January 1,1 AD through December 31, 9999
            //     AD.
            Date = 31,
            //
            // Summary:
            //     Time data based on a 24-hour clock. Time value range is 00:00:00 through
            //     23:59:59.9999999 with an accuracy of 100 nanoseconds.
            Time = 32,
            //
            // Summary:
            //     Date and time data. Date value range is from January 1,1 AD through December
            //     31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an
            //     accuracy of 100 nanoseconds.
            DateTime2 = 33,
            //
            // Summary:
            //     Date and time data with time zone awareness. Date value range is from January
            //     1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through
            //     23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range
            //     is -14:00 through +14:00.
            DateTimeOffset = 34,
        }


    jdweng

    • Proposed as answer by EmreAydemir Tuesday, September 24, 2013 10:20 AM
    • Marked as answer by ss1w Tuesday, September 24, 2013 10:52 AM
    Tuesday, September 24, 2013 2:54 AM

All replies

  • That looks fine.  Can you reproduce the error in a small, self-contained sample application?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, September 23, 2013 3:34 PM
  • You can try the SQL statement in the SQL Manager on the server to make sure the field names are spelled correctly or field types are wrong..  Aslo if you are getting an exception you can press the shortcut on the exception "Copy To clipboard" and paste into notepad.  You can also post the full exception which may give additional information.  I also prefer to use the Add rather than AddWithValues so I can specify the type of data that is being transfered like below.  You may have an issue with some of the fields in your code not matching the database.  You may have to convert integers to strings or strings to integer.  The exception stack may give a clue to clue to the problem. 

                cmd.Parameters.Add("@Health_insurance_NO", SqlDbType.Int);
                cmd.Parameters["@Health_insurance_NO"]   = userInfo.InsuranceNum;


    jdweng

    Monday, September 23, 2013 3:48 PM
  • What's the datatype of userInfo.InsuranceNum? Do you need to convert it to a string first?

    Chris "Is there anything besides programming?"

    Monday, September 23, 2013 10:23 PM
  • The data type would be varchar(50). I wasnt having the impression i have to convert to string since there wasnt any error while implementing.
    Monday, September 23, 2013 11:30 PM
  • Hi david. I am actually doing it as a wcf service. So i am assuming if my test as a wcf client fails, it will fail everywhere else.
    Monday, September 23, 2013 11:59 PM
  • Hi Joel. The full exception is as shown. 

    I am beginning to suspect the mismatch of the value too. Is varchar(50) considered as a string ? also my phoneNum is a int. and what are the difference between Add and AddwithValue ?

    System.Data.SqlClient.SqlException was unhandled by user code
      HResult=-2146232060
      Message=The parameterized query '(@Health_Insurance_NO nvarchar(4000),@First_name nvarchar(4000),' expects the parameter '@Health_Insurance_NO', which was not supplied.
      Source=.Net SqlClient Data Provider
      ErrorCode=-2146232060
      Class=16
      LineNumber=0
      Number=8178
      Procedure=""
      Server=.\SQLEXPRESS
      State=1
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
           at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at Assignment_4.ADODatabaseService.PatientRegistration(PatientsDetails userInfo) in c:\Users\user\Dropbox\University of Queensland\Sem 2 2013\INFS3204\Practical\4\Assignment 4\Assignment 4\ADODatabaseService.svc.cs:line 32
           at SyncInvokePatientRegistration(Object , Object[] , Object[] )
           at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
           at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
      InnerException: 
    Cheers mate


    • Edited by ss1w Tuesday, September 24, 2013 12:52 AM
    Tuesday, September 24, 2013 12:13 AM
  • I normally use the SQL Mananger in the Server to confirm that my SQL statment are correct.  The SQL Manager has better error messages than VS application.  I normally put a break point in the code on the ExecuteNonQuery() line in the code.  Then copy from the VS code the variable values into notepad and create a SQL script that exactly matches what you are doing in your application.  The paste the SQL Script into the SQL Manager on the server.

    The AddWithValues method uses a table in the Net Library to cast the Net library variable type to a SQL Data type.  The Add does a cast of the Net Library Type to the SQL type defined in the Add() statement.  So using a AddWithValues method may use for "$123.45" a string, but with a Add method you would specify a type SqlDbType.Money so the Net library would parse the string type to a money type.

    Below are the Net library enumeration for a SqlDbType.  If a mismatch occured, VS would give a better error message when the error occured during the VS cast.  Instead you are getting an error back from the server indicating the server could not parse the command and not specifying the exact cause of the error.

       // Summary:
        //     Specifies SQL Server-specific data type of a field, property, for use in
        //     a System.Data.SqlClient.SqlParameter.
        public enum SqlDbType
        {
            // Summary:
            //     System.Int64. A 64-bit signed integer.
            BigInt = 0,
            //
            // Summary:
            //     System.Array of type System.Byte. A fixed-length stream of binary data ranging
            //     between 1 and 8,000 bytes.
            Binary = 1,
            //
            // Summary:
            //     System.Boolean. An unsigned numeric value that can be 0, 1, or null.
            Bit = 2,
            //
            // Summary:
            //     System.String. A fixed-length stream of non-Unicode characters ranging between
            //     1 and 8,000 characters.
            Char = 3,
            //
            // Summary:
            //     System.DateTime. Date and time data ranging in value from January 1, 1753
            //     to December 31, 9999 to an accuracy of 3.33 milliseconds.
            DateTime = 4,
            //
            // Summary:
            //     System.Decimal. A fixed precision and scale numeric value between -10 38
            //     -1 and 10 38 -1.
            Decimal = 5,
            //
            // Summary:
            //     System.Double. A floating point number within the range of -1.79E +308 through
            //     1.79E +308.
            Float = 6,
            //
            // Summary:
            //     System.Array of type System.Byte. A variable-length stream of binary data
            //     ranging from 0 to 2 31 -1 (or 2,147,483,647) bytes.
            Image = 7,
            //
            // Summary:
            //     System.Int32. A 32-bit signed integer.
            Int = 8,
            //
            // Summary:
            //     System.Decimal. A currency value ranging from -2 63 (or -9,223,372,036,854,775,808)
            //     to 2 63 -1 (or +9,223,372,036,854,775,807) with an accuracy to a ten-thousandth
            //     of a currency unit.
            Money = 9,
            //
            // Summary:
            //     System.String. A fixed-length stream of Unicode characters ranging between
            //     1 and 4,000 characters.
            NChar = 10,
            //
            // Summary:
            //     System.String. A variable-length stream of Unicode data with a maximum length
            //     of 2 30 - 1 (or 1,073,741,823) characters.
            NText = 11,
            //
            // Summary:
            //     System.String. A variable-length stream of Unicode characters ranging between
            //     1 and 4,000 characters. Implicit conversion fails if the string is greater
            //     than 4,000 characters. Explicitly set the object when working with strings
            //     longer than 4,000 characters.
            NVarChar = 12,
            //
            // Summary:
            //     System.Single. A floating point number within the range of -3.40E +38 through
            //     3.40E +38.
            Real = 13,
            //
            // Summary:
            //     System.Guid. A globally unique identifier (or GUID).
            UniqueIdentifier = 14,
            //
            // Summary:
            //     System.DateTime. Date and time data ranging in value from January 1, 1900
            //     to June 6, 2079 to an accuracy of one minute.
            SmallDateTime = 15,
            //
            // Summary:
            //     System.Int16. A 16-bit signed integer.
            SmallInt = 16,
            //
            // Summary:
            //     System.Decimal. A currency value ranging from -214,748.3648 to +214,748.3647
            //     with an accuracy to a ten-thousandth of a currency unit.
            SmallMoney = 17,
            //
            // Summary:
            //     System.String. A variable-length stream of non-Unicode data with a maximum
            //     length of 2 31 -1 (or 2,147,483,647) characters.
            Text = 18,
            //
            // Summary:
            //     System.Array of type System.Byte. Automatically generated binary numbers,
            //     which are guaranteed to be unique within a database. timestamp is used typically
            //     as a mechanism for version-stamping table rows. The storage size is 8 bytes.
            Timestamp = 19,
            //
            // Summary:
            //     System.Byte. An 8-bit unsigned integer.
            TinyInt = 20,
            //
            // Summary:
            //     System.Array of type System.Byte. A variable-length stream of binary data
            //     ranging between 1 and 8,000 bytes. Implicit conversion fails if the byte
            //     array is greater than 8,000 bytes. Explicitly set the object when working
            //     with byte arrays larger than 8,000 bytes.
            VarBinary = 21,
            //
            // Summary:
            //     System.String. A variable-length stream of non-Unicode characters ranging
            //     between 1 and 8,000 characters.
            VarChar = 22,
            //
            // Summary:
            //     System.Object. A special data type that can contain numeric, string, binary,
            //     or date data as well as the SQL Server values Empty and Null, which is assumed
            //     if no other type is declared.
            Variant = 23,
            //
            // Summary:
            //     An XML value. Obtain the XML as a string using the System.Data.SqlClient.SqlDataReader.GetValue(System.Int32)
            //     method or System.Data.SqlTypes.SqlXml.Value property, or as an System.Xml.XmlReader
            //     by calling the System.Data.SqlTypes.SqlXml.CreateReader() method.
            Xml = 25,
            //
            // Summary:
            //     A SQL Server 2005 user-defined type (UDT).
            Udt = 29,
            //
            // Summary:
            //     A special data type for specifying structured data contained in table-valued
            //     parameters.
            Structured = 30,
            //
            // Summary:
            //     Date data ranging in value from January 1,1 AD through December 31, 9999
            //     AD.
            Date = 31,
            //
            // Summary:
            //     Time data based on a 24-hour clock. Time value range is 00:00:00 through
            //     23:59:59.9999999 with an accuracy of 100 nanoseconds.
            Time = 32,
            //
            // Summary:
            //     Date and time data. Date value range is from January 1,1 AD through December
            //     31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an
            //     accuracy of 100 nanoseconds.
            DateTime2 = 33,
            //
            // Summary:
            //     Date and time data with time zone awareness. Date value range is from January
            //     1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through
            //     23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range
            //     is -14:00 through +14:00.
            DateTimeOffset = 34,
        }


    jdweng

    • Proposed as answer by EmreAydemir Tuesday, September 24, 2013 10:20 AM
    • Marked as answer by ss1w Tuesday, September 24, 2013 10:52 AM
    Tuesday, September 24, 2013 2:54 AM
  • Hi david. I am actually doing it as a wcf service. So i am assuming if my test as a wcf client fails, it will fail everywhere else.

    I understand, but your code looks fine, so your assumption may be incorrect. Can you reproduce the error in a small, self-contained sample application?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 24, 2013 4:05 AM
  • Hi 

       Kidly check userInfo.InsuranceNum is not null

    Thanks

    Sowmiya

    Tuesday, September 24, 2013 7:16 AM
  • maybe @Health_insurance_NO is identity field (has auto increment) in sqlServer.


    • Edited by EmreAydemir Tuesday, September 24, 2013 10:22 AM
    Tuesday, September 24, 2013 10:21 AM