将DataAdapter结果用作新查询的字符串

I am in the process of creating a "Related Items" feature for an online store. I have a SQL query that will pull a list of ITEMs from a Database and display them on the page but I'm trying to rig up system where the list will change depending on a few variables.

我正在为在线商店创建“相关项目”功能。我有一个SQL查询,它将从数据库中提取ITEM列表并在页面上显示它们,但我正在尝试装配系统,其中列表将根据一些变量而改变。

The code is below and the part I'm having trouble with is getting a usable string out of Query1 to be used as the 'results' variale in query 2.

代码如下,我遇到问题的部分是从Query1中获取一个可用的字符串,用作查询2中的“结果”变量。

Public Shared Function GetExtraProducts(ByVal strAddOnCat As String) As DataSet
    Dim connect As New SqlConnection

    Dim Data1 As New DataSet
    Dim data2 As New DataSet
    connect.ConnectionString = "SERVER = SERVER-SQL01; Trusted_Connection=yes; DATABASE=GlobalPCSQL"
    connect.Open()

    Dim query1 As String = ""
    Dim query2 As String = ""
    query1 = "SELECT StockID FROM dbo.ADDONLISTS WHERE SubCategory = 'Acer-Desktops'"

    Dim command1 = New SqlDataAdapter(query1, connect)
    command1.Fill(Data1)

    If Data1.Tables(0).Rows.Count > 0 Then
        query1 = "SELECT StockID FROM dbo.ADDONLISTS WHERE SubCategory = 'Generic'"
        Dim command3 = New SqlDataAdapter(query1, connect)
        command3.Fill(Data1, "StockID")
    End If

    Dim results As String = ""
    For Each row In Data1.Tables(0).Rows

        results += row.ToString() + "','"
    Next
    If results.Length > 2 Then
        results = results.Substring(0, results.Length - 2)
    End If




    'results = "'HD12047' , 'TV12008'"

    query2 = "SELECT stock_items.Stockcode, STOCK_GROUPS.XW_URL as stockgroup, STOCK_GROUP2S.XW_URL as stockgroup2, STOCK_MAINGROUP.XW_URL as stockmaingroup, stock_items.Stockcode as pID, stock_items.description as pName, stock_web.sales_html as pdescription, stock_web.picture_url as pImage, stock_web.picture_url as pLargeimage, stock_items.sellprice1 as pPrice, stock_items.SELLPRICE1, stock_items.SELLPRICE2, stock_items.SELLPRICE3, stock_items.SELLPRICE4, stock_items.SELLPRICE5, stock_items.SELLPRICE6, stock_items.SELLPRICE7, stock_items.SELLPRICE8, stock_items.SELLPRICE9, stock_items.status as itemtype, stock_items.SELLPRICE10 as pListPrice, stock_items.x_totalstock as pInStock, stock_items.x_webhits as pHits, stock_items.ISACTIVE, stock_items.WEB_SHOW, stock_items.X_WebBlub as X_WebBlurb, stock_items.x_webpromo as X_PROMOPAGE, stock_items.last_updated as lastupdated, stock_items.x_stockleveloverride, isnull(stock_items.Restricted_item,'N') as Restricted_item "
    query2 += "FROM stock_items Left OUTER Join STOCK_WEB ON (stock_items.Stockcode = STOCK_WEB.Stockcode) LEFT OUTER JOIN STOCK_GROUPS ON (STOCK_GROUPS.GROUPNO = STOCK_ITEMS.STOCKGROUP) LEFT OUTER JOIN STOCK_GROUP2S ON (STOCK_GROUP2S.GROUPNO = STOCK_ITEMS.STOCKGROUP2) LEFT OUTER JOIN STOCK_MAINGROUP ON (STOCK_MAINGROUP.GROUPNO = STOCK_GROUPS.XW_MAINGROUP)"
    query2 += "WHERE stock_items.ISACTIVE='Y'  AND stock_web.picture_url IS NOT NULL "
    query2 += "AND stock_items.Stockcode IN ('" + results + "')"


    query2 += results


    Dim command2 = New SqlDataAdapter(query2, connect)
    command2.Fill(data2)
    connect.Close()


    Return data2

End Function

Everything works fine if I spoon feed the stock Id numbers into query 2 (in place of the ' + results + ' section, but when I try to use the string from query one all I get is

如果我把库存Id数字输入查询2(代替'+ results +'部分,但是当我尝试使用查询中的字符串时,我得到的全部内容都可以正常工作

Incorrect syntax near 'System.Data.DataRow'

Which makes me think that even if I got the syntax sorted, it'l only search for System.Data.Datarow instead of the actual value of the field.

这让我觉得即使我对语法进行了排序,它也只搜索System.Data.Datarow而不是字段的实际值。

Any help would be appreciated.

任何帮助,将不胜感激。

1 个解决方案

#1

A DataRow can contain many DataColumns. Even though your return DataRow contains only one DataColumn, you must still specify the DataColumn:

DataRow可以包含许多DataColumns。即使返回的DataRow只包含一个DataColumn,您仍必须指定DataColumn:

Dim results As String = ""
For Each row In Data1.Tables(0).Rows

    results += row.Item(0).ToString() + "','" '<--- Added Item(0)'
Next
If results.Length > 2 Then
    results = results.Substring(0, results.Length - 2)
End If

Also, double-check that you have an apostrophe for the first result. You might need:

另外,仔细检查第一个结果是否有撇号。您可能需要:

Dim results As String = "'"

Finally, on an unrelated note, while the + operator can be used for string concatenation, I would recommend you use the & operator instead and only use + for numerical addition:

最后,在一个不相关的注释,虽然+运算符可以用于字符串连接,我建议你使用&运算符,只使用+进行数字加法:

results &= row.Item(0).ToString() & "','" '<--- Added Item(0)'

#1