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
    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
    •