Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimbrly
Starting Member
9 Posts |
 Posted - 2003-02-16 : 10:02:15
|
| Hello..I recently imported my Access 2000 files into SQL, and I have fixed all but two errors...The first error says, Monday February 10, 2003 8:15 PM I am getting an error when I transition from ACCESS TO SQL... the form works with the access database, but not with the SQL...[Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. The Error Occurred in D:\webserver\sidewalksavvy.com\www\database\GalleryPage.cfm: line 5452 : </tr>53 : <tr> 54 : <td bgcolor="666666" height="5"><font size="2" color="FFCC00"><span class="copyright"><font color="666666">..</font></span></font><span class="copyright"><font color="FFFFFF"> 55 : <cfoutput>#DateFormat(Now(),"dddd, mmmm dd, yyyy")#</cfoutput>56 : </font></span></td>--------------------------------------------------------------------------------SQL SELECT Top 20 ImageID, Image, PhotoGallery.StreetID, PhotoGallery.streetname, Description FROM PhotoGallery, StreetDirectory WHERE PhotoGallery.StreetName=StreetDirectory.STreetName and PhotoGallery.StreetName='Northampton' Group By Image, ImageID, PhotoGallery.StreetID, PhotoGallery.streetname, Description, EntryDate order by EntryDate DESC DATASOURCE sidewalksavvy VENDORERRORCODE 306 SQLSTATE HY000 I was told:"You should really change the datatype to something like char(50) or varchar(50). Then the equal sign comparison will work."The data type for PhotoGallery.StreetName in the database is nvarchar(50). Is this wrong??? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-16 : 10:13:17
|
| I don't think that's the problem. You have a column named "image" in the GROUP BY clause, and if that indeed is an image type column containing image data, it cannot be grouped. In fact, I don't even see the need for GROUP BY at all in your query, and if you were using GROUP BY to order the results, two things:1. Use ORDER BY to order/sort results2. GROUP BY does NOT automatically order results anymore, if you need a particular order, use ORDER BY |
 |
|
|
Kimbrly
Starting Member
9 Posts |
Posted - 2003-02-16 : 10:25:30
|
| The image field doesn't actually store the image, it just holds the link to the image on my server... it should be a text data type...I don't think that's the problem. You have a column named "image" in the GROUP BY clause, and if that indeed is an image type column containing image data, it cannot be grouped. In fact, I don't even see the need for GROUP BY at all in your query, and if you were using GROUP BY to order the results, two things:1. Use ORDER BY to order/sort results2. GROUP BY does NOT automatically order results anymore, if you need a particular order, use ORDER BY [/quote] |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-16 : 10:29:24
|
| Text datatype in SQL Server is different from text in Access. If it's actually a text datatype in SQL Server, the same applies as does image datatypes, they can't be grouped. The same thing for any other columns in the list that might be text, ntext or image (i.e. Description) |
|
|
|
Kimbrly
Starting Member
9 Posts |
Posted - 2003-02-16 : 10:46:05
|
| HEY... you were right... it was the GROUP BY! It works now, thank you.Now I will work on the other error... |
|
|
|
|
|
|
|
|
data types cannot be compared or sorted