I'm completly baffled by this.

I have a database table (VYtests) with some data in it (if needed I can post the script SQL generated to create the table so yall can see types etc)

I also have an ASP file I wrote, works fine in Access, well now its getting big and they are wanting to upgrade to SQL. I imported the tables etc so I have all the data in SQL server.

The SQL statement "Select * from VYTests where Approve = 0 order by testDate desc" only displays ProjectGroup

Where as "Select OtherEquipmentName,ProjectGroup,testNumber,TestDat e,jobNumber,EquipmentName,MarksIdNumber,ApprovedBy from VYTests where Approve = 0 order by testDate desc"

Displays everything properly.

I've run into this before but I want to know WHY it does this.

to see what I'm talking about go to:
You will see the SQL statements used listed at the top of the pages.
Listing fields
http://dev.vbar.com/test-management/...ved&machine=VY
Using *
http://dev.vbar.com/test-management/...ved&machine=VY

I can post the code if needed.

Thanks a bunch!
Scott

Hello,

Out of curiosity, do you have both a table and a view using the VYTests name?

Brian

If your answer to Brian's question is "NO", Can you generate the SQL script and post that here?

_________________________
-Vijay G
Strive for Perfection

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VYTests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VYTests]
GO

CREATE TABLE [dbo].[VYTests] (
    [Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BrokenWires] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Customer] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EquipmentDescript] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EquipWeight] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FailLoad] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [jobNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LoadCells] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MagDate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MagNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MarksIdNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MeasureLoad] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PictNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PinBushingAdapter] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProofLoad] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [stopUsed] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TestDate] [smalldatetime] NULL ,
    [TestDescript] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [testLevelClass] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [testNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Length] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [testTech] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [thirdPartyWit] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [timeHeld] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Color] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WireCertNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WireManu] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WireReel] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WLL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Approve] [bit] NOT NULL ,
    [Eye1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Eye2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Kinks] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Waves] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EquipmentName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SlingManu] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OtherEquipmentName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProjectGroup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ApprovedBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastEdit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastEditDate] [smalldatetime] NULL ,
    [VBwitness] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I got that. As far as I have experienced you always have to use your TEXT datatype COLUMNS at the end of your select statement.

Eg: Select OtherDATAType_COLUMNS, TEXT_COLUMN from TABLE1

When you give Select * from VYtests, that behaves abnormally. I am sorry, I could not find time to search for supporting docmuent to say why this happens. If I find let me post it here.

But try to keep the ntext or text datatype columns come at last in your SELECT statement. Or it is so simple, while creating the table, place all the TEXT/nTEXT datatype columns at last, so that when you do a SELECT *... that doesn't keep you wondering where your data has gone off.

So, as in your case, you got to select the columns that are only needed into your recordset. That is the right approach too, when you need only 3 columns from the table (of 25 columns), never do a select *. Select only the columns that are needed.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

The problem is most of my fields are text fields...and there is one page where I HAVE to call ALL the fields for processing (where the entire record is editable)

Is this a short coming in SQL? Because this works fine in access.

I am not sure if that is a short coming in sql. Yes I have observed working with memo and other big datatypes with access. There seem to be no problem with it.

Remember, when you want to edit the entire record, while doing so, do not use select * ... there, instead, arrange the ntext datatype columns all at the end, to get that work. Also while updating keep them at last. Then you should find not problem at all.

Cheers!

_________________________
-Vijay G
Strive for Perfection

Well I re-created my tables and everything works like it should. What is the reason for this? Is it documented by microsoft some where?

Thanks.