Link to home
Start Free TrialLog in
Avatar of sainavya1215
sainavya1215

asked on

timestamp conversion and comparision in where clause

I retireve a row with timestamp . In the select statement itself i convert the timestamp to datetime and Pass the datetime back to a stored proc for comparision for data concurrency.

i converted currenttimestamp to datetime and compare the oldtimestamp. but it doesnot work somehow . I added a error handler which returns -100
in my stored proc. there might be some data conversion problem

=============================================================================================================
I retrieve a row using timestamp using

select UserName,city,isnull(Convert(datetime,timestamp),'') as tmpstamp from userInfo where userID = @UserID

  UpdateUser(1,Chris,tustin,tmpstamp)





Table :  UserInfo
COLUMNS
==============
UserID        
UserName      varchar(40)
City                varchar(40)
TimeStamp     TimeStamp


create procedure sp_update_user
    @UserID int,
    @UserName varchar(40),
    @city varchar(40),
    @oldtimestamp datetime

as

begin

update UserInfo set UserName=@UserName,city=@city where UserID =@UserID  and convert(datetime,timestamp) = @oldtimestamp
                                                                                                                        OR  isnull(convert(datetime,timestamp,'')) = @oldtimestamp
                                                                                                                         (convert it to the same datatype)
     if @@error!=0
          begin
                return -100
           end
               else
                     if @@ROWCOUNT = 0
                          begin
                                return -101
                          end  
                                 else
                                       begin
                                              return -102
                                       end
end
======================================================================
public function updateUser(byval UserID as integer,UserName as string,City as string,oldtimestamp as datetime)
 Dim iOpenDb As New COpenDb
       
        Dim mCommand As SqlClient.SqlCommand
        Dim drObject As SqlClient.SqlDataReader

        Try
            mCommand = New SqlClient.SqlCommand("sp_update_user", iOpenDb.openconnection())
            mCommand.CommandType = CommandType.StoredProcedure
            'Add Params to the Command collection

            With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .add("@UserID",sqldbtype.int)
                .Add("@UserName", SqlDbType.VarChar, 40)
                .Add("@city", sqldbtype.varchar,40)
                .add("@oldtimestmap,sqldbtype.datetime)
            End With

            'Sepcify the parameters values to be passed to SP
            With mCommand
                .Parameters("@UserID").Value = USerID
                .parameters("@Username").value = UserName
                .parameters("city").value =  city
                .parameters("@oldtimestamp") = oldtimestamp

               
            End With

            With mCommand
                .Parameters("@Return_value").Direction = ParameterDirection.ReturnValue
                .Parameters("@UserName").Direction = ParameterDirection.Input
                .Parameters("@City").Direction = ParameterDirection.intput
                .parameters("@UserID").direction = parameterdirection.input
                .parameters("@oldtimestamp") =parameterdirection.input

            End With

            drObject = mCommand.ExecuteReader
            valueReturned as integer  = mCommand.Parameters("@Return_value").Value

            If valueReturned= -100 Then
                Throw New Exception("Error Occoured")
              elseif valuereturned = -101
                throw new exception("COncurrency violation some one has updated")
             else
                 throw new exception("Update successfull")
            End If

           
        Catch ex As Exception
            Err.Raise(vbObjectError + 6103, "", ex.Message)
     
        End Try


End function
Avatar of gsiric
gsiric

Hi

First you dont need to check if timestamp column is null. It can't be.

so you can try:

select UserName,city,Convert(datetime,timestamp) as tmpstamp from userInfo where userID = @UserID

and

Here it is better to convert date time to timestamp, so you will have:
update UserInfo set UserName=@UserName,city=@city where UserID =@UserID  and convert(TimeStamp, @oldtimestamp) = timestamp
ASKER CERTIFIED SOLUTION
Avatar of gsiric
gsiric

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sainavya1215

ASKER

hi Grisic,

I just used this statement to show you i use eitherone of the statements  ie  convert(datetime,timestamp) = @oldtimestamp
                                                                                                                        OR  
                                                                                                                        isnull(convert(datetime,timestamp,'')) = @oldtimestamp
                                                                                                                         )

The best i feel is convert into a integer during selecting record and updating the record like SELECT ISNULL(CONVERT(integer, timestamp), '')
    AS tmstamp
FROM table

This returned results for records as  801,802,803 (timestamp values) .........Wouldnt it be more easier than datetime conversion or others???
Hi,
You can convert to whatever you want, but must be sure that conversion will not truncate values.
Once again, I think that you don't need to check for NULL timestamp, because timestamp column cannot contain null values.

yes got u thanks for that