mardi 3 avril 2018

SqlDataReader collecting one row value instead of all rows? Confused & new to SqlDataReaders

Two tables:

CART (CartID, UserID, DateCreated, ProductID, ProductName, Size, Price, Quantity, Subtotal, HoursWork)

PRODUCTS(ProductID, Name, SDescription, Price, Size, Images, Thumbnail, Weight, LDescription, Stock, CategoryID, HoursWork)

What I'm trying to do: Display one of two buttons depending on all of the categoryIDs selected by SELECT statement.

Category IDs range from 1-7. If any CategoryIDs selected match "1" "2" or "7", a 'Create Booking' button should be displayed, otherwise a 'Delivery Option' button should be displayed.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

       'Display the correct button:
    Dim conn As SqlConnection = New SqlConnection(ConnectionString)
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT products.CategoryID FROM Products INNER JOIN Cart on products.ProductID = cart.ProductID WHERE ([UserID] = @UserID) AND ([DateCreated] = CONVERT(date, GETDATE()))"

    Dim UserID As SqlParameter = New SqlParameter("@UserID", SqlDbType.NVarChar, 128)
    UserID.Value = "249f38b8-e745-44f4-8d78-806b0e5e7e0e"
    cmd.Parameters.Add(UserID)

    cmd.Connection = conn
    conn.Open()
    cmd.ExecuteNonQuery()

    Dim reader As SqlDataReader = cmd.ExecuteReader
    Dim CategoryID As Integer

    While reader.Read()
         'CategoryID = reader.GetValue(0)
    CategoryID = CType(reader("CategoryID"), Integer)
    End While
    conn.Close()

    lblTest.Text = CategoryID

    If CategoryID = "1" Or "2" Or "7" Then
            btnCreateBooking.Visible = True
        Else
            btnDeliveryOptions.Visible = True
        End If
End Sub

However, the CategoryID of the last product in the cart is produced by the SqlDataReader into the label/variable, and the button 'Create Booking' is only even displayed.

Aucun commentaire:

Enregistrer un commentaire