Unable to cast object error
Unable to cast object error
Unable to cast object error
Unable to cast object error
Unable to cast object error
Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error
Unable to cast object error Unable to cast object error
Unable to cast object error
Go Back  Xtreme Visual Basic Talk > > > Unable to cast object error


Reply
 
Thread Tools Display Modes
  #1  
Old 10-07-2008, 07:39 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Question Unable to cast object error


hello guys!

i am trying to create an sql parameter but i am having an error. it gives me an error like this: Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. on all of this line

Dim paramcollection As New List(Of SqlParameter) ' no error on this part but the lines below gives me an error:
paramcollection.Add(New SqlParameter("@CGLCode", SqlDbType.VarChar).Value = CGLCode)
paramcollection.Add(New SqlParameter("@CGLDesc", SqlDbType.VarChar).Value = CGLDesc)
paramcollection.Add(New SqlParameter("@CSLCode", SqlDbType.VarChar).Value = CSLCode)
paramcollection.Add(New SqlParameter("@CSLDesc", SqlDbType.VarChar).Value = CSLDesc)
paramcollection.Add(New SqlParameter("@NGLCode", SqlDbType.VarChar).Value = NGLCode)
paramcollection.Add(New SqlParameter("@NGLDesc", SqlDbType.VarChar).Value = NGLDesc)
paramcollection.Add(New SqlParameter("@NSLCode", SqlDbType.VarChar).Value = NSLCode)
paramcollection.Add(New SqlParameter("@NSLDesc", SqlDbType.VarChar).Value = NSLDesc)
paramcollection.Add(New SqlParameter("@StartPosition", SqlDbType.SmallInt).Value = StartPosition)
paramcollection.Add(New SqlParameter("@AccountType", SqlDbType.VarChar).Value = AccountType)

here's the code
Code:
Private Sub buttImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttImport.Click Dim i As Integer Dim CGLCode As String, CGLDesc As String, CSLCode As String, CSLDesc As String, NGLCode As String, NGLDesc As String, NSLCode As String, NSLDesc As String, StartPosition As Integer, AccountType As String For i = 0 To ds.Tables("LedgerCode").Rows.Count - 1 'Get Dataset (ds) Field Values CGLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CGLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("CGLCode")) CGLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CGLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("CGLDesc")) CSLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CSLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("CSLCode")) CSLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CSLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("CSLDesc")) NGLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NGLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("NGLCode")) NGLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NGLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("NGLCode")) NSLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NSLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("NSLCode")) NSLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NSLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("NSLCode")) StartPosition = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("StartPosition")), 0, ds.Tables("LedgerCode").Rows(i).Item("StartPosition")) AccountType = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("AccountType")), "", ds.Tables("LedgerCode").Rows(i).Item("AccountType")) 'Create an array of SQLParameter Dim paramcollection As New List(Of SqlParameter) paramcollection.Add(New SqlParameter("@CGLCode", SqlDbType.VarChar).Value = CGLCode) paramcollection.Add(New SqlParameter("@CGLDesc", SqlDbType.VarChar).Value = CGLDesc) paramcollection.Add(New SqlParameter("@CSLCode", SqlDbType.VarChar).Value = CSLCode) paramcollection.Add(New SqlParameter("@CSLDesc", SqlDbType.VarChar).Value = CSLDesc) paramcollection.Add(New SqlParameter("@NGLCode", SqlDbType.VarChar).Value = NGLCode) paramcollection.Add(New SqlParameter("@NGLDesc", SqlDbType.VarChar).Value = NGLDesc) paramcollection.Add(New SqlParameter("@NSLCode", SqlDbType.VarChar).Value = NSLCode) paramcollection.Add(New SqlParameter("@NSLDesc", SqlDbType.VarChar).Value = NSLDesc) paramcollection.Add(New SqlParameter("@StartPosition", SqlDbType.SmallInt).Value = StartPosition) paramcollection.Add(New SqlParameter("@AccountType", SqlDbType.VarChar).Value = AccountType) Debug.Print(obj.Execute("INSERT INTO LedgerCode (CGLCode, CGLDesc, CSLCode, CSLDesc, NGLCode, NGLDesc, NSLCode, NSLDesc, StartPosition, AccountType) VALUES (@CGLCode, @CGLDesc, @CSLCode, @CSLDesc, @NGLCode, @NGLDesc, @NSLCode, @NSLDesc, @StartPosition, @AccountType)", paramcollection)) Next End Sub

what is wrong with my sqlparameter? i tried to add a SIZE on the parameter but it also does not work. what should i do to make a collection of parametercollection?

i hope that you can help me with my problem
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #2  
Old 10-07-2008, 08:16 AM
AtmaWeapon's Avatar
AtmaWeaponUnable to cast object error AtmaWeapon is offline
Fabulous Florist

Forum Leader
* Guru *
 
Join Date: Feb 2004
Location: Austin, TX
Posts: 9,500
Default

Here's what's happening, more or less:

You have three statements:
statement1 = paramcollection.Add(statement2)
statement2 = statement3.Value = CGLCode
statement3 = New SqlParameter("@CGLCode", SqlDbType.VarChar)

These evaluate from the inside out, so evaluation starts with statement3. This returns a new SqlParameter object. Now statement2 exposes the problem with overloading "=" to mean both assignment and an equality check. It looks like VB believes you are asking, "Does the value of this SqlParameter equal the same value as CGLCode?. VB beleives this because the call to Add in statement1 expects a value, but assignment does not return a value. The equality comparison returns a boolean value. This boolean value is used by statement1, which expects an object of type SqlParameter and fails.

You have four choices to fix this.
  1. You could use multiple lines to create the parameter, then set the value:
    Code:
    Dim param As New SqlParameter("@CGLCode", SqlDbType.VarChar)
    param.Value = CGLCode
    paramCollection.Add(param)
  2. You could use the constructor that takes a value:
    Code:
    paramCollection.Add(New SqlParameter("@CGLCode", CGLCode))
  3. You could use property initializers in VB .NET 2008:
    Code:
    paramCollection.Add(New SqlParameter("@CGLCode", SqlDbType.VarChar) With { .Value = CGLCode })
  4. As a variant of choice number 1, you could write a function to create the appropriate parameter:
    Code:
    Function CreateParameter(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object) As SqlParameter
        Dim param As New SqlParameter(name, type)
        param.Value = value
        Return param
    End Function
    
    ' ... later
    paramCollection.Add(CreateParameter("@CGLCode", SqlDbType.VarChar, CGLCode)

Method 1 has lots of repetition and breaks the DRY (Don't Repeat Yourself) principle; you should use method 4 instead of method 1. Method 2 reduces your control over the final type; if the computer makes the wrong guess you could get an unexpected error. Method 3 is a good version of Method 1, but will only work in VB .NET 2008. Method 4 is an improvement over Method 1 as well.

I'd suggest using Method 3 or Method 4.
__________________
.NET Resources
My FAQ threads | Tutor's Corner | Code Library
I would bet money 2/3 of .NET questions are already answered in one of these three places.
Reply With Quote
  #3  
Old 10-07-2008, 08:03 PM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Thumbs up works perfectly but another problem occurs

hello AtmaWeapon!

thanks for explaining to me what is happening why am i getting that kind of error. i am using vs 2005 and i implemented the code that you have gave me in option no 4.

here's the new buttImport courtesy of AtmaWeapon
Code:
Private Sub buttImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttImport.Click Dim i As Integer Dim CGLCode As String, CGLDesc As String, CSLCode As String, CSLDesc As String, NGLCode As String, NGLDesc As String, NSLCode As String, NSLDesc As String, StartPosition As Integer, AccountType As String For i = 0 To ds.Tables("LedgerCode").Rows.Count - 1 CGLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CGLCode")), "*", ds.Tables("LedgerCode").Rows(i).Item("CGLCode")) CGLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CGLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("CGLDesc")) CSLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CSLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("CSLCode")) CSLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("CSLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("CSLDesc")) NGLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NGLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("NGLCode")) NGLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NGLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("NGLCode")) NSLCode = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NSLCode")), "", ds.Tables("LedgerCode").Rows(i).Item("NSLCode")) NSLDesc = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("NSLDesc")), "", ds.Tables("LedgerCode").Rows(i).Item("NSLCode")) StartPosition = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("StartPosition")), 0, ds.Tables("LedgerCode").Rows(i).Item("StartPosition")) AccountType = IIf(IsDBNull(ds.Tables("LedgerCode").Rows(i).Item("AccountType")), "", ds.Tables("LedgerCode").Rows(i).Item("AccountType")) Dim paramcollection As New List(Of SqlParameter) paramcollection.Add(CreateParameter("@CGLCode", SqlDbType.VarChar, CGLCode)) paramcollection.Add(CreateParameter("@CGLDesc", SqlDbType.VarChar, CGLDesc)) paramcollection.Add(CreateParameter("@CSLCode", SqlDbType.VarChar, CSLCode)) paramcollection.Add(CreateParameter("@CSLDesc", SqlDbType.VarChar, CSLDesc)) paramcollection.Add(CreateParameter("@NGLCode", SqlDbType.VarChar, NGLCode)) paramcollection.Add(CreateParameter("@NGLDesc", SqlDbType.VarChar, NGLDesc)) paramcollection.Add(CreateParameter("@NSLCode", SqlDbType.VarChar, NSLCode)) paramcollection.Add(CreateParameter("@NSLDesc", SqlDbType.VarChar, NSLDesc)) paramcollection.Add(CreateParameter("@StartPosition", SqlDbType.SmallInt, StartPosition)) paramcollection.Add(CreateParameter("@AccountType", SqlDbType.VarChar, AccountType)) Debug.Print(obj.Execute("INSERT INTO LedgerCode (CGLCode, CGLDesc, CSLCode, CSLDesc, NGLCode, NGLDesc, NSLCode, NSLDesc, StartPosition, AccountType) VALUES (@CGLCode, @CGLDesc, @CSLCode, @CSLDesc, @NGLCode, @NGLDesc, @NSLCode, @NSLDesc, @StartPosition, @AccountType)", paramcollection)) Next End Sub

and here is the function in my module class. i also added a sDBNull and iDBNull to adapt DRY Principle. thanks for the advice master AtmaWeapon
Code:
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ' CreateParameter(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object) As SqlParameter ' |--=> Date Created : 10/08/2008 ' |--=> Creates new SQL parameter to avoid DRY Principle. AtmaWeapon of XtremeVBTalk.com coded this one =) '--------------------------------------------------------------------------------------- Public Function CreateParameter(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object) As SqlParameter Dim param As New SqlParameter(name, type) param.Value = value Return param End Function '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ' sDBNull(ByVal StringVariable As String, Optional ByVal DefaultValue As String = "") As String ' |--=> Date Created : 10/08/2008 ' |--=> Replaces the NULL Value with "" String '--------------------------------------------------------------------------------------- Public Function sDBNull(ByVal StringVariable As String, Optional ByVal DefaultValue As String = "") As String DefaultValue = IIf(DefaultValue <> "", DefaultValue, "") sDBNull = IIf(IsDBNull(StringVariable), DefaultValue, StringVariable) End Function '=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ' iDBNull(ByVal StringVariable As String, Optional ByVal DefaultValue As Integer = 0) As Integer ' |--=> Date Created : 10/08/2008 ' |--=> Replaces the NULL Value with 0 String '--------------------------------------------------------------------------------------- Public Function iDBNull(ByVal StringVariable As String, Optional ByVal DefaultValue As Integer = 0) As Integer DefaultValue = IIf(DefaultValue <> 0, DefaultValue, 0) iDBNull = IIf(IsDBNull(StringVariable), DefaultValue, StringVariable) End Function

and now... the code works well but my problem is the execution part in my class. i tested the class with simple sql statements without any parameters and it works perfectly. but now when i added a parameter in my sqlstatement, it gives me this kind of error.

ERROR: The SqlParameterCollection only accepts non-null SqlParameter type objects, not List`1 objects.

here is my cSQL class
Code:
Imports System Imports System.Data Imports System.Data.SqlClient Public Class SQL Public Overloads Function Execute(ByVal sqlStatement As String, ByVal cparam As List(Of SqlParameter)) As String Try Dim cm As New SqlCommand cm.Connection = cn 'Set a Connection cm.CommandText = sqlStatement 'Execute SQL Statement cm.Parameters.Add(cparam) cm.CommandType = CommandType.Text 'Refers that the command is SQL and not Stored Proc cm.ExecuteNonQuery() Execute = "OK" Catch ex As Exception Debug.Print(ex.Message) Execute = "ER" End Try End Function End Class

how can it be that the sqlparameter has a null value but i put a "" on it? is "" and null the same?
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #4  
Old 10-08-2008, 08:04 AM
AtmaWeapon's Avatar
AtmaWeaponUnable to cast object error AtmaWeapon is offline
Fabulous Florist

Forum Leader
* Guru *
 
Join Date: Feb 2004
Location: Austin, TX
Posts: 9,500
Default

It takes a little experience to piece this one together.

not List`1 objects
The "`1" notation is a .NET implementation detail that appears when you are using Generic classes. I believe the full name of List(Of SqlParameter) would appear as List`1SqlParameter in call stacks and other internals.

ByVal cparam As List(Of SqlParameter)
This is the only generic list that your function works with.

cm.Parameters.Add(cparam)
This is the only method that uses the generic list.

The error message indicates that the List type isn't supported by this method; let's look at the documentation. Parameters is of type SqlParameterCollection; here's the Add method overloads. Hmm, notice they are all singular, "Adds a SqlParameter..." The problem is the Add method was made to add only one SqlParameter at a time, and you are trying to add more than one.

Add(Object) seems to imply you can add anything, but if you look through the documentation you see that it only exists because it's defined on the base class DbParameterCollection; the designers should have used an interface rather than inheritance here, and it also shows why generic types were such a fantastic addition to the framework.

Anyway, it's very common for collection classes in .NET to implement two Add methods: Add for adding one object at a time and AddRange for adding multiple objects. SqlParameterCollection.AddRange is what you want. Note that it needs an array (yet another design error; it should have been IEnumerable), so you'll need to call the ToArray method of your list:

Try this instead of the Add call:
cm.Parameters.AddRange(cparam.ToArray())

Technically, it would be a better design decision for your cparam parameter to be either an array or IEnumerable instead of a list. IEnumerable(Of SqlParameter) is the most flexible, and it has a ToArray extension method in .NET 3.5. If you aren't using .NET 3.5, you might want to stick to just an array type since the interface to Add is cruddy.
__________________
.NET Resources
My FAQ threads | Tutor's Corner | Code Library
I would bet money 2/3 of .NET questions are already answered in one of these three places.
Reply With Quote
  #5  
Old 10-08-2008, 08:02 PM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Default whoe!

Thanks for the reply AtmaWeapon! =)

Thank you for the information that you have gave me. I almost gave up and resorted to non parameter SQL statements.

Im still using VS2005 and what i did was to put the addrange instead of Add just like you've said and sadly, i encountered another problem.

cm.Parameters.AddRange(cparam.ToArray())

i also put a size for the CreateParameter Statement because by default, the parameter is in the maximum value and im totally scared about that one.

Public Function CreateParameter(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object, ByVal SizeOfTheField As Integer) As SqlParameter

and im calling it like this:
paramcollection.Add(CreateParameter("@CGLCode", SqlDbType.VarChar, CGLCode, 10))

the error code now is this:
The parameterized query '(@CGLCode varchar(10),@CGLDesc varchar(150),@CSLCode varchar(10)' expects the parameter '@CGLCode', which was not supplied.
ER


now my nose starts to bleed!

you are right master AtmaWeapon... creating this miserable class is for a professional coder and not for newbie like me! hahaha!

i hope that you can help me again with my problem. thank you for your help and well detailed explanation. you rock
__________________
GREAT INVENTIONS would not be DONE without FUN

Last edited by monkeynote; 10-08-2008 at 08:04 PM. Reason: changed the error code
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Unable to cast object error
Unable to cast object error
Unable to cast object error Unable to cast object error
Unable to cast object error
Unable to cast object error
Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error Unable to cast object error
Unable to cast object error
Unable to cast object error
 
Unable to cast object error
Unable to cast object error
 
-->