MSSQL not returning all data from query

Changing from MYSQL to MS SQL on our ASP pages. We can query the database and get all fields with data. However when inserting the value from the same query to the ASP page only some data is displayed.  For example: 3 columns (lets say a, b, c) and 1 row of data (lets sat 1,2,3). So I declare the value for textboxes.

Textbox 1
rs.Fields.Item("a").Value

Textbox 2
rs.Fields.Item("c").Value

Textbox 3
rs.Fields.Item("b").Value

textbox 3 has no data displayed but, if I switch textbox 2 and 3 around so I request them in order of my SQL statement, they all have the data displayed.

Any ideas how to fix?
crundleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
can you please share the query with us ? and the table structure
0
crundleAuthor Commented:
Query:
SELECT tbldeliveryorder.stName, tbldeliveryorder.stAddress, tbldeliveryorder.stAddress2, tbldeliveryorder.stAddress3, tbldeliveryorder.stPhone
FROM tbldeliveryorder
WHERE (tbldeliveryorder.CLIENT_ID = 3)  

Results in MSSQL
Company1  /  Address1  /  address2 /  address3 /  1234569870

The textboxes are in the following order on the page company name, phone number, address1, address2, address3

Only 2 textboxes show data company name and phone number but, if I move phone number textbox to the bottom of the page then all textboxes have data.
0
Pratima PharandeCommented:
There is no problem with the query......

as you are saying
if I move phone number textbox to the bottom of the page then all textboxes have data

then must be problem in code ...can you share that?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
The problem is that you a text data type, right?  If so the solutions are pretty easy, but you will have to post the structure of the table tbldeliveryorder.
0
crundleAuthor Commented:
0
Anthony PerkinsCommented:
Unfortunately, that tells me nothing.  I need the structure of the table tbldeliveryorder.  That means all the columns and their data types.  If you need help with getting that, please indicate the SQL Server version you are using.
0
crundleAuthor Commented:
I think this is what you are looking for;


USE [CLLDdataSQL]
GO
/****** Object:  Table [CLLAdmin].[tbldeliveryorder]    Script Date: 01/31/2008 20:07:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [CLLAdmin].[tbldeliveryorder](
	[DeliverorderID] [int] IDENTITY(1,1) NOT NULL,
	[TypeofMove] [int] NULL DEFAULT (NULL),
	[Trucking_COid] [int] NULL DEFAULT (NULL),
	[Trucking_CO] [nvarchar](50) NULL DEFAULT (NULL),
	[QuoteNumber] [nvarchar](50) NULL DEFAULT (NULL),
	[PONumber] [nvarchar](50) NULL DEFAULT (NULL),
	[stName] [nvarchar](50) NULL DEFAULT (NULL),
	[stAddress] [nvarchar](50) NULL DEFAULT (NULL),
	[stAddress2] [nvarchar](50) NULL DEFAULT (NULL),
	[stAddress3] [nvarchar](50) NULL DEFAULT (NULL),
	[stPhone] [nvarchar](15) NULL DEFAULT (NULL),
	[stContact] [nvarchar](25) NULL DEFAULT (NULL),
	[rtName] [nvarchar](50) NULL DEFAULT (NULL),
	[rtPhone] [nvarchar](50) NULL DEFAULT (NULL),
	[rtAddress] [nvarchar](50) NULL DEFAULT (NULL),
	[rtAddress2] [nvarchar](50) NULL DEFAULT (NULL),
	[ShipmentID] [int] NULL DEFAULT (NULL),
	[specialinstr] [ntext] NULL DEFAULT (NULL),
	[rtContact] [nvarchar](50) NULL DEFAULT (NULL),
	[pickuptime] [nvarchar](15) NULL DEFAULT (NULL),
	[DeliverTime] [nvarchar](15) NULL DEFAULT (NULL),
	[pckQTY1] [int] NULL DEFAULT (NULL),
	[pckQTY2] [int] NULL DEFAULT (NULL),
	[pcktype1] [nvarchar](15) NULL DEFAULT (NULL),
	[pcktype2] [nvarchar](15) NULL DEFAULT (NULL),
	[prdescription1] [nvarchar](75) NULL DEFAULT (NULL),
	[prdescription2] [nvarchar](75) NULL DEFAULT (NULL),
	[prclass1] [nvarchar](3) NULL DEFAULT (NULL),
	[prclass2] [nvarchar](3) NULL DEFAULT (NULL),
	[weight1] [nvarchar](10) NULL DEFAULT (NULL),
	[weight2] [nvarchar](10) NULL DEFAULT (NULL),
	[Liftgate] [int] NULL DEFAULT (NULL),
	[PalletJack] [int] NULL DEFAULT (NULL),
	[ReleaseNUM] [nvarchar](45) NULL DEFAULT (NULL),
	[driver] [nvarchar](25) NULL DEFAULT (NULL),
	[trailer] [nvarchar](8) NULL DEFAULT (NULL),
	[AirRouting] [nvarchar](45) NULL DEFAULT (NULL),
	[CBM1] [nvarchar](10) NULL DEFAULT (NULL),
	[CBM2] [nvarchar](10) NULL DEFAULT (NULL),
 CONSTRAINT [_tbldeliveryorder_PRIMARY] PRIMARY KEY NONCLUSTERED 
(
	[DeliverorderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

0
Anthony PerkinsCommented:
It looks like this fell through the cracks.  But the problem is exactly as I suspected, there is a column (specialinstr),
of data type text, which is causing the problem.  In order to resolve it you need to explicitly name the columns and list the specialinstr column last.  So instead of:

rsShipment.Source = "SELECT  tblshipment.MBL, tblshipment.tblDO_ID,  tblshipment.CLIENT_NAME,  tblshipment.CLIENT_ID,  tblshipment.ShipmentID,  tblshipment.CLID,  tblshipment.Railmove,  tblshipment.SSLType_of_Move, tblshipment.LastUpdate,  tblshipment.EnteredDate,  tblshipment.EnteredBy,  tblshipment.Lastupdateby, tblshipment.Active, tbldeliveryorder.* FROM  tbldeliveryorder  INNER JOIN tblshipment ON (tbldeliveryorder.DeliverorderID = tblshipment.tblDO_ID) WHERE  (tblshipment.ShipmentID = 1206)"

It should be:
rsShipment.Source = "SELECT  tblshipment.MBL, tblshipment.tblDO_ID,  tblshipment.CLIENT_NAME,  tblshipment.CLIENT_ID,  tblshipment.ShipmentID,  tblshipment.CLID,  tblshipment.Railmove,  tblshipment.SSLType_of_Move, tblshipment.LastUpdate,  tblshipment.EnteredDate,  tblshipment.EnteredBy,  tblshipment.Lastupdateby, tblshipment.Active, tbldeliveryorder.DeliverorderID,  tbldeliveryorder.TypeofMove, tbldeliveryorder.Trucking_COid, ... tbldeliveryorder.CBM1, tbldeliveryorder.CBM2, tbldeliveryorder.specialinstr FROM  tbldeliveryorder  INNER JOIN tblshipment ON (tbldeliveryorder.DeliverorderID = tblshipment.tblDO_ID) WHERE  (tblshipment.ShipmentID = 1206)"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.