...

View Full Version : ASP, VB code - joining table issue for google product feed



London Muscle
08-25-2011, 10:24 PM
Hi All,

I'm a beginner when it comes to all this and im just getting by at the moment after trying to fix problems left for me by a slap dash web design company who will remain un named for the moment.

I have a product feed which is created by an aspx page which writes an XML file. I have it in 2 forms right now the first works fine and shows all my items but I need to add some new fields and join a 3rd table to the query and thats where it goes wrong.

please see the code:

Dim adp As New SqlDataAdapter("Select ps.sizename, b.brandname, p.* from products p left Join Brands b on p.brandid=b.id join productsize ps on ps.pid = p.pid where p.active = 1", ConfigurationManager.AppSettings("Connection"))
Dim ds As New Data.DataSet
adp.Fill(ds)
Dim dv As Data.DataView = ds.Tables(0).DefaultView
For Each dr As Data.DataRowView In dv
objXml.WriteString(Environment.NewLine)
objXml.WriteStartElement("item")
objXml.WriteString(Environment.NewLine)
'Mandatory Attributes
objXml.WriteElementString("g:id", dr("pid"))
objXml.WriteString(Environment.NewLine)
objXml.WriteElementString("title", HtmlEntities(dr("pname")))
objXml.WriteString(Environment.NewLine)
objXml.WriteElementString("link", "http://www.londonmuscle.co.uk/" & "pages/Productdetails.aspx?" & "pid=" & dr("Pid") & "+&bid=" & dr("brandid") & "+&sid=" & dr("sizename"))
objXml.WriteString(Environment.NewLine)
objXml.WriteElementString("g:price", dr("pprice").ToString() + " GBP")
objXml.WriteString(Environment.NewLine)
objXml.WriteElementString("description", HtmlEntities(dr("pshort")))
objXml.WriteString(Environment.NewLine)
objXml.WriteElementString("g:condition", "new")
objXml.WriteString(Environment.NewLine)

This code works and creates an XML File but now the end product, The XML File now only shows items that have size entries in the "productsize" table.

What i was trying to do is show all items even those without size variations.

Does anyone have any suggestions. I know the key is in the sqladapter statement but I cant work it out :-(

dotnetmind
08-26-2011, 12:27 AM
Based on what I understood from you post you just need to delete the clause where.

I mean to use the follwoing:


Dim adp As New SqlDataAdapter("Select ps.sizename, b.brandname, p.* from products p left Join Brands b on p.brandid=b.id join productsize ps on ps.pid = p.pid ", ConfigurationManager.AppSettings("Connection"))

If I'm wrong, correct me by posting your tables' fields



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum