none
HELP... Need Work Around for FULL OUTER JOIN TSQL using CE???

    Question

  •  

    Hey all,

     

    Anyone have a work around for using a TSQL FULL OUTER JOIN in CE? Something like a Right and a Left Outer Join for the below?

     

    Thanks,

    billb

     

    Here's an example:

     

    ' Connection To SQL CE

    Dim ConsqlShipTo As New SqlCeConnection

    ConsqlShipTo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

    '

    'TSQL STATEMENT......

    Dim reqSQL As New SqlCeCommand

    reqSQL.CommandText = "SELECT " & _

    " COALESCE(ShipTo.ShipToCust, Prospect.ProspectCust) AS [Customer ID], " & _

    " COALESCE(ShipTo.ShipToName, Prospect.ProspectName) AS Name, " & _

    " COALESCE(ShipTo.ShipToCity, Prospect.ProspectCity) AS City " & _

    " FROM ShipTo FULL OUTER JOIN " & _

    " Prospect ON ShipTo.ShipToName LIKE Prospect.ProspectName AND Prospect.ProspectCust LIKE ShipTo.ShipToCust " & _

    " WHERE " & _

    " (Prospect.Alias = '" & reqGetUserName & "' AND Prospect.ProspectName LIKE '%" & reqSearch & "%') " & _

    " OR (ShipTo.Alias > '%%' OR ShipTo.Alias IS NULL) " & _

    " AND (ShipTo.ShipToName LIKE '%" & reqSearch & "%') " & _

    " GROUP BY " & _

    " ShipTo.ShipToCust, Prospect.ProspectCust, ShipTo.ShipToName, Prospect.ProspectName, " & _

    " ShipTo.ShipToCity, Prospect.ProspectCity " & _

    " ORDER BY Name "

    reqSQL.CommandType = CommandType.Text

    reqSQL.Connection = ConsqlShipTo

    ConsqlShipTo.Open()

    '

    'CREATE NEW SQL DATA ADAPTER

    Dim sqlda As New SqlCeDataAdapter(reqSQL)

    '

    ' FILL DATASET

    Dim sqlds As New DataSet

    sqlda.Fill(sqlds, "Customer ID")

    '

    ' FILL DATATABLE - lightweight

    Dim sqldt As New DataTable

    sqlda.Fill(sqldt)

    Thursday, March 06, 2008 4:21 AM

All replies