We help IT Professionals succeed at work.

MSSQL not returning all data from query

Medium Priority
216 Views
Last Modified: 2011-10-19
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
can you please share the query with us ? and the table structure

Author

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.
CERTIFIED EXPERT

Commented:
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?
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

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

CERTIFIED EXPERT
Top Expert 2012
Commented:
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)"
Forced accept.

Computer101
EE Admin

Explore More ContentExplore courses, solutions, and other research materials related to this topic.