November 25, 2008 at 6:02 am
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.
November 25, 2008 at 6:22 am
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
November 25, 2008 at 6:35 am
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