Link to home
Start Free TrialLog in
Avatar of mascoloj
mascolojFlag for United States of America

asked on

VB/ASP.Net Collection with a For Each loop

I am trying to figure out how to use a For Each loop with a collection to access the information within. Currently when the code runs I get an error at the beginning of the For Each loop which states "Specified cast is not valid". All items in the collection are Strings though so the error is puzzling me.

Below is a sample of my code with some comments. The error is in the dbsql class (second code window below). Any help on this issue would be great.

Public Structure SQL_Elements
        Dim FieldName As String
        Dim FieldValue As String
        Dim FieldType As String
End Structure

Public SQLCollection As Collection

Protected Sub btnSave1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave1.Click

SQLCollection = New Collection() ' Creates a new Collection to store the SQL elements in
Dim Temp As SQL_Elements 'Creates a new empty structure to place the SQL elements in

        Temp.FieldName = "OtherProgramInvolvment"
        Temp.FieldValue = txtOtherProgramInvolvment.Text
        Temp.FieldType = "SQLDbType.nvarchar, (MAX)"

dbsql.sqlUpdate("TblReferral_Community", SQLCollection, "ID='" & Request.QueryString("RID") & "'")
End Sub

Open in new window

dbsql Class
Public Shared Sub sqlUpdate(ByVal SQLTable As String, ByVal SQLCollection As Collection, ByVal WhereClause As String)

 For Each Item In SQLCollection 'This line causes the error
            cmd.Parameters.Add(New SqlParameter("'" & "@" & Item.FieldName & "'", Item.FieldType))
            cmd.Parameters("'" & "@" & Item.FieldName & "'").Value = Item.FieldValue
            SetClauses = SetClauses & Item.FieldName & " = " & "@" & Item.FieldName
            If Not ItemCounting = SQLCollection.Count Then
                SetClauses = SetClauses & ","
            End If

End Sub

Open in new window

Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

I suspect that your FieldValue in the database is a numeric type, not a string type (ie. varchar, etc.).
Avatar of mascoloj


The field value is of type string though.

For Each Item As Object In SQLCollection 'This line causes the error
Your collection elements are not string, they are of type SQL_Elements which is a structure.

As wallycode pointed out, that will probably work, but usually its not advisable.
What would you advise then. At first i was going to try a multidimensional array but went for the collection instead. Any ideas are welcome.
You can use collection or List. Change the loop to

For Each Item As SQL_Elements In SQLCollection
I tried all the suggestions but either get the same error or an error stating no accessible 'New' can be called without a narrowing conversion. I'm now looking into other ways to get around this.
Avatar of Easwaran Paramasivam
Easwaran Paramasivam
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I ended up using a List as EaswaranP recommended and it worked perfectly. I have now implemented the code into all my pages for SQL inserts and updates. Great idea!

I checked your profile and it looks like you are a lot better than I am in C#.  I tried to help someone last week on a different question that nobody else was looking at.  I was able to get rid of the first error but am at a loss what to do next.  He has asked if anyone else can help in his post.  I asked him to post more info....but maybe you could take a look also?

I am currently an SQL developer with a lot of VB experience (VB3 - .Net) but been doing SQL and VBA only for the last three years.  RUSTY!

Anyway...I asked him to try to escalate the issue...but I honestly don't know how to do that you take a look at this one please?'t-have-a-primary-key.html?anchorAnswerId=38179855#a38179855

Thank you,