nText Null Value error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

nText Null Value error

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-18 : 09:56:05

In one of my tables, the data type is ntext and if it contains a null value, I get the following error:

String[6]: the Size property has an invalid size of 0

I'm using vb.net 2005 and sql server 2000.

Here's a code snippet of my code behind:


Dim pDescription As SqlParameter = Command.Parameters.Add("@description", SqlDbType.NText)
 pDescription.Direction = ParameterDirection.Output

And my stored procedure:


CREATE  PROCEDURE dbo.retrieveEquipmentDetails

	@id			INT,	
    @assignedID			VARCHAR(50)		OUTPUT,
    @manufacturer		VARCHAR(100)		OUTPUT,
    @modelNumber		VARCHAR(50)		OUTPUT,
    @serialNumber		VARCHAR(50)		OUTPUT,
    @equipmentType		VARCHAR(50)		OUTPUT,
    @description			NTEXT			OUTPUT,
    @location			VARCHAR(50)		OUTPUT,
    @status			VARCHAR(50)		OUTPUT,
    @modifiedDate		DATETIME		OUTPUT,
    @modifiedBy			INT			OUTPUT,
    @notes			NTEXT			OUTPUT,
    @calibrationLabId		VARCHAR(255)		OUTPUT,
    @calibrationRequired		BIT			OUTPUT,
    @calibrationDate		DATETIME		OUTPUT,
    @calibrationDueDate		DATETIME		OUTPUT,
    @assetNumber		VARCHAR(50)		OUTPUT,
   --@picture			IMAGE			OUTPUT,
   @errMessage			VARCHAR(256)	OUTPUT

AS
BEGIN

	DECLARE @errCode      INT		
			
	DECLARE c_equipment CURSOR FOR
	
		SELECT  a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.Description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			--a.Picture,			
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber
					

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab
		
		  
	FOR READ ONLY

	OPEN c_equipment	
	
	FETCH NEXT FROM c_equipment INTO @assignedID, @manufacturer, @modelNumber,
		  @serialNumber, @equipmentType, @description, @location, @status,
		  @modifiedDate, @modifiedBy, @notes, @calibrationLabId, @calibrationRequired, @calibrationDate,
		  @calibrationDueDate, @assetNumber--, @picture

		IF( @@FETCH_STATUS <> 0 )
		BEGIN
			SET @errMessage = 'Equipment not found.'
			GOTO HANDLE_APPERR
		END
	CLOSE c_equipment
	DEALLOCATE c_equipment
	
	SET @errCode = 0	
	RETURN @errCode
	
HANDLE_APPERR:
	IF( CURSOR_STATUS( 'local', 'c_equipment' ) >= 0 )
	BEGIN
		CLOSE c_equipment
		DEALLOCATE c_equipment
		
	END
	SET @errCode = 1	
	RETURN
HANDLE_DBERR:
	SET @errCode = -1	
	RETURN
END
GO

So, if anyone has any suggestions, I would like to hear them.
And btw, it also happens to the image field (@picture) but I commented it out to try to get the rest working until I'm ready to tackle that one.

Thanks,

Zath

Kristen
Test

22859 Posts

Posted - 2006-12-18 : 10:03:09

Wouldn't it be easier to use a SELECT statement to get the columns from this one row back to your application, rather than a Cursor and Output variables?

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-18 : 10:04:56

No, that wouldn't be easier as I am creating an object to use in my application, and it needs to be part of it.

And, when the stored procedure is accessed, the database connection is open and I can't open another one until it closes and by then, the object needs to be complete.

Zath

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-18 : 10:26:14

How about using ISNULL()?

DECLARE c_equipment CURSOR FOR
	
		SELECT  a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			ISNULL(a.Description,'') AS Description
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			--a.Picture,			
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber
					

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-18 : 10:37:01

Sorry but no, I get the same error if I change it to

ISNULL(a.Description,'') AS Description,

There has to be a way to handle null values for this stored procedure.

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 10:50:49

I don't see a loop for the CURSOR. Are you only trying to establish if a record exists or not?
If so, try this code

DECLARE	@errCode INT

IF EXISTS
		(
			SELECT		NULL
			FROM		tblEquipments a
			INNER JOIN	tblManufacturers b ON b.manufacturerID = a.manufacturer
			INNER JOIN	tblLocation c ON c.locationID = a.location
			INNER JOIN	tblStatus d ON d.statusID = a.Status
			INNER JOIN	tblEquipment_Type e ON e.ID = a.EquipmentType
			INNER JOIN	tblLabs f ON f.ID = a.calLab
			WHERE		a.ID = @id
		)
	SELECT	@errCode = 1
ELSE
	SELECT	@errCode = -1

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-12-18 : 10:59:48

"No, that wouldn't be easier as I am creating an object to use in my application, and it needs to be part of it."

I may be being thick I don't see what that has to do with it!

You can get the data back in a ResultSet and just transfer it to your variables. But if for some reason that isn;t possible you can just do the


		SELECT  @assignedID = a.assignedID,
			...		
		FROM 	tblEquipments a, 
			...
		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab

"And, when the stored procedure is accessed, the database connection is open and I can't open another one until it closes and by then, the object needs to be complete."

The resultset will come down the same connection you called the SProc with, and once you've "consumed" the single record in the resultset you can close the resultset / command / connection.

(However, none of this solves your error, but I just think the way you are tackling the problem is a lot more coding than I would use!!, and is possibly less efficient)

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-18 : 11:13:54

Thanks for the suggestion and I really don't need the cursor.

I am simply retrieving the information back.

So, I removed the cursor and tried this:


		SELECT  @assignedID = a.assignedID,
			@manufacturer = b.Manufacturer,
			@modelNumber = a.modelNumber,
			@serialNumber = a.serialNumber,
			@equipmentType = e.equipmentType,
			@description = a.description,
			@location = c.Location,
			@status = d.Status,
			@modifiedDate = a.modifiedDate,
			@modifiedBy = a.modifiedBy,
			--@notes = a.Notes,
			--a.Picture,			
			@calibrationLabId = f.LabName,
			@calibrationRequired = a.calibrationRequired,
			@calibrationDate = a.calDate,
			@calibrationDueDate = a.CalDueDate,
			@assetNumber = a.assetNumber
					

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab

I get the Check Syntax Error:

The assignment operator operation cannot take a ntext datatype as an argument.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-12-18 : 11:20:23

You can't set the value of a Text / NText parameter in SQL Server. You will need to return a single row result set instead of using parameters.

Kristen
Test

22859 Posts

Posted - 2006-12-18 : 12:40:14

You can return the first 8,000 characters of a Text [or 4,000 of an NText] ... if that is any help?

SQL2005 would let you return the lot, as varchar(MAX), IIRC

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-18 : 12:59:26

Ok, I solved the problem.

No more cursor and no more parameters that I don't really need...


		SELECT  a.assignedID,
			b.Manufacturer,
			a.modelNumber,
			a.serialNumber,
			e.equipmentType,
			a.description,
			c.Location,
			d.Status,
			a.modifiedDate,
			a.modifiedBy,
			a.Notes,
			a.Picture,			
			f.LabName,
			a.calibrationRequired,
			a.calDate,
			a.CalDueDate,
			a.assetNumber
					

		FROM 	tblEquipments a, 
			tblManufacturers b, 
			tblLocation c, 
			tblStatus d, 
			tblEquipment_Type e,
			tblLabs f

		WHERE a.ID = @id
			AND a.manufacturer = b.manufacturerID
			AND a.location = c.locationID
			AND a.Status = d.statusID
			AND a.EquipmentType = e.ID
			AND f.ID = a.calLab

Then, I simply filled a dataset from the code behind and all is good again!

thanks everyone for the help!

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 13:01:46

You're welcome.
Please also take this as an opportunity to learn ANSI syntax of T-SQL.

SELECT		a.assignedID,
		b.Manufacturer,
		a.modelNumber,
		a.serialNumber,
		e.equipmentType,
		a.description,
		c.Location,
		d.Status,
		a.modifiedDate,
		a.modifiedBy,
		a.Notes,
		a.Picture,			
		f.LabName,
		a.calibrationRequired,
		a.calDate,
		a.CalDueDate,
		a.assetNumber
FROM		tblEquipments a
INNER JOIN	tblManufacturers b ON b.manufacturerID = a.manufacturer
INNER JOIN	tblLocation c ON c.locationID = a.location
INNER JOIN	tblStatus d ON d.statusID = a.Status
INNER JOIN	tblEquipment_Type e ON e.ID = a.EquipmentType
INNER JOIN	tblLabs f ON f.ID = a.calLab
WHERE		a.ID = @id

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources