• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1403
  • Last Modified:

Unable to save data to a table using LINQ

I am trying to save data to a table using an InsertonSubmit.  The program finishes without error but when I go to the table to see the records, there are no records.  After reviewing some debug, it tells me that there is a possiblity for truncation of data, therefore it did not right the record.  How do you determine which field has the problem where data truncation might be occuring.  Here is my code and screenshot of the error message.


Detailed Error message
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges()
if (type == "PATIENT")
                                db.PATIENTs.InsertOnSubmit(record as PATIENT);
                                
 
                            try
                            {
                                // Attempt to update the database
                                db.SubmitChanges();----------------------------------Error is trapped Here
                            }
                            catch (DuplicateKeyException duppatexc)
                            {
                                // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                // in the DataContect and not the database, reason why it was not catching it.
                                // Remove record from DataContext and continue.
                                switch (type)
                                {
                                    case "PATIENT":
                                        db.PATIENTs.DeleteOnSubmit(record as PATIENT);
                                        break;
                                }
                            }
                            catch (SqlException patientExc)
                            {
                                // This is a duplicate record found in the databas, remove record from DataContext.
                                switch (type)
                                {
                                    case "PATIENT":
                                        db.PATIENTs.DeleteOnSubmit(record as PATIENT);
                                        break;
                                }

Open in new window

LINQ4.jpg
0
kwh3856
Asked:
kwh3856
  • 2
1 Solution
 
aibusinesssolutionsCommented:
Truncation of data means that one of your variables are longer than the specified length in SQL.

IE if you have Patient Name set as a Varchar(10), and you try to send a name that is 15 characters long, it will throw this error.

The only way to test that would be to put a break point on the InsertOnSubmit line, and examine the "patient" object, you should be able to view each of the variables, and see which one is too long.  You may have to look at your SQL table to get the allowed lengths first.
0
 
Fernando SotoCommented:
Hi Kenny;

Add this function in the code snippet to your code, outside of Main then just before calling :

db.PATIENTs.InsertOnSubmit(record as PATIENT);

Place the following statements:

PrintFields(record );    // Will report the length of all String and Binary fields that have values
Console.ReadLine();   // Will pause the program now verify that non of the file are longer then define in DB

The field that is longer then what is defined in the DB is the problem. The option is to make the DB field longer to accept larger data or to truncate the fields to the size defined in the DB.

Hope this helps.

Fernando
// Add this using statement to the top of the code file
using System.Reflection;
 
 
private static void PrintFields(Object pRecord)
{
    // Print the header of the report
    Console.WriteLine("Length   Field Name");
    FieldInfo[] fInfos = pRecord.GetType().GetFields(BindingFlags.Public | 
                                                     BindingFlags.NonPublic | 
                                                     BindingFlags.Instance);
    // Iterate through all the fields and print the length of each string
    // or binary filed.
    foreach(FieldInfo fInfo in fInfos)
    {
        // String Fields
        if (fInfo.FieldType == typeof(string))
        {
            // Get the value of the field
            Object value = fInfo.GetValue(pRecord);
            // If value has data print its field lentgth and name
            if (value != null)
            {
                Console.WriteLine(String.Format("{0,-8} {1}", 
                    value.ToString().Length, fInfo.Name ));
            }
        }
        // Binary Fields
        if (fInfo.FieldType == typeof(Binary))
        {
            Binary value = (Binary)fInfo.GetValue(pRecord);
            if (value != null)
            {
                Console.WriteLine(String.Format("{0,-8} {1}", 
                    value.Length, fInfo.Name ));
            }
        }
    }
} 

Open in new window

0
 
kwh3856Author Commented:
Once again.  Thank you.  My SSN field was 9 characters in the db.  I forgot to include the dashes in the SSN:)
Thanks
Kenny
0
 
Fernando SotoCommented:
No problem, glad to be of help.  ;=)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now