Linq Specified Cast is not valid

[Jdsurgeon]

ok I have a weird issue. I created a master record in the master table and then a record in the detail table. when creating the detail record i get a Specified cast is not valid error. Now another weird thing is that once I create the master record I can't query and verify it was created. But when i got to sql management studio I can see the master record. Below is my two procedures and the table definitions. Master table primary key is INVNUM child table primary key it INVLIN_ID and the reference key is INVNUM

Partial Public Class RcvMaster
	Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
	
	Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New PropertyChangingEventArgs(String.Empty)
	
	Private _INVNUM As String
	
	Private _invtyp As String
	
	Private _invdte As System.Nullable(Of Date)
	
	Private _expdte As System.Nullable(Of Date)
	
	Private _invsts As String
	
	Private _rcvcont As String
	
	Private _rcvdte As System.Nullable(Of Date)
	
	Private _credte As System.Nullable(Of Date)
	
    Private _RcvDetails As EntitySet(Of RcvDetail)

<Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.RcvDetail")> _
Partial Public Class RcvDetail
    Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged

    Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New PropertyChangingEventArgs(String.Empty)

    Private _INVLIN_ID As Integer

    Private _INVNUM As String

    Private _prtnum As String

    Private _linnum As System.Nullable(Of Integer)

    Private _expqty As System.Nullable(Of Integer)

    Private _rcvqty As System.Nullable(Of Integer)

    Private _exprdte As System.Nullable(Of Date)

    Private _lotnum As String

    Private _RcvMaster As EntityRef(Of RcvMaster)

Insert master below

If VerifyFields(False) Then
            If IsMasterCreated = True Then
                MessagePresenter.ShowMessage("You cannot create two master records complete the first one", "Master Already Exist for this receipt")
            Else
                Dim NewMasterRecord = New RcvMaster With {.expdte = ExpectedDateTimeEditor.Value,
                                                      .invdte = InvoiceDateTimeEditor.Value,
                                                      .INVNUM = CurrentInvoiceNumber,
                                                      .rcvcont = ContainerTextBox.Text}

                db.RcvMasters.InsertOnSubmit(NewMasterRecord)

                Try
                    db.SubmitChanges()
                    IsMasterCreated = True
                    InvoiceNumberTextBox.IsEnabled = False
                    ContainerTextBox.IsEnabled = False
                    MessagePresenter.ShowMessage("Master Created", "Master Created")
                Catch ex As Exception
                    MessagePresenter.ShowMessage(ex.Message, "Exception Error")
                End Try
            End If
           
        Else
            MessagePresenter.ShowMessage("Please complete all fields", "Missing Information")

        End If

Is details

If VerifyLineItem() Then
            If IsMasterCreated Then

                Dim ExpireDate As Date = ExpectedDateTimeEditor.Value
                If ExpireDate = Nothing Then
                    ExpireDate = Date.Now
                End If

                Dim check = (From I In db.RcvMasters Where I.INVNUM = Trim(CurrentInvoiceNumber) Select I.invsts).First

                Dim NewLineItemRecord As New RcvDetail With {.INVNUM = CurrentInvoiceNumber,
                                                             .expqty = CType(ExpectedQtyTextBox.Text, Integer),
                                                       .lotnum = LotNumberTextBox.Text,
                                                       .prtnum = PartNumberComboBox.SelectedValue.ToString, .exprdte = ExpireDate, .linnum = LineCount
                                                       }

                db.RcvDetails.InsertOnSubmit(NewLineItemRecord)

                Try
                    db.SubmitChanges()
                    IsCurrentlyWorking = True
                    RefreshGrid()
                    LineCount += 1
                Catch ex As Exception
                    MessagePresenter.ShowMessage(ex.Message, "Exception Error")
                End Try
            Else
                MessagePresenter.ShowMessage("Please create master invoice first", "Master invoice not created")
            End If
        End If

[Shaggy Hiker]

Can you say more about where you get the specified cast error? As a general rule, the reason for that is just as it states: You are trying to stuff a type into a field that cannot hold that type. If it is the NewLineItem that is causing the problem, I see two potential issues, though neither might be a problem in practice. For one thing lotnum suggests a Number, yet you are giving it a string. Second, prtnum...well, whatever that is, are you giving it the right type?

[Jdsurgeon]

Lotnum is a string datatype so is prtnum. I cut on option strict and did all the explicit conversions. but I am still receiving this error. I am able to create a Master record but not the detail below is the failing sub. It fails specifically at db.submitchanges.

If VerifyLineItem() Then
            If IsMasterCreated Then

                Dim ExpireDate As Date = ExpectedDateTimeEditor.Value
                If ExpireDate = Nothing Then
                    ExpireDate = Date.Now
                End If

                Dim check = (From I In db.RcvMasters Where I.INVNUM = Trim(CurrentInvoiceNumber) Select I.invsts).First

                Dim NewLineItemRecord As New RcvDetail With {.INVNUM = CurrentInvoiceNumber,
                                                             .expqty = CType(ExpectedQtyTextBox.Text, Integer),
                                                       .lotnum = LotNumberTextBox.Text,
                                                       .prtnum = PartNumberComboBox.SelectedValue.ToString, .exprdte = ExpireDate, .linnum = LineCount
                                                       }

                db.RcvDetails.InsertOnSubmit(NewLineItemRecord)

                Try
                    db.SubmitChanges()
                    IsCurrentlyWorking = True
                    RefreshGrid()
                    LineCount += 1
                Catch ex As Exception
                    MessagePresenter.ShowMessage(ex.Message, "Exception Error")
                End Try
            Else
                MessagePresenter.ShowMessage("Please create master invoice first", "Master invoice not created")
            End If
        End If

Here is the stack trace also

at System.Data.SqlClient.SqlBuffer.get_Int32()
  at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
  at Read_Object(ObjectMaterializer`1 )
  at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
  at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
  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()
  at Cheetah_Warehouse_Management.NewReceipt.Button1_Click(Object sender, RoutedEventArgs e) in C:\Users\joseph.surgeon\Documents\Visual Studio 2010\Projects\Cheetah Warehouse Management\Cheetah Warehouse Management\Receiving\NewReceipt.xaml.vb:line 96

This does not make any sense I understand the error clearly but cannot find which type it is referring to or the object. The error does not provide enough details in regards to which variable is the causing the error or which property. I have checked several times the database types and even the code types. they match exactly. You can see above the post the DBML generated classes and associated types. linq is frustrating.

[Shaggy Hiker]

LINQ can be confusing, and I haven't used LINQ to SQL, so I am a bit out, but after what you have said, I would suggest that the place to investigate is INVNUM.

What is check? For First to work, there HAS to be something returned, or else it will throw an exception, so you are clearly expecting something to be there, yet you don't appear to use check anywhere after that, so it appears to be that you have it there so that the process will crash if the check bounces.

Is it possible that the order of submitting the records is not the one you expect, such that the master record isn't recorded in the DB by the time the child is inserted? It looks like that is not the case, since you mention being able to see the master record, but I figured I might as well ask. You mentioned a second problem which kind of suggests that the record might not be there, or is part of an open transaction, or some such.

[Jdsurgeon]

The check was there so I can verify that the transaction of the master was created. It's not there to stay but just for debugging purposes so I can check the value. INVNUM is a string value varchar(20) on both Parent and child tables. The value I am sending to INVNUM is specifically being converted to a string before the insert and also it is the same INVNUM inserted into the master record. The DBML file created has the same values db type varchar(20) and system type string. I don't understand what conversion is necessary if I am sending all the values as the types referenced. Then it fails on the inner function SQL.get(INT32) what is it getting an integer for. I wish the exception just could provide more details this makes it impossible to just insert a record.

[Jdsurgeon]

Has anybody else ran across this issue yet.