Hi All,

How would I update a field with the sql server date/time?

Currently, I use something like:

 DECLARE @varUpdatedID  varchar(30),
    @varUpdatedDate datetime

SET @varUpdatedID = USER_NAME()
SET @varUpdatedDate = GETDATE()

UPDATE Assignment
SET     SectionNo = @parSectionNo, 
    Name = @parName, 
    Points = @parPoints, 
    Weight = @parWeight, 
    CategoryID = @parCategoryId, 
    DateDue = @parDateDue, 
    CimType = @parCimType,
    AssignmentDesc = @parAssignmentDesc, 
    Sequence = @parSequence,
    RequiredFlag = @parRequiredFlag, 
    Note = @parNote,
    Publish = @parPublish,
    UpdatedID= @varUpdatedID,
    UpdatedDate = @varUpdatedDate
WHERE 
        AssignmentId = @parAssignmentId 

Which use the GETDATE and USER_NAME functions.

So, how would I accomplish this without a sproc?

Thanks,

Fishy.

This is not yet supported. You also can't fake it with some low level code at the moment.

Since these fields are on every table I take it that I can only use llblgen to select data. Maybe I can use it to Insert/Delete.

Where is this enhancement in the queue?

Thanks,

Fishy.

Fishy wrote:

Since these fields are on every table I take it that I can only use llblgen to select data. Maybe I can use it to Insert/Delete.

Where is this enhancement in the queue?

In the next upgrade of the designer, which release is expected early 2005.

You could work around the requirement of your system of course, by passing in DateTime.Now for the date and for the username you can either use the windows user the app is under if you use integrated security, or read the userid from the connection string, as that's the same value.

Otis wrote:

You could work around the requirement of your system of course, by passing in DateTime.Now for the date and for the username you can either use the windows user the app is under if you use integrated security, or read the userid from the connection string, as that's the same value.

I suppose I could get a little elaborate and get the time off of sql server when I start up the app, then take the difference from DateTime.Now and use that to adjust the value of the DateTime.Now when assigning that value to the field.

or if the enhancement is early enough I'll just wait.

Thanks again,

Fishy.

I can't give an estimate release date, as the final functionality for the designer hasn't been finalized so I'm afraid it's a workaround for now , sorry.

You could use a sql server trigger to solve the problem.

-CT

I have altered my DBUtils template a few weeks ago - so that i could call UDF's. It is for selfservicing but i am sure you guys can Hack it a bit. - Maybe it could help you.

Here is a example of how i have used it:

return DBUtils.CallUDFunction("Decrypt", CreditCardPaymentEntity.CCNumber)

CreditCardPaymentEntity.CCNumber =  DBUtils.CallUDFunction("Encrypt",CCNum,DateTime.Now)

I will ask Frans to put it on the 3rdParty section on Monday.

Wayne, how do you call the function in ado.net? As an sqlserver function (not a proc) is not callable from a command, just from SQL, or am I mistaken?

Hi Frans,

I don't have the exact code with me at the momment - it is at work but this is what i do.

I wrap it in a SQL Select statement using DB_Command.CommandType = CommandType.Text - this will return a normal result set that you can put in a datatable - or you can try ExecuteScalar.

select dbo. FunctionName (param1, Param2)

So far i have not had any problems and it works fine.

Sounds the way to do it indeed, after I wrote the post I thought of something similar, however perhaps there was a more cleaner way )

Ok guys - Add-on for User Defined Functions is available from 3rd Party section!

ctadlock wrote:

You could use a sql server trigger to solve the problem.

-CT

That works out best for my situation. The following code is what I'll use

 CREATE TRIGGER UpdateTableNameTrigger ON TableName
 FOR Update
AS 
Update TableName
  SET LastUpdateDateTime = GETDATE
WHERE TableName.PK = Inserted.PK

Just in case someone else needs to do something like this.

Thanks,

Fishy