Professional Web Applications Themes

ADO.NET with Instead-Of-Insert Triggers, sets NULLs - Microsoft SQL / MS SQL Server

Hi, This has confused me and I have not been able to figure out what is happening. I have a simple application that is developed in Visual Studio .NET and I used the wizard template to create a SDI App with ODBC support (no file support). The application allows a user to insert records to a table in a Microsoft SQL Server database through a front end. Now the catch is the data is inserted through a View. I'm able to get the insert to work when using an Instead-Of-Insert trigger and defining the View with the VIEW_METADATA attribute. The ...

  1. #1

    Default ADO.NET with Instead-Of-Insert Triggers, sets NULLs

    Hi,

    This has confused me and I have not been able to figure out what is
    happening. I have a simple application that is developed in Visual
    Studio .NET and I used the wizard template to create a SDI App with
    ODBC support (no file support). The application allows a user to
    insert records to a table in a Microsoft SQL Server database through a
    front end.

    Now the catch is the data is inserted through a View. I'm able to get
    the insert to work when using an Instead-Of-Insert trigger and
    defining the View with the VIEW_METADATA attribute.

    The following article describes this issue:
    [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q304096[/url]

    I have been able to determine that the trigger fires and data is
    handled and processed. The problem is the View has a column that is
    "massaged" and that column is always set to NULL. It shouldn't be
    NULL; it should have a value based on the user-input. The
    instead-of-trigger is supposed to take the value passed by the
    application and use the value returned by a function to properly store
    it into the base table. My assumption is that there is a problem with
    the RFX (Record Field Exchange) process. Stepping through the VC++
    code, the data is set properly in the DoFieldExchange method.

    I expected the value to be passed into the [inserted] table used by
    the trigger. But the data in the [inserted] table is NULL. I had
    placed a debug statement placed inside the trigger to print out the
    the values that were stored in the [inserted] table that the trigger
    uses. The value that is received for the column that needs to be
    "massaged" is always NULL, even though I watched it get set properly
    on the client application side. The values for the other columns in
    the view are fine. Only the columns that must go through a function
    are passed a NULL.

    Can anyone shed some light on this issue?

    -Brian
    Brian Cook Guest

  2. #2

    Default Re: ADO.NET with Instead-Of-Insert Triggers, sets NULLs

    You should create your instead of trigger on the Base Table instead of on
    the View.

    "Brian Cook" <briancook0734> wrote in message
    news:1a94d309.0307030817.71a382beposting.google.c om...
    > Hi,
    >
    > This has confused me and I have not been able to figure out what is
    > happening. I have a simple application that is developed in Visual
    > Studio .NET and I used the wizard template to create a SDI App with
    > ODBC support (no file support). The application allows a user to
    > insert records to a table in a Microsoft SQL Server database through a
    > front end.
    >
    > Now the catch is the data is inserted through a View. I'm able to get
    > the insert to work when using an Instead-Of-Insert trigger and
    > defining the View with the VIEW_METADATA attribute.
    >
    > The following article describes this issue:
    > [url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q304096[/url]
    >
    > I have been able to determine that the trigger fires and data is
    > handled and processed. The problem is the View has a column that is
    > "massaged" and that column is always set to NULL. It shouldn't be
    > NULL; it should have a value based on the user-input. The
    > instead-of-trigger is supposed to take the value passed by the
    > application and use the value returned by a function to properly store
    > it into the base table. My assumption is that there is a problem with
    > the RFX (Record Field Exchange) process. Stepping through the VC++
    > code, the data is set properly in the DoFieldExchange method.
    >
    > I expected the value to be passed into the [inserted] table used by
    > the trigger. But the data in the [inserted] table is NULL. I had
    > placed a debug statement placed inside the trigger to print out the
    > the values that were stored in the [inserted] table that the trigger
    > uses. The value that is received for the column that needs to be
    > "massaged" is always NULL, even though I watched it get set properly
    > on the client application side. The values for the other columns in
    > the view are fine. Only the columns that must go through a function
    > are passed a NULL.
    >
    > Can anyone shed some light on this issue?
    >
    > -Brian

    Nikhil Patel Guest

Similar Threads

  1. Why did Photoshop CS quit suddenly in the middle of making sets? Action sets
    By blackxacto@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 1
    Last Post: May 4th, 04:01 AM
  2. CS quit suddenly in the middle of making Action sets Action sets,
    By blackxacto@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 3
    Last Post: May 2nd, 06:06 AM
  3. dealing with nulls....
    By Ken Schaefer in forum ASP
    Replies: 3
    Last Post: July 7th, 05:49 AM
  4. Replies: 6
    Last Post: January 3rd, 10:51 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139