将DataAdapter结果用作新查询的字符串
将DataAdapter结果用作新查询的字符串
提问于 2015-05-08 04:56:24
我正在为一家网上商店创建一个“相关商品”功能。我有一个SQL查询，它将从数据库中提取一个项目列表，并在页面上显示它们，但我正在尝试配置系统，其中列表将根据几个变量进行更改。
代码如下所示，我遇到问题的部分是从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如果我将股票Id号输入查询2(而不是“+ results +”部分)，那么一切都很好，但是当我尝试使用查询1中的字符串时，我得到的只是
Incorrect syntax near 'System.Data.DataRow'这使我认为，即使对语法进行排序，它也只是搜索System.Data.Datarow，而不是字段的实际值。
任何帮助都将不胜感激。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-05-08 05:01:44
DataRow可以包含许多DataColumn。即使您的返回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另外，再次检查第一个结果是否有撇号。你可能需要：
Dim results As String = "'"最后，对于不相关的注释，虽然+运算符可以用于字符串连接，但我建议您使用&运算符，而只使用+作为数值加法：
results &= row.Item(0).ToString() & "','" '<--- Added Item(0)'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30116057
复制相关文章
相似问题
腾讯云开发者
