Avatar of mascoloj
mascoloj
Flag for United States of America asked on

VB/ASP.Net Collection with a For Each loop

Hello,
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)"
        SQLCollection.Add(Temp)

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
 Next

End Sub

Open in new window

.NET ProgrammingVisual Basic.NETASP.NET

Avatar of undefined
Last Comment
WallyCode

8/22/2022 - Mon
David L. Hansen

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

ASKER
The field value is of type string though.
WallyCode

Try...

For Each Item As Object In SQLCollection 'This line causes the error
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pra4444

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.
mascoloj

ASKER
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.
Nasir Razzaq

You can use collection or List. Change the loop to

For Each Item As SQL_Elements In SQLCollection
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mascoloj

ASKER
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.
ASKER CERTIFIED SOLUTION
Easwaran Paramasivam

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mascoloj

ASKER
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!
WallyCode

EaswaranP,

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 myself...so...could you take a look at this one please?

https://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27786772.html#a38179855

Thank you,

Wally
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck