ADO.NET database application run time Exception

Question

Help please with understanding what these exceptions telling me and how I can resolve them?

I've created an application with ADO.NET. The project debugger don't show any errors, though, during run time I receive these exceptions. I guess I am messing some lines of code I wrote in the 'PersonkartotekDBUtil.cs file, inside this method 'GetFullContactPersonTreeDB'. Here what I am trying to do is to show a full person overview info with connecting properties of Person directory through JOIN principles.

Can anyone correct me please?

The method that I suspect not being functional is:

public void GetFullContactPersonTreeDB(ref Person fcpt)
    {
        string fullPersonkartotek = @"SELECT  Person.PersonId, Person.FirstName, Person.MiddleName, Person.LastName, Person.Email, Person.Notes, Person.PrimaryAddress, Person.AlternativeAddresses, Person.TelefonNumbers
                                          Address.AddressID, Address.StreetName, Address.HouseNumber, Address.PostNr, Address.PersonPrimary, Person.AlternativePerson
                                          PostNr.PostNrID, PostNr.PostNumber, PostNr._Country, PostNr._City
                                          City.CityID, City.CityName,
                                          Country.CountryID, Country.CountryCode, Country.CountryName,
                                          Telefon.TelefonID, Telefon.Number, Telefon.TelefonType, Telefon.TelefonProvider, 
                                          Provider.ProviderID, Provider.ProviderName

            FROM      Person INNER JOIN
            Address ON Person.PersonId = Address.Person INNER JOIN
            Telefon ON Person.PersonID = Telefon.Person
            AlternativeAddress ON Person.PersonID = AlternativeAddress.Person
            Address ON Person.PersonID = Address.Person
            WHERE   (Person.PersonID = @PersonId)";


    }

The Error message on the console window after running the program:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '.'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Infrastructure.PersonkartotekDB.ADONET.PersonkartotekDBUtil.GetFullContactPersonTreeDB(Person& fcpt) in C:\Users\User\Desktop\E18I4DABHandIn2au2016082168\Personkartotek\Infrastructure\PersonkartotekDB.ADONET\PersonkartotekDBUtil.cs:line 382 ClientConnectionId:716d2899-cbbe-4aa6-89de-66452d907b66 Error Number:102,State:1,Class:15

Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '.'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Infrastructure.PersonkartotekDB.ADONET.PersonkartotekDBUtil.GetFullContactPersonTreeDB(Person& fcpt) in C:\Users\User\Desktop\E18I4DABHandIn2au2016082168\Personkartotek\Infrastructure\PersonkartotekDB.ADONET\PersonkartotekDBUtil.cs:line 449 at ApplicationLogic.PersonkartotekApp.ProgramApp() in C:\Users\User\Desktop\E18I4DABHandIn2au2016082168\Personkartotek\ApplicationLogic\PersonkartotekApp.cs:line 14 at ProgramApplication.Program.Main(String[] args) in C:\Users\User\Desktop\E18I4DABHandIn2au2016082168\Personkartotek\ProgramApplication\Program.cs:line 10

The attachment shows the exception on the debugger.

Please do not expect us to go dig in github to get the code. Post the relevant code in the question body. — Zohar Peled
arent you missing some commas in your select statement? Just after "Person.TelefonNumbers", "Person.AlternativePerson" and "PostNr._City"? — Franco Fusaro
Copy the query into SSMS. Brake the columns list in the select so that each column will be in a different row. Check visually if you see something suspicious. If not, declare the @PersonId variable and try to run it directly on SSMS. If you get an error message, double click it. This should move the caret roughly to where the problem is. — Zohar Peled

Daniel Filipe Daniel Filipe · Accepted Answer · 2018-10-10T13:03:09

Looks like you were missing several commas and also missing an Inner join.

By running your query in Sql server management studio(SSMS) and using parse(CTRL+F5) you can highlight the problem.

Please see fixed code below. In future it's worth having consistent formatting to make your life easier.

SELECT  Person.PersonId, Person.FirstName, Person.MiddleName, Person.LastName, Person.Email, Person.Notes, Person.PrimaryAddress, Person.AlternativeAddresses, Person.TelefonNumbers,
                                      Address.AddressID, Address.StreetName, Address.HouseNumber, Address.PostNr, Address.PersonPrimary, Person.AlternativePerson,
                                      PostNr.PostNrID, PostNr.PostNumber, PostNr._Country, PostNr._City,
                                      City.CityID, City.CityName,
                                      Country.CountryID, Country.CountryCode, Country.CountryName,
                                      Telefon.TelefonID, Telefon.Number, Telefon.TelefonType, Telefon.TelefonProvider, 
                                      Provider.ProviderID, Provider.ProviderName

        FROM      Person 
        INNER JOIN Address ON Person.PersonId = Address.Person 
        INNER JOIN Telefon ON Person.PersonID = Telefon.Person
        INNER JOIN AlternativeAddress ON Person.PersonID = AlternativeAddress.Person
        INNER JOIN   Address ON Person.PersonID = Address.Person
        WHERE   Person.PersonID = @PersonId

ADO.NET database application run time Exception

1 Answers