Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert DateTime value into SQL datetime value

Posted on 2006-05-11
10
Medium Priority
?
642 Views
Last Modified: 2008-01-09
Ok, I'm totally stuck on this one, here is what I'm trying to do.

I pull a record, and with it I pull the last modification time.  Users makes changes and when they go to save them, the system checks if the modification date has changed on that record or not, basically record locking.

So, I have a variable time_stamp that holds that datetime value from SQL server, but when I try to pass it back into the SQL as a parameter I get errors. I have tried  so many combinations that I'm stuck.  Here is the list:

combo 1:
Dim time_stamp As DateTime
sqlUpdateDR.Parameters.Add("@time_stamp_old", time_stamp)
ERROR: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

combo 2:
Dim time_stamp as String
sqlUpdateDR.Parameters.Add("@time_stamp_old", DateTime.Parse(time_stamp))
ERROR: String reference not set to an instance of a String. Parameter name: s

combo 3:
Dim time_stamp as String
sqlUpdateDR.Parameters.Add("@time_stamp_old", SqlDbType.DateTime, 4, Format(time_stamp, "MM/dd/yyyy hh:mm:ss tt"))
OR
sqlUpdateDR.Parameters.Add("@time_stamp_old", time_stamp)
ERROR: Prepared statement '(@DR_ID int,@transfer_type nvarchar(4000),@dr_reason nvarchar(40' expects parameter @time_stamp_old, which was not supplied.

I have even made a few attempts at declaring time_stamp as SqlType.sqldatetime and working wite that, but I can't do anything with that either.  

Any help would be much appreciated, I'm sure there is a way to pass a datetime value back into sql for comparison.

TK
0
Comment
Question by:tomasz_k
  • 5
  • 3
  • 2
10 Comments
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16661322
In your SQL Server database, what is the type of the field? Is it time_stamp or DateTime?

-Nauman.
0
 

Author Comment

by:tomasz_k
ID: 16661358
The field is a datetime field and yes, I'm running on SQL.  I have even tried a very basic T-SQL statement "SET @sqlMessage = @time_stamp_old" and I'm still getting those error message.  @sqlMessage is defined as an output parameter.
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16661477
I think you should rather handle this using Trigger rather than in the code. For the trigger you can write the following code:

CREATE TRIGGER [UPDATE_TABLE_NAME_TIME_STAMP] ON dbo.TABLE_NAME
FOR UPDATE
AS
UPDATE TABLE_NAME SET TIME_STAMP_FIELD=getDate()

-Nauman.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:wtconway
ID: 16661516
I would recommend throwing a timestamp field on the table. Using timestamp, you can use SQL keywords like TSEQUAL to make sure the timestamp matches. Timestamp is NOT a date or a time, no clue why SQL Server called it that. What it does is it serves as a postmark for that record. Each time it changes via UPDATE/INSERT, the timestamp will change. So if you pass the timestamp back and forth you can just have your UPDATE statement look like this:

UPDATE table
   SET blah=newblah
WHERE
   UniqueID=myid and
   TSEQUAL([tStamp],@tStamp) first param is the field in the database, second is your parameter. if they match, it updates.
0
 

Author Comment

by:tomasz_k
ID: 16661606
Guys, I know that I could use timestamp to keep track, but I want to kill 2 birds with one stone, get the timestamp and date modified.  In the end this should be just a simple parameter passing issue.  How can I pass a datetime value as a parameter in SQL?
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16661630
I do it the old school way. Let's say your proc would normally be called in QA like so:

execute spUpdateCustomer 'John','Doe','123-45-6789','2006/5/11 13:45:22'

last param is the last updated value that you've stored off in your class. I would simply send the exact formatted statement back to sql server using the OLEDB classes and not the SQL Server ones. But then again, I'm lazy and didn't feel like upgrading my classes when I realized they would work with the SQLOLEDB provider.

Basically, I don't use SQLParameters AT ALL.
0
 

Author Comment

by:tomasz_k
ID: 16661655
Yes, that works, but I'm passing 20 parameters, and I want to keep everything uniform :( this sucks.
0
 
LVL 25

Accepted Solution

by:
nauman_ahmed earned 2000 total points
ID: 16661864
Yes you can like the following:

SqlParameter sqlParam = new SqlParameter("@old_timestamp", SqlDbType.Timestamp);
sqlParam.Value = "timestamp_value";
sqlUpdateDR.Parameters.Add(sqlParam);

-Nauman.
0
 

Author Comment

by:tomasz_k
ID: 16661916
It's still not working. My parameter needs to be of type date time, here is my code (per your suggestions)

        Dim sqlParam As New SqlClient.SqlParameter
        sqlParam.DbType = DbType.DateTime
        sqlParam.ParameterName = "@time_stamp_old"
        sqlParam.Value = time_stamp
        sqlUpdateDR.Parameters.Add(sqlParam)

When I run this, I get the following error message:
Prepared statement '(@DR_ID int,@transfer_type nvarchar(4000),@dr_reason nvarchar(40' expects parameter @time_stamp_old, which was not supplied.
0
 

Author Comment

by:tomasz_k
ID: 16662495
Guys, the problem was on my side...don't ask...I pulled an 1d10t .... Thanks for your efforts.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question