• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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?
0
crundle
Asked:
crundle
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now