Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 28th, 2004, 11:06 AM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to skotman Send a message via Yahoo to skotman
Default STRANGE behavior..SQL Help

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
 
Old May 28th, 2004, 11:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

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

Brian
 
Old May 28th, 2004, 12:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
-Vijay G
Strive for Perfection
 
Old May 28th, 2004, 01:38 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to skotman Send a message via Yahoo to skotman
Default

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



 
Old May 28th, 2004, 01:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 28th, 2004, 02:12 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to skotman Send a message via Yahoo to skotman
Default

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.

 
Old May 28th, 2004, 04:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 6th, 2004, 02:55 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to skotman Send a message via Yahoo to skotman
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange behavior of Firefox 3 and hallo.xml PKHG BOOK: XSLT 2.0 and XPath 2.0 Programmer's Reference, 4th Edition ISBN: 978-0-470-19274-0 3 August 6th, 2008 10:32 AM
Strange Behavior With Anchor in XSLT kwilliams XSLT 6 July 21st, 2005 01:52 PM
Strange behavior Listview under XP pavel Pro VB 6 1 June 1st, 2005 05:14 AM
Strange behavior of DateTimePicker? wwz VS.NET 2002/2003 0 February 19th, 2004 06:56 AM
Please help with strange file download behavior! glwatson Classic ASP Basics 0 September 10th, 2003 07:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.