[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LINQ to SQL delete throwing InvalidCastExpcetion was unhandled

Posted on 2012-08-20
49
Medium Priority
?
500 Views
Last Modified: 2012-09-04
I'm using VB 2010 with SQL express 2012 database.

I'm trying to delete a row from the Invoices table I have in my db, but i'm getting a  was unhandled error Specified cast is not valid.
        Dim deleteThisRow As Integer
        deleteThisRow = ds.Tables("Firstdb").Rows(inc).Item("InvoiceId")
        
        Dim deleteInvoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow).ToList()(0)

        database.Invoices.DeleteOnSubmit(deleteInvoice)
        database.SubmitChanges()

Open in new window

I'm not really sure what the problem is... I was thinking it might have been my variable for, which row I want deleted, but when I highlight it when it throws the error it says the correct row number I want deleted.
InvalidCastException
0
Comment
Question by:easyworks
  • 26
  • 8
  • 7
  • +2
49 Comments
 
LVL 4

Expert Comment

by:fritzfrancis
ID: 38311688
You need to add a Select clause that extracts the integer column that you want. The query you have right now is returning a list of invo objects not a list of integers.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38311706
I've already tried that and have gotten the same error. Doesnt VB automatically do a select at the end anyways?
Dim deleteInvoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow _
                            Select invo).ToList()(0)

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38311768
try something like this:

Dim deleteThisRow As Integer
        deleteThisRow = ds.Tables("Firstdb").Rows(inc).Item("InvoiceId")
        Dim inv= (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow)
        Dim deleteInvoice = inv.ToList()(0)

        database.Invoices.DeleteOnSubmit(deleteInvoice)
        database.SubmitChanges() 

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 4

Expert Comment

by:fritzfrancis
ID: 38311776
I am sorry you are correct. I mis-read the program. Silly question but is InvoiceId a string or an integer? If the exception is happening on that line then maybe the types in the Where clause is different. Other than that you can remove the indexer after the ToList() method and see what type you are receiving.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38311803
YZlat it errored out on with the UnhandledCastException so does this mean it is because it doesn't know which item to take in the list even though there is only 1 item?
Dim deleteInvoice = inv.ToList()(0)

Open in new window



fritzfrancis the InvoiceId is a integer. Also if i delete out the 0 after the ToList

database.Invoices.DeleteOnSubmit(deleteInvoice) this line comes up with a issue

Error      1      Value of type 'System.Collections.Generic.List(Of MyFirstDB.Invoice)' cannot be converted to 'MyFirstDB.Invoice'.      C:\SkyDrive\VB\Projects\MyFirstDB\MyFirstDB\frmSecond.vb      131      42      MyFirstDB
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38311962
what if you try

Dim deleteInvoice = inv.First()
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38311992
or this

Dim deleteThisRow As Integer
        deleteThisRow = ds.Tables("Firstdb").Rows(inc).Item("InvoiceId")
        
        Dim deleteInvoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow)

        database.Invoices.DeleteOnSubmit(deleteInvoice)
        database.SubmitChanges() 

Open in new window

0
 
LVL 1

Author Comment

by:easyworks
ID: 38311999
I get the same error UnhandledCastException on this line now.
Dim deleteInvoice = inv.First()

Open in new window

0
 
LVL 1

Author Comment

by:easyworks
ID: 38312058
That code creates an invalidcastexception was unhandled on the deleteonsubmit line.
Unable to cast object of type 'System.Data.Linq.DataQuery`1[MyFirstDB.Invoice]' to type 'MyFirstDB.Invoice'.
0
 
LVL 4

Expert Comment

by:fritzfrancis
ID: 38312319
Try just accessing that entity by itself. For example, just get a count of records in that table.

Dim count as Intger = database.Invoices.Count()

If that fails then you might have to regenerate your model.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312452
Dim count as Intger = database.Invoices.Count() 
MsgBox(count)

Open in new window


The message box pops the # of the rows that is in the database right now, which is 10.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38312596
Try

Dim deleteInvoice As Invoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow).First()
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312699
I've tried that
Dim deleteInvoice As Invoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow).*
where * is...
First
FirstOrDefault
ToList
Single
SingleOrDefault
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312727
Just to show everyone for sure that the InvoiceId is a INT data type.

invoiceid datatype
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312752
I looked at MSDN for some reference and i see they got.
http://msdn.microsoft.com/en-us/library/bb907191.aspx
Private Sub DeleteButton_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs
                              ) Handles DeleteButton.Click
  Dim deleteCust = (From cust In db.Customers 
                    Where cust.CustomerID = "JILLF").ToList()(0)

  db.Customers.DeleteOnSubmit(deleteCust)

  Try
    db.SubmitChanges()
  Catch
    ' Handle exception.
  End Try

  RefreshData()
End Sub

Open in new window

Besides for the "deleteThisRow variable there is nothign different... I guess i can try to MANUALLY 1x to plug in a hardcoded #.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312760
Even when i manually put it to  (I just realized doing this was pointless b/c i posted above how it already knew that the variable deleteThisRow was showing 14)
Dim deleteInvoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = 14 _
                            Select invo).ToList()(0)

Open in new window

I still get InvalidCastExpcetion was unhandled
0
 
LVL 4

Expert Comment

by:fritzfrancis
ID: 38312791
Try this code:

Dim query As IQueryable(Of Invoice) = database.Invoices.Where(Function(r) r.InvoiceId)
Dim invoice As Invoice

For Each item As Invoice in query
invoice = item
Exit
Next

In the example of above, I am not using ToList() to execute the query. I am letting the LINQ-to-SQL use its deferred execution in the for loop. I am also using the method syntax to generate the SQL instead of the query syntax. (However that should have an impact on the outcome).
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312812
Sorry my brain isn't following the code at the moment. I've copied and pasted everything you have there and commented out all my code. Where/How am i telling VB that i want to delete this Row? I had to remove Exit from your statement b/c it was like for a sub or something else under it.
0
 
LVL 4

Expert Comment

by:fritzfrancis
ID: 38312856
Sorry I meant Exit For.

The idea is to see if you can get results back from that query.
After you get an instance inside the invoice variable, you can invoke the DeleteOnSubmit(invoice).
0
 
LVL 1

Author Comment

by:easyworks
ID: 38312878
Sorry i'm just not following what I'm supposed to be doing just have a couple other things going on as well phone wont stop ringing :(.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38313561
>I've tried that  http:#a38312699

and the outcome was?
0
 
LVL 1

Author Comment

by:easyworks
ID: 38313579
My bad it came out with the same error about InvalidCastExpcetion was unhandled.
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314318
Just to show everyone for sure that the InvoiceId is a INT data type
Check out InvoiceID type in *.dbml
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314505
Says int too.
<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_InvoiceId", AutoSync:=AutoSync.OnInsert, DbType:="Int NOT NULL IDENTITY", IsPrimaryKey:=true, IsDbGenerated:=true)>  _
	Public Property InvoiceId() As Integer
		Get
			Return Me._InvoiceId
		End Get
		Set
			If ((Me._InvoiceId = value)  _
						= false) Then
				Me.OnInvoiceIdChanging(value)
				Me.SendPropertyChanging
				Me._InvoiceId = value
				Me.SendPropertyChanged("InvoiceId")
				Me.OnInvoiceIdChanged
			End If
		End Set
	End Property

Open in new window

0
 
LVL 28

Expert Comment

by:Ark
ID: 38314524
Does following string produce same exception?
MsgBox((From invo In database.Invoices Where invo.InvoiceId = deleteThisRow).Count)
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314548
Not sure, but you can try lambda instead of direct WHERE:

From invo In database.Invoices Where (Function(x) x.InvoiceId = deleteThisRow)
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314566
Ark this code displays 1.
MsgBox((From invo In database.Invoices Where invo.InvoiceId = deleteThisRow).Count)

Open in new window


I'm not sure about From invo In database.Invoices Where (Function(x) x.InvoiceId = deleteThisRow b/c im not sure what X is ive tried invo, but it still complains about being converted to a boolean.
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314578
this code displays 1
Good news - it's IEnumerable :)
Now try
Dim a = (From invo In database.Invoices _
              Where invo.InvoiceId = deleteThisRow)
Dim b = a(0)
Debug.Print("---")'<= Place a break point here and look on a Type of a and b
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314595
PS As for lamda function - x is just a variable which receive items from IENumarable, like an alias for each invo item. You can use any name, like:
Where (Function(y) y.InvoiceId = AnyExpressionHere
Where (Function(z) z.InvoiceId = AnyExpressionHere
Where (Function(myLambda) myLambda.InvoiceId =...

It is usefull for complex checking:
Private function IsInvoiceOK(inv as database.Invoice) As Boolean
   'Check all or some fields here, calling other functions if need
   'Return True or false
End Function

'And call from LINQ
...Where (Function(x) IsInvoiceOK(x))
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314698
When I try this i get an error Dim b = a(0) NotSupportedException was unhandled; The query operator 'ElementAtOrDefault' is not supported.
Dim a = (From invo In database.Invoices _
              Where invo.InvoiceId = deleteThisRow)
Dim b = a(0)
Debug.Print("--"))

Open in new window

When I highlight A & B
a|NotSupportedException
b|Nothing
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314730
Now try
Dim a = (From invo In database.Invoices _
              Where invo.InvoiceId = deleteThisRow).ToList
Dim b = a(0)
Debug.Print("--"))
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314740
It throws the error now (InvalidCastExpcetion was unhandled) on
Dim a = (From invo In database.Invoices _
              Where invo.InvoiceId = deleteThisRow).ToList

Open in new window

says a|Nothing
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314756
OK, we narrow the error. What is exception details? "Cannot casr from ??? to ???"
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314764
The underscore red mark stop on the .ToList
InvalidCastExpcetion
0
 
LVL 28

Expert Comment

by:Ark
ID: 38314773
What is DataException in "Details"?
0
 
LVL 1

Author Comment

by:easyworks
ID: 38314815
Ill have to get back to you tomorrow.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38316214
Is this what you wanted?
viewDetail
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38328447
Do you have any integer type properties on invoice object which have null values for this invoice in the database? I usually had this issue when DB had a null value for an Integer/Double/... property of the class.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38329088
This is in the Design for the table in SQL Server Management Studio
DesignInvoices
This is the data that is on the invoice tables.
DataOnTables
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38331539
Could it be the date column? What is the type of property in class?

Does the invoice load correctly elsewhere?
0
 
LVL 1

Author Comment

by:easyworks
ID: 38331642
Date column is using data type: date.
I'm using linqtosql class so its a *.dbml, but here is a cut and paste of the storage type for column. (I used theDate as the column because i know date is a reservered word)
<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_theDate", DbType:="Date")>  _
	Public Property theDate() As System.Nullable(Of Date)
		Get
			Return Me._theDate
		End Get
		Set
			If (Me._theDate.Equals(value) = false) Then
				Me.OntheDateChanging(value)
				Me.SendPropertyChanging
				Me._theDate = value
				Me.SendPropertyChanged("theDate")
				Me.OntheDateChanged
			End If
		End Set
	End Property

Open in new window


WHen i try to just return a select statement it also gives me a invalid exception.
Dim deleteThisRow As Integer
        deleteThisRow = 14
        Dim deleteInvoice = (From invo In database.Invoices _
                            Where invo.InvoiceId = deleteThisRow).FirstOrDefault

        cbOrgId.SelectedValue = deleteInvoice.OrgId
        cbClientId.SelectedValue = deleteInvoice.ClientId
        cbTechId.SelectedValue = deleteInvoice.TechId
        dtpDate.Value = deleteInvoice.theDate
        txtNotes.Text = deleteInvoice.Notes
        txtParts.Text = deleteInvoice.Parts
        txtLabor.Text = deleteInvoice.Labor
        txtMileage.Text = deleteInvoice.Mileage
        txtTotalCost.Text = deleteInvoice.TotalCost
        txtInvoiceNumber.Text = deleteInvoice.InvoiceNumber

Open in new window

0
 
LVL 1

Author Comment

by:easyworks
ID: 38336408
Was worried about maybe some type of conversion problem so I created some mappings.
I created a module and put this in it. Also at the very top I did.

Imports System.Data.Linq.Mapping
Public Class lqdc
        Inherits DataContext
        Public invoices As Table(Of Invoice)

        Public Sub New(ByVal connection As String)
            MyBase.new(Connection)
        End Sub

  <Table(Name:="Invoices")> Public Class Invoice
        Private _InvoiceId As Integer
        Private _OrgId As Integer
        Private _ClientId As Integer
        Private _TechId As Integer
        Private _Date As Date
        Private _Notes As String
        Private _Parts As String
        Private _Labor As Double
        Private _Mileage As Integer
        Private _TotalCost As Double
        Private _InvoiceNumber As Integer

        <Column(Storage:="_InvoiceId", DbType:="Int NOT NULL IDENTITY", _
            IsPrimaryKey:=True, IsDBGenerated:=False)> _
        Public ReadOnly Property InvoiceId() As Integer
            Get
                Return Me._InvoiceId
            End Get
        End Property

        <Column(Storage:="_OrgId", DbType:="Int")> Public Property OrgId() As Integer
            Get
                Return Me._OrgId
            End Get
            Set(ByVal value As Integer)
                Me._OrgId = value
            End Set
        End Property

        <Column(Storage:="_ClientId", DbType:="Int")> Public Property ClientId() As Integer
            Get
                Return Me._ClientId
            End Get
            Set(ByVal value As Integer)
                Me._ClientId = value
            End Set
        End Property

        <Column(Storage:="_TechId", DbType:="tinyint")> Public Property TechId() As Integer
            Get
                Return Me._TechId
            End Get
            Set(ByVal value As Integer)
                Me._TechId = value
            End Set
        End Property

        <Column(Storage:="_Date", DbType:="Date")> Public Property theDate() As Date
            Get
                Return Me._Date
            End Get
            Set(ByVal value As Date)
                Me._Date = value
            End Set
        End Property

        <Column(Storage:="_Notes", DbType:="varchar(max)")> Public Property Notes() As String
            Get
                Return Me._Notes
            End Get
            Set(ByVal value As String)
                Me._Notes = value
            End Set
        End Property

        <Column(Storage:="_Parts", DbType:="varchar(100)")> Public Property Parts() As String
            Get
                Return Me._Parts
            End Get
            Set(ByVal value As String)
                Me._Parts = value
            End Set
        End Property

        <Column(Storage:="_Labor", DbType:="real")> Public Property Labor() As Double
            Get
                Return Me._Labor
            End Get
            Set(ByVal value As Double)
                Me._Labor = value
            End Set
        End Property

        <Column(Storage:="_Mileage", DbType:="tinyint")> Public Property Mileage() As Integer
            Get
                Return Me._Mileage
            End Get
            Set(ByVal value As Integer)
                Me._Mileage = value
            End Set
        End Property

        <Column(Storage:="_TotalCost", DbType:="smallmoney")> Public Property TotalCost() As Double
            Get
                Return Me._TotalCost
            End Get
            Set(ByVal value As Double)
                Me._TotalCost = value
            End Set
        End Property

        <Column(Storage:="_InvoiceNumber", DbType:="smallint")> Public Property InvoiceNumber() As Integer
            Get
                Return Me._InvoiceNumber
            End Get
            Set(ByVal value As Integer)
                Me._InvoiceNumber = value
            End Set
        End Property

    End Class
    End Class

Open in new window


Now I'm getting a slightly different error but still invalid cast exception. Both errors look really close to 1 in the same.
NewError
0
 
LVL 1

Author Comment

by:easyworks
ID: 38336980
I can add records to my database doing...
Dim AddInvoice As New Invoice
        AddInvoice.OrgId = cbOrgId.SelectedValue
        AddInvoice.ClientId = cbClientId.SelectedValue
        AddInvoice.TechId = cbTechId.SelectedValue
        AddInvoice.theDate = dtpDate.Value
        AddInvoice.Notes = txtNotes.Text
        AddInvoice.Parts = txtParts.Text
        AddInvoice.Labor = txtLabor.Text
        AddInvoice.Mileage = txtMileage.Text
        AddInvoice.TotalCost = txtTotalCost.Text
        AddInvoice.InvoiceNumber = txtInvoiceNumber.Text

        database.invoices.InsertOnSubmit(AddInvoice)
        database.SubmitChanges()

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38341568
Does it work if you temporarily remove the date column from the class? Or may be add another entity to the same table but do not add date column and test that.
0
 
LVL 1

Author Comment

by:easyworks
ID: 38342504
This test is just to make sure I have the basics working and if this works I will reward points (Really at this point if i can make any LINQ to SQL statement work being an update or delete).

Ok, I just made a test button to just delete a row in a simple table that is my addresses. I do not get any errors it just doesn't seem to do anything. Whenever i try to delete a different address being used by multiple tables i get
The DELETE statement conflicted with the REFERENCE constraint "FK_Clients_Addresses". The conflict occurred in database "C:\SKYDRIVE\VB\PROJECTS\MYFIRSTDB\MYFIRSTDB\BIN\DEBUG\FIRSTDB.MDF", table "dbo.Clients", column 'AddressId'.
The statement has been terminated.

        Dim deleteAddress = (From addr In database.Addresses _
                            Where addr.AddressId = 4).ToList()(0)
        database.Addresses.DeleteOnSubmit(deleteAddress)
        database.SubmitChanges()

Open in new window


Colum Name - Data Type - (None of the boxes are checked for allow nulls.)
AddressId - int
Address - varchar(50)
City - varchar(20)
State - char(2)
Zip - char(5)
Pic of dataTypes
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38344784
>Whenever i try to delete a different address being used by multiple tables i get

That error makes sense doesn't it? This is for referential integrity. You can change your constraints to cascade deletes if you want (client linked to this address will also be deleted!)
0
 
LVL 1

Author Comment

by:easyworks
ID: 38345159
I'm not really trying to delete address used by multiple tables. I was just pointing out that I was trying to delete a row and that it didn't give me an error or DELETE the row I wanted, but when I tried to delete a row that was used in multiple tables it gave me that error. I'm not sure why LINQ is giving me so much trouble :( its so much easier for me to do actions using SqlCommand its just that I hate looking for problems with my SQL statements when stuff is not working right. I'm able to delete rows using.

Dim deleteThisRow As Integer
deleteThisRow = ds.Tables("Firstdb").Rows(inc).Item("InvoiceId")
Dim deleteInvoice As String = "DELETE from Invoices WHERE InvoiceId = " & deleteThisRow.ToString

con.Open()
Dim cmd = New SqlCommand(deleteInvoice, con)
cmd.ExecuteNonQuery()
con.Close()

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38345509
LINQ is very easy to use once its setup correctly.

This will help you

http://msdn.microsoft.com/en-us/library/bb386947.aspx
0
 
LVL 1

Author Closing Comment

by:easyworks
ID: 38363340
Turned out to be an issue with *.dbml and that just creating new mappings resolved the issue.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question