Giving a Field in a view a definition. RRS feed

  • Question

  • I have an instance where I have a view.  One of its fields is a Calculated Value.  I need to use that field in ADO maybe assign it a new value.  I keep getting a Multiple Step Generation Error. 


    Is there a way in SQL to maybe give a field properties to mimic something in a table?


    Just looking for a quick fix for a poorly designed line of code until i can do a full release of code to fix the whole situation.

    Let me know




    Thursday, August 23, 2007 5:01 PM

All replies

  • If what you are asking if you can assign a new value to a computed column in a view, the answer to that question is no.  If you need to alter the attributes of this computed column, that is readily doable by using cast or naming the column as needed.  I am not sure what else you are getting at.


    If you really need to change the value of a computed column then you must update the columns on which the computed column is based.

    Thursday, August 23, 2007 5:06 PM
  • Please post your view code and a bit more detailed explanation.  Using instead of triggers you can do a lot of things that aren't "naturally" possible.  It just depends on what you mean by assigning it a new value means.

    Thursday, August 23, 2007 5:11 PM
  • Ok this is a sample of SQL that replicates my issue.....


    Code Snippet


    DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,


    dbo.tbSubdivisionFollowUp SubdivFU

    INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent

    INNER JOIN dbo.tbClientDate ClientDate ON ClientDate.SubdivisionID =Subdiv.ID

    AND ClientDate.DateTypeID = TE.DateTypeID

    AND (

    (ClientDate.ClientRankID = SubdivFU.ClientRankID AND TE.UseRank = 1)

    OR (TE.UseRank = 0)





    This is my a code block tha replicates my problem...

    Code Snippet


        Dim x As ADODB.Recordset
        Dim vFields As Variant
        Dim vFilter As Variant
        Dim cError As String

        Set mobjDal = New SSnet_DAL.Functions
        mobjDal.SetINIFile "64Server"

        Set x = New ADODB.Recordset
        x.CursorLocation = adUseClient
        x.LockType = adLockBatchOptimistic
        'This code basically does a select * from the above query where 1=1  and returns me the data... 

    vFields = Array("*")
    vFilter = Array("1=1")
    cError = mobjDal.GetData("vuFollowUpTriggerAll", x, vFields, vFilter)



        x("DueDate").Value = CDate("8/22/2007")
        If cError <> "" Then Err.Raise 100, , cError
        If x.EOF = False Then
        End If



    THe line "x("DueDate").Value = CDate("8/22/2007")" returns me a multiple step generation.


    The problem is that since it is a calcuated value it doesn't have any properties in ADO.  This occured when we upgraded to SQL 2005. 


    Any thoughts on how to get this to have the properties of a date field.  I tried conveting.  I tried casting and nothing..


    Any ideas are appreciated ..



    Thursday, August 23, 2007 5:24 PM
  • Moved to the data access forum.  Perhaps we have more luck there.


    Saturday, August 25, 2007 5:50 AM
  • The problem is that you're trying to assign a value to a recordset record.


    x("DueDate").Value = CDate("8/22/2007") <--This is not valid code.


    x("DueDate").Value already has a value and it's read-only.


    I'm not sure what the goal of this line is.


    Typically you would assign the value of the record to a variable:


    myVar = x("DueDate").Value



    Thursday, October 25, 2007 9:15 PM
  • Also, it does have a date value and you can access through the recordset index instead of the fieldname


    myVar = x(0).Value



    Thursday, October 25, 2007 9:18 PM