Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:
    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 :-(

  2. #2
    New Coder
    Join Date
    Aug 2011
    Posts
    50
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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:

    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 ", ConfigurationManager.AppSettings("Connection"))
    If I'm wrong, correct me by posting your tables' fields
    Don't forget to mark solution providing post as "Answered".
    It helps others to find correct solutions!


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •