ASP, VB code - joining table issue for google product feed

[London Muscle]

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]

Delete Sql query clause

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