Problem with ExecuteQuery

[Zero-G.]

Hey
I use VB.NET 2010 and your components Version:6.10.135

I have created a Public Class like this:

Code: Select all

Public Class Umsatzstatistik
    Public Property Firma() As String
    Public Property Auftragsart() As String
    Public Property Auftragssumme() As Decimal?

    Public Sub New()
        Firma = ""
        Auftragsart = ""
        Auftragssumme = 0
    End Sub
    Public Sub New(nFirma As String, nAuftragsart As String, nAuftragssumme As Decimal)
        Firma = If(IsDBNull(nFirma), "", nFirma)
        Auftragsart = If(IsDBNull(nAuftragsart), "", nAuftragsart)
        Auftragssumme = nAuftragssumme
    End Sub
End Class

And I try to run the following code to my Server:

Code: Select all

 myDataContext = New VOptNeuContext.VOptNeuDataContext(LinqProvider.GetConnectionString)
        Dim Druck = myDataContext.ExecuteQuery(Of Umsatzstatistik)("SELECT " & _
                                                 "CONCAT(f.`firmenname`, ' (', f.`strasse`, ')' ) AS `Firma`, " & _
                                                 "a.auftragsart AS `Auftragsart`, SUM(a.`BezBetrag`) AS `Auftragssumme` " & _
                                                 "FROM " & _
                                                 "`Auftrag` a " & _
                                                 "INNER JOIN Kundenstamm k " & _
                                                 "ON k.`KundenID` = a.`KundenID` " & _
                                                 "INNER JOIN firmendaten f " & _
                                                 "ON k.`firmenid` = f.`ID` " & _
                                                 "WHERE a.`AufDat` BETWEEN '2011-01-01' AND '2011-04-30' " & _
                                                 "AND a.`Bewegung` = 1 " & _
                                                 "GROUP BY `Firma`, " & _
                                                 "a.`Auftragsart` WITH ROLLUP;")

When trying to get the result set from "Druck", I get an error, that the fiel with the name _Firma is not found in resultset
Please help - THX

[StanislavK]

The thing is that the LinqConnect runtime tries to fill the private fields, not the public properties. For each field, LinqConnect searches for a column (or an alias) with the same name. Since the fields are not explicitly declared in your example, they are auto-generated; e.g., a '_Firma' field is generated for the 'Firma' property. And since there is no '_Firma' column or alias in the query, the runtime does not know what column should be used to fill the '_Firma' field, and throws an exception.

To avoid such situations, you can explicitly define the fields (and use their names in queries). Otherwise, you can add attributes specifying the mapping between columns and properties (i.e., add the Table attribute to the class and the Column attributes to the properties).

Please tell us if this helps.