SQL statement Syntax

Can anybody see what is the problem with this SQL statement, particularly the =True part:

strSQL = "SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & " " & "ysnMasterImage = True"

SQL Server Gives:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

/includes/left_menu.asp, line 59

but access works?
net-workxAsked:
Who is Participating?
 
rdivilbissConnect With a Mentor Commented:
Actually, I just noticed you preface your column names with the assumed type, so does ysnMasterImage refer to a yes no field which exists in MS Access but has no complementary field in MS SQL.

What is the type of that column?  Boolean, VarChar, or something else?

If it is bit:

"SELECT     *
FROM         tblPropertyPhotos
WHERE     (intPropertyID = "& PropertyID &") AND (ysnMasterImage = 1)"

if it is varchar:

"SELECT     *
FROM         tblPropertyPhotos
WHERE     (intPropertyID = "& PropertyID &") AND (ysnMasterImage = "& chr(34) & True & chr(34) &")"


0
 
rdivilbissCommented:
strSQL = "SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & " " & "ysnMasterImage = True"

should be

strSQL = "SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & " ysnMasterImage = True"
0
 
net-workxAuthor Commented:
Nope Still The Same!!!

It actually now says:

"SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & "AND ysnMasterImage = True"

that doesnt work either tho so any more ideas?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rdivilbissCommented:
First, thanks for catching the missing AND for me.

Is ysnMasterImage the correct name of a column that contains true false values?

0
 
net-workxAuthor Commented:
How strange is that that we both just worked it out at the same time!

I went into the Enterprise Manager and did a return all rows to check the column name and then it it me that SQL server does not store it like access and the figures were 0,0,1,0 for the ysnMasterImage!

Changed to:

strSQL = "SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & "AND ysnMasterImage=1" and it worked straight away!

It was only the fact you sent me to check the field name that i noticed so the points are yours as you gave the correct answer too!

Thanks,
Carl
0
 
net-workxAuthor Commented:
It was a bit field by the way!  Just for future reference what does bit stand for?
0
 
rdivilbissCommented:
one bit, e.g. 0 or 1 only.  8 bits is a byte, 16 bits is a word, 32 bits is a double word, etc.

Thanks,
Rod

Good luck with the rest of your project...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.