CODEWITHSUNDEEP
.netvb.net
mlg74
mlg74

Reputation: 520

For Loop only shows last item

I have a for loop and it shows the last item. I would like get all items and use as parameters for stored proc. Is there a basic reason, why it would only display the last item?

Dim Flipper As String
For x As Integer = 0 To EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles().Count - 1
        Flipper = EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles(x).Name

    Next

Stored Proc:
Select * From myTable Inner Join myOtherTalbe ON myTable.Id = myOtherTable.Id Where RoleId = @RoleId

VB CodeBehind:
Dim strConnection As String = "myDataSource"
        Dim con As New SqlConnection(strConnection)
        Dim cmd As New SqlCommand("mainForumA", con)
        cmd.CommandType = CommandType.StoredProcedure
        'params here if needed
        cmd.Parameters.Add(New SqlParameter("@RoleId", Flippers))
        con.Open()
        Dim ds = New DataSet()
        Dim da = New SqlDataAdapter(cmd)
        da.Fill(ds)

        cdcatalog.DataSource = ds
        cdcatalog.DataBind()
        con.Close()

That will be fine if I only have one Role, but if there are multiple Roles, I need:

Select * From myTable Inner Join myOtherTalbe ON myTable.Id = myOtherTable.Id Where RoleId = @RoleId1 Or RoleId = @RoleId2, etc

Upvotes: 0

Views: 177

Answers (2)

OneFineDay
OneFineDay

Reputation: 9024

You need a comma separated string and use the In operator in the sql text. 

Dim Flippers As New List(Of String)
For x As Integer = 0 To EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles().Count - 1
    Flippers.Add(EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles(x).Name)
Next
Dim values As String = String.Join(Flippers.ToArray, ",") 

Dim sqlText = "Select * From table Where Role In (" & values & ")"

Upvotes: 1

FernandoZ
FernandoZ

Reputation: 438

As the reply above said, you are not doing anything with Flipper. You can try adding the names to a list and then binding that. Also I would store the CustomerRoles in a list instead of resolving IWorkContext each time. It will help you with readability and possibly performance.

 Dim Flipper As String
    Dim names As New List(Of String)
    For x As Integer = 0 To EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles().Count - 1
            Flipper = EngineContext.Current.Resolve(Of IWorkContext)().CurrentCustomer.CustomerRoles(x).Name
            names.Add(Flipper)
        Next

    ListView1.DataSource = names
    ListView1.DataBind()

Upvotes: 0

Related Questions