CODEWITHSUNDEEP
sqlvb.netinner-joinmultiple-tables
Calidus
Calidus

Reputation: 1414

Multiple INNER JOINs SQL Server Compact Edition

I am trying to do multiple joins within a single query in SQL Server CE. I know that SQL Server CE doesn't support multiple SELECTs but I can't find any information on multiple INNER JOINs. I keep getting token errors on ProjectItemMaster (after the FROM) and the first INNER JOIN. Warning this is a big ugly query used to generate reports.

GetCutSheetDataByCustPnumTagQuery as defined as:

SELECT ReportCalculations.ItemNumber, ReportCalculations.PartNumber,
       ReportCalculations.calcWidth, ReportCalculations.calcHeight, 
       ReportCalculations.calcQuantity, ReportCalculations.Description,
       ReportCalculations.PrintonCutSheet, ProjectItemMaster.Quantity, 
       ProjectItemMaster.HingeDirection, ProjectItemMaster.ItemDescription,
       ProjectItemMaster.MetalFinish, ProjectItemMaster.Width, 
       ProjectItemMaster.Height, ProjectItemMaster.CustomerID, 
       CustomerMaster.CustomerId AS Id_CM, ProjectItemMaster.GlassType,  
       ProjectItemMaster.Tag AS Expr1, ReportCalculations.Tag, 
       ProjectItemMaster.ItemNumber AS Expr2, ReportCalculations.CalcX, 
       ReportCalculations.CalcY, ProjectItemMaster.OpeningWidth,
       ProjectItemMaster.ReturnDirection, ProjectItemMaster.PanelDirection, 
       ProjectItemMaster.ReturnWidth, ProjectItemMaster.ButtressHeight,
       ProjectItemMaster.ButtressWidth, ProjectItemMaster.AvailThickness, 
       ProjectItemMaster.PanelThickness, ProjectItemMaster.Image,
       ProjectItemMaster.SoftwareVersion, ProjectItemMaster.DatabaseVersion, 
       ProdlineMaster.Series, ProdlineMaster.Report, ProjectItemMaster.Addons,
       ProjectItemMaster.PanelWidth, CustomerMaster.CustomerName, 
       ProjectMaster.WO, ProjectMaster.PO, ProdlineMaster.SeriesName, 
       ReportCalculations.Series AS Expr3
FROM ProjectItemMaster 
INNER JOIN CustomerMaster 
ON ProjectItemMaster.CustomerID = CustomerMaster.CustomerId 
INNER JOIN ReportCalculations 
ON ProjectItemMaster.Tag = ReportCalculations.Tag 
AND ProjectItemMaster.CustomerID = ReportCalculations.CustomerID 
AND ProjectItemMaster.ProjectNumber = ReportCalculations.ProjectNumber 
INNER JOIN ProdlineMaster 
ON ReportCalculations.Series = ProdlineMaster.Series 
INNER JOIN ProjectMaster 
ON CustomerMaster.CustomerId = ProjectMaster.CustomerId 
AND ProjectItemMaster.ProjectNumber = ProjectMaster.ProjectNumber
WHERE (ReportCalculations.PrintonCutSheet = 'Y') 
AND (ProjectItemMaster.ProjectNumber = @ProjectNumber) 
AND (CustomerMaster.CustomerId = @CustomerID) 
AND (ProjectItemMaster.CustomerID = @CustomerID) 
AND (ProjectItemMaster.Tag = @TAG)

This is the function that Is executing the Query in VB.NET on the SQL Compact Database.

Public Function getCutSheetInfobyCustProdTag(ByRef customerID As String, 
                                             ByRef projectNumber As Integer, 
                                             ByVal tag As String) 
                                      As System.Data.DataTable

    Dim mydata As New DataTable
    GetCutSheetDataByCustPnumTagQuery.Parameters.Clear()
    GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@ProjectNumber", 
                                                                 projectNumber)
    GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@CustomerID", 
                                                                    customerID)
    GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@TAG", tag)

    Try
       splConnection.Open()
       Dim reader As SqlCeDataReader = GetCutSheetDataByCustPnumTagQuery.ExecuteReader
       mydata.Load(reader)
    Catch ex As Exception
       MessageBox.Show("Problem with reportcalculations table", "Error", 
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
       ' Return False
    Finally
       splConnection.Close()
    End Try
    'Return True
    Return mydata

End Function

If I can't uses multiple INNER JOINs I can I break the query down into a number of different queries but I would prefer not to. So it is easier to maintain a SQL Server CE and an SQL Server version of the program.

Upvotes: 1

Views: 3956

Answers (1)

Calidus
Calidus

Reputation: 1414

Apparently SQL Compact doesn't like it when you give Query parameters that do not have their type and length explicitly stated. The query kept trying to convert the CustomerID which was "987654" to a numeric. I figured this out because hard coding the values into the query made it run fine but when I used @CustomerID the program threw an expection. So switching from parameters.addwithvalue(Name, Value) to Parameters.add(Name, Type, Length) solved the problems.

 Public Function getCutSheetInfobyCustProdTag(ByRef customerID As String, ByRef projectNumber As Integer, ByVal tag As String) As System.Data.DataTable

        Dim mydata As New DataTable
        GetCutSheetDataByCustPnumTagQuery.Parameters.Clear()
        GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@ProjectNumber", System.Data.SqlDbType.Int)
        GetCutSheetDataByCustPnumTagQuery.Parameters("@ProjectNumber").Value = projectNumber
        GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@CustomerID", System.Data.SqlDbType.NVarChar, 25)
        GetCutSheetDataByCustPnumTagQuery.Parameters("@CustomerID").Value = customerID
        GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@TAG", System.Data.SqlDbType.NVarChar, 50)
        GetCutSheetDataByCustPnumTagQuery.Parameters("@TAG").Value = tag

        Try
            splConnection.Open()
            Dim reader As SqlCeDataReader = GetCutSheetDataByCustPnumTagQuery.ExecuteReader
            mydata.Load(reader)
        Catch ex As Exception
            MessageBox.Show("Problem with reportcalculations table", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            splConnection.Close()
        End Try

        Return mydata
    End Function

Thank you @X-Zero for your help

Upvotes: 2

Related Questions