CODEWITHSUNDEEP
c#asp.net
Mike
Mike

Reputation: 3

c# SQL Statement Incorrect syntax near '.'

This code wasn't working as it should as it was showing all ads whatever if the approv= YES or NO. Where it is supposed to check into UserInfo if the user has YES in column 'approv' then show his ads otherwise don't show. So I tried to fix it but now I have put myself in more trouble as I am getting the error message as in my post title incorrect syntax near '.' 

SqlDataAdapter dashowadsoneLogo = new SqlDataAdapter(@"SELECT AD.[AdsID], AD.[Country], AD.[State], AD.[City], AD.[AdsTit], SUBSTRING([AdsDesc],1,50) as AD.AdsDesc,
                  AD.[AdsPrice], AD.[Section], AD.[Category], AD.[Img1], AD.[AdsDate], I.[approv] FROM [ads] as AD JOIN UserInfo as I ON  AD.[Country] = @Location and I.[approv]='YES' ORDER BY AD.[AdsDate] DESC", cs);

Upvotes: 0

Views: 532

Answers (4)

Rahul Singh
Rahul Singh

Reputation: 21795

You can't have period symbol in column name alias:-

 SUBSTRING([AdsDesc],1,50) as AD.AdsDesc,

Change this to:-

 SUBSTRING([AD.AdsDesc],1,50) as AdsDesc,

Also, for such complex query you should create stored procedure instead.

Upvotes: 4

Vaibhav Chaurasiya
Vaibhav Chaurasiya

Reputation: 166

Try This : 

  SqlDataAdapter dashowadsoneLogo = new SqlDataAdapter(@"SELECT AD.[AdsID], AD.[Country], AD.[State], AD.[City], AD.[AdsTit], SUBSTRING(AD.[AdsDesc],1,50) as AD.AdsDesc,AD.[AdsPrice], AD.[Section],AD.[Category],AD.[Img1], AD.[AdsDate], I.[approv] FROM [ads] as AD JOIN UserInfo as I ON  AD.[Country] = @Location and I.[approv]='YES' ORDER BY AD.[AdsDate] DESC",cs);

Upvotes: 0

Rajeesh Menoth
Rajeesh Menoth

Reputation: 1750

You missed the alias AD in your code.

Try This Code :

SqlDataAdapter dashowadsoneLogo = new SqlDataAdapter(@"SELECT AD.[AdsID], AD.[Country], AD.[State], AD.[City], AD.[AdsTit], SUBSTRING([AD.AdsDesc],1,50) as AD.AdsDesc,
              AD.[AdsPrice], AD.[Section], AD.[Category], AD.[Img1], AD.[AdsDate], I.[approv] FROM [ads] as AD JOIN UserInfo as I ON  AD.[Country] = @Location and I.[approv]='YES' ORDER BY AD.[AdsDate] DESC", cs);

Error Portion in you code :

SUBSTRING([AdsDesc],1,50) as AD.AdsDesc

Upvotes: 0

Aman
Aman

Reputation: 142

you have mistake here replace in your query from SUBSTRING([AdsDesc],1,50) as AD.AdsDesc change to SUBSTRING([AD.AdsDesc],1,50) as [AdsDesc]

If it is not working then replace like this

 SELECT [AD.AdsID], [AD.Country], [AD.State], [AD.City], [AD.AdsTit],

Upvotes: 0

Related Questions