时间戳

首页 > 解决方案 > 从数据库-SQL/VB.Net 设置超链接和图像 URL

sql - 从数据库-SQL/VB.Net 设置超链接和图像 URL

问题描述

我有一个表，它总是只有 3 行，每周更新。我需要阅读它们并填充 3 个 ImageUrl 和 3 个关联的超链接。但是，我收到了错误：

System.IndexOutOfRangeException: hyperlink1

我可能离这里很远，因为我还在学习，但我们开始了..我只是用..来选择它们

SELECT 
    (SELECT hyperlink As hyperlink1 WHERE productNo=1),
    (SELECT imageUrl As imageUrl1 WHERE productNo=1),
    (SELECT hyperlink As hyperlink2 WHERE productNo=2),
    (SELECT imageUrl As imageUrl2 WHERE productNo=2),
    (SELECT hyperlink As hyperlink3  WHERE productNo=3),
    (SELECT imageUrl As imageUrl3 WHERE productNo=3)

FROM tblAmazonProducts 结束

然后是VB代码..

    Public Function GetAmazonProducts()
        Dim DBConnectAm As New DBConn
        Using db As DbConnection = DBConnectAm.Conn("DBConnectionString")
            Dim cmd As SqlCommand = DBConnectAm.Command(db, "SelectAmazonProducts")
            'cmd.Parameters.Add(New SqlParameter("productNo", SqlDbType.Int, ParameterDirection.Input)).Value = productNo
            db.Open()
            Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                hypAmazonProduct1.navigateurl=DR("hyperlink1")
                imghypAmazonProduct1.ImageUrl=DR("imageUrl1")
                hypAmazonProduct2.navigateurl=DR("hyperlink2")
                imghypAmazonProduct2.ImageUrl=DR("imageUrl2")
                hypAmazonProduct3.navigateurl=DR("hyperlink3")
                imghypAmazonProduct3.ImageUrl=DR("imageUrl3")
            DR.Close()
            DR = Nothing
            cmd.Dispose()
            cmd = Nothing
            db.Dispose()
            db.Close()
        End Using
   End Function

标签： sqlvb.net

解决方案

我将 Do While 放回原处，然后在每一行中调整 SELECT 语句，如下所示：

SELECT 
    (SELECT hyperlink FROM tblAmazonProducts WHERE productNo=1)As hyperlink1,
    (SELECT imageUrl FROM tblAmazonProducts WHERE productNo=1) As imageUrl1,
    (SELECT hyperlink FROM tblAmazonProducts WHERE productNo=2) As hyperlink2,
    (SELECT imageUrl FROM tblAmazonProducts WHERE productNo=2) As imageUrl2,
    (SELECT hyperlink FROM tblAmazonProducts  WHERE productNo=3) As hyperlink3,
    (SELECT imageUrl FROM tblAmazonProducts WHERE productNo=3) As imageUrl3

VB..

    Dim DBConnectAm As New DBConn
    Using db As DbConnection = DBConnectAm.Conn("DBConnectionString")
        Dim cmd As SqlCommand = DBConnectAm.Command(db, "SelectAmazonProducts")
        'cmd.Parameters.Add(New SqlParameter("productNo", SqlDbType.Int, ParameterDirection.Input)).Value = productNo
        db.Open()
        Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While DR.Read
            hypAmazonProduct1.navigateurl=DR("hyperlink1")
            imghypAmazonProduct1.ImageUrl=DR("imageUrl1")
            hypAmazonProduct2.navigateurl=DR("hyperlink2")
            imghypAmazonProduct2.ImageUrl=DR("imageUrl2")
            hypAmazonProduct3.navigateurl=DR("hyperlink3")
            imghypAmazonProduct3.ImageUrl=DR("imageUrl3")
        End While
        DR.Close()
        DR = Nothing
        cmd.Dispose()
        cmd = Nothing
        db.Dispose()
        db.Close()
    End Using

推荐阅读