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(Conve rt(datetim e,timestam p),'') 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=@c ity where UserID =@UserID and convert(datetime,timestamp ) = @oldtimestamp
OR isnull(convert(datetime,ti mestamp,'' )) = @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_u pdate_user ", iOpenDb.openconnection())
mCommand.CommandType = CommandType.StoredProcedur e
'Add Params to the Command collection
With mCommand.Parameters
.Add("@Return_value", SqlDbType.Int, 4)
.add("@UserID",sqldbtype.i nt)
.Add("@UserName", SqlDbType.VarChar, 40)
.Add("@city", sqldbtype.varchar,40)
.add("@oldtimestmap,sqldbt ype.dateti me)
End With
'Sepcify the parameters values to be passed to SP
With mCommand
.Parameters("@UserID").Val ue = USerID
.parameters("@Username").v alue = UserName
.parameters("city").value = city
.parameters("@oldtimestamp ") = oldtimestamp
End With
With mCommand
.Parameters("@Return_value ").Directi on = ParameterDirection.ReturnV alue
.Parameters("@UserName").D irection = ParameterDirection.Input
.Parameters("@City").Direc tion = ParameterDirection.intput
.parameters("@UserID").dir ection = parameterdirection.input
.parameters("@oldtimestamp ") =parameterdirection.input
End With
drObject = mCommand.ExecuteReader
valueReturned as integer = mCommand.Parameters("@Retu rn_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
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(Conve
UpdateUser(1,Chris,tustin,
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=@c
OR isnull(convert(datetime,ti
(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_u
mCommand.CommandType = CommandType.StoredProcedur
'Add Params to the Command collection
With mCommand.Parameters
.Add("@Return_value", SqlDbType.Int, 4)
.add("@UserID",sqldbtype.i
.Add("@UserName", SqlDbType.VarChar, 40)
.Add("@city", sqldbtype.varchar,40)
.add("@oldtimestmap,sqldbt
End With
'Sepcify the parameters values to be passed to SP
With mCommand
.Parameters("@UserID").Val
.parameters("@Username").v
.parameters("city").value = city
.parameters("@oldtimestamp
End With
With mCommand
.Parameters("@Return_value
.Parameters("@UserName").D
.Parameters("@City").Direc
.parameters("@UserID").dir
.parameters("@oldtimestamp
End With
drObject = mCommand.ExecuteReader
valueReturned as integer = mCommand.Parameters("@Retu
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,ti mestamp,'' )) = @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???
I just used this statement to show you i use eitherone of the statements ie convert(datetime,timestamp
OR
isnull(convert(datetime,ti
)
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.
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.
ASKER
yes got u thanks for that
First you dont need to check if timestamp column is null. It can't be.
so you can try:
select UserName,city,Convert(date
and
Here it is better to convert date time to timestamp, so you will have:
update UserInfo set UserName=@UserName,city=@c