[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL statement Syntax

Posted on 2005-04-18
7
Medium Priority
?
256 Views
Last Modified: 2010-04-06
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?
0
Comment
Question by:net-workx
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13811341
strSQL = "SELECT * FROM tblPropertyPhotos WHERE intPropertyID=" & PropertyID & " " & "ysnMasterImage = True"

should be

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

Author Comment

by:net-workx
ID: 13811422
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
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13811500
First, thanks for catching the missing AND for me.

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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Accepted Solution

by:
rdivilbiss earned 1000 total points
ID: 13811558
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
 

Author Comment

by:net-workx
ID: 13811602
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
 

Author Comment

by:net-workx
ID: 13811618
It was a bit field by the way!  Just for future reference what does bit stand for?
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13811673
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question