Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Deleting all NTEXT to NULL

    I am using an Access frontend for a SQL backend. I have various text boxes using NTEXT for their type. (I use NTEXT because NVARCHAR(MAX) does not work as according to this article You receive a "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect" exception when using NVarchar parameters with Sqlclient). I can insert and update to these boxes just fine due to my trigger that I wrote.

    However when I try to delete ALL data from the box IE make it NULL I receive:

    "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3(""): Data type 0xE7 has an invalid data length or metadata length. (#8016)"

    Deleting some of the data as long as there is at least one character will allow the form to save correctly.

    Is there something I can do to make this function as I need to? It doesn't seem like a big deal but there are times we need to delete these notes to either start over or because teachers will sometimes edit the wrong students notes etc.

    I've already tried editing my trigger to allow for this by using a CASE statement in the WHERE clause but SQL gives this error:

    "The data types ntext and ntext are incompatible in the equal to operator."

    This is one of the codes in question.

    Code:
    	UPDATE dbo.tbl_student_record
    	SET  EMIS_ID = inserted.EMIS_ID, Last = inserted.Last, First = inserted.First, [Date of Birth] = inserted.[Date of Birth], M = inserted.M, append = inserted.append, Returning = inserted.Returning
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	AND dbo.tbl_student_record.Office_Notes = CASE WHEN inserted.Office_Notes IS NULL THEN '' ELSE inserted.Office_Notes END
    Any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have your MS-Access code set the attrribute to Nothing instead of to the empty string. The value of Nothing in MS-Access is the same as the NULL value in SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2009
    Posts
    47
    I don't understand what you mean. Could you explain a little more what would be set to "nothing"?

    Do you mean make another query in Access that is used if the text box is empty?

    Thanks for your help.

  4. #4
    Join Date
    Jul 2009
    Posts
    47
    OK well I got it to work correctly. Apparently I had accidentally created an instead of insert trigger as well as an instead of update and that was messing everything up.

    Of course I have hit another problem. When I try to edit the fields, I am editing every student's field at the same time. I mean everyone's field updates to the same SSID, Teacher_Notes, etc. The only things that stay as they should are any fields kept in student record (EMIS_ID, Office_Notes, Last, First, etc).

    Here is my update trigger:

    Code:
    USE [Backend]
    GO
    /****** Object:  Trigger [dbo].[trig_student_record_update]    Script Date: 06/17/2010 12:21:22 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER TRIGGER [dbo].[trig_student_record_update]
       ON  [dbo].[Q_student_record]
       INSTEAD OF UPDATE
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for trigger here
    	UPDATE dbo.tbl_student_record
    	SET  EMIS_ID = inserted.EMIS_ID, Last = inserted.Last, First = inserted.First, [Date of Birth] = inserted.[Date of Birth], M = inserted.M, append = inserted.append, Office_Notes = inserted.Office_Notes, Returning = inserted.Returning
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	
    
    	UPDATE dbo.tbl_Attendance
    	SET [Date Withdrawn] = inserted.[Date Withdrawn], [Date Started] = inserted.[Date Started], Enter_2008 = inserted.Enter_2008
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	
    	
    	UPDATE dbo.tbl_school_info
    	SET JOG_Info = inserted.JOG_Info, Teacher_Notes = inserted.Teacher_Notes, Gr = inserted.Gr, PCS_Info = inserted.PCS_Info, centerID = inserted.centerID, ID_staff = inserted.ID_staff, SessionID = inserted.SessionID
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	
    	
    	UPDATE dbo.tbl_sped
    	SET  Retun_2008 = inserted.Retun_2008
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	
    		
    	UPDATE dbo.tbl_state_info
    	SET SSID = inserted.SSID, [Economic dis] = inserted.[Economic dis], EthnityID = inserted.EthnityID, [Gender ID] = inserted.[Gender ID]
    	FROM inserted, dbo.tbl_student_record
    	WHERE dbo.tbl_student_record.EMIS_ID = inserted.EMIS_ID	
    		
    END
    I'm sure I have something messed up but I can't figure out what it is.

  5. #5
    Join Date
    Jul 2009
    Posts
    47
    Figured it out. I just needed to add the EMIS_ID of each table and add it to the WHERE clause, instead of using dbo.tbl_student_record for them all.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •