Invalid Column Name ' ' SQL 2005

  • Im trying to concetenate Sql commands depending on the parameters but i keep getting the error message invalid column name, when im using C#, but the same procedure works fine with VB6.

    This is how im doing it, The error message in the C# programme.

    DECLARE @sql nvarchar(4000),

    @paramlist nvarchar(4000),

    SELECT @sql =

    'SELECT Account.AccountName,Site.SiteName,SiteAudit.SiteAuditID,SiteAudit.DateDue,SiteAudit.DateComplete,Audit.AuditTitle, [User].FirstName + " " + [User].LastName as FullName

    FROM dbo.Site INNER JOIN

    dbo.SiteAudit ON dbo.Site.SiteID = dbo.SiteAudit.SiteID INNER JOIN

    dbo.Audit ON dbo.Audit.AuditID = dbo.SiteAudit.AuditID INNER JOIN

    dbo.[User] ON dbo.[User].UserID = dbo.SiteAudit.AssignedUserID INNER JOIN

    dbo.Account ON dbo.Site.AccountID = dbo.Account.AccountID

    Where NOT EXISTS(SELECT SiteAuditID FROM AuditRefusal WHERE SiteAudit.SiteAuditID = AuditRefusal.SiteAuditID AND IsRefusal = 1)

    AND SiteAudit.Active = 1'

    --

    IF @AccountID > 0

    SELECT @sql = @sql + ' AND SiteAudit.AccountID = @AccountID'

    SELECT @paramlist = '@AccountID int'

    EXEC sp_executesql @sql @paramlist,

    @AccountID

    even if i replace @AccountID by 2 and leave out the parameter list i still get the error message.

    the first part works fine without the concatenation.

    Does anyone what i should do to correct this.

  • i believe the error is here:

    User].FirstName + " " + [User].LastName as FullName

    double quotes are treated the same as brackets: [ ]

    so it is looking for a column named [emptyspace], which doesn't exist....switch to single quotes instead.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That was the problem ,

    thank you very much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply