CODEWITHSUNDEEP
c#sqlsql-server-2008-r2inner-join
Daniel Underwood
Daniel Underwood

Reputation: 2271

SQLException at Inner Join

I am using a C# program that calls a SQL statement that is executed on an instance of SQL Server 2008 R2. Here is the SQL Call:

SELECT TOP 1 as1.AssetTagID, as1.TagID, as1.CategoryID, as1.Description,
    as1.HomeLocationID, as1.ParentAssetTagID 
    FROM Assets AS as1 ORDER BY ar.DateScanned DESC
INNER JOIN AssetsReads AS ar
ON as1.AssetTagID = ar.AssetTagID
WHERE (ar.ReadPointLocationID='Readpoint1' OR ar.ReadPointLocationID='Readpoint2')
    AND as1.TagID!='000000000000000000000000';

I am getting an SQLException around INNER. The exception text is the following:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INNER'.

I can put the stack trace on here as well, but I felt like it would clutter the question. Here is the actual string in the C# code that I am using with the call:

"SELECT TOP 2 as1.AssetTagID, as1.TagID, " +
    "as1.CategoryID, as1.Description, as1.HomeLocationID," +
    "as1.ParentAssetTagID FROM Assets AS as1 ORDER BY ar.DateScanned DESC\n" +
    "INNER JOIN AssetsReads AS ar ON as1.AssetTagID = ar.AssetTagID\n" +
    "WHERE (ar.ReadPointLocationID='" + IntegrationService.Lane2Zones[0] + 
    "' OR ar.ReadPointLocationID='" + IntegrationService.Lane2Zones[1] + "')\n" +
    "AND as1.TagID!='000000000000000000000000';"

Upvotes: 0

Views: 86

Answers (2)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

Like he said... your order by clause was out of order :)

SELECT TOP 1 as1.AssetTagID,
         as1.TagID,
         as1.CategoryID,
         as1.Description,
         as1.HomeLocationID,
         as1.ParentAssetTagID
FROM   Assets AS as1
   INNER JOIN AssetsReads AS ar
           ON as1.AssetTagID = ar.AssetTagID
WHERE  ( ar.ReadPointLocationID = 'Readpoint1'
      OR ar.ReadPointLocationID = 'Readpoint2' )
   AND as1.TagID != '000000000000000000000000'
ORDER  BY ar.DateScanned DESC;

I'll also note that using schema qualified objects is recommended by Microsoft (http://technet.microsoft.com/en-us/library/ms190387(v=sql.105).aspx). Also you should use parenthesis around your top (value) statement.

SELECT TOP (1) [as1].[AssetTagID],
           [as1].[TagID],
           [as1].[CategoryID],
           [as1].[Description],
           [as1].[HomeLocationID],
           [as1].[ParentAssetTagID]
FROM   [<schema>].[Assets] AS [as1]
   INNER JOIN [<schema>].[AssetsReads] AS [ar]
           ON [as1].AssetTagID = [ar].[AssetTagID]
WHERE  ( [ar].[ReadPointLocationID] = 'Readpoint1'
      OR [ar].[ReadPointLocationID] = 'Readpoint2' )
   AND cast([as1].TagID AS [INT]) != 0
ORDER  BY [ar].[DateScanned] DESC;

Upvotes: 0

dazedandconfused
dazedandconfused

Reputation: 3186

Your ORDER BY statement can't be there. Move it to the end

I'll also give the obligatory "Don't do this" speech. Concatenating SQL strings like this opens you up to SQL injection attacks. There is plenty of information about that on SO and Google so I won't go into it, but you should definitely consider making this a parameterized query.

Upvotes: 4

Related Questions