Date problem

Hi,
This code below in VB.net , delete a record in the database next day from inserting it to DB, for example:
If I add new record today 14-7-2005, and tommorow 15-7-2005 open to view the records available, i will not find it this record because it was deleted.

PrivateSub checkdate()

Dim ssqlAsString

Dim start_dateAsDate

Dim updcmdAs SqlClient.SqlCommand

mysqladap =New SqlClient.SqlDataAdapter(" select start_date from auction ", mySqlConn)

If Today.Date > start_dateThen

ssql = "delete auction where start_date < '" & Today.Date & "'"

updcmd =New SqlClient.SqlCommand(ssql, mySqlConn)

updcmd.ExecuteNonQuery()

EndIf

EndSub

BUTI want to change that code in a way that the record will be deleted after 3 days from the date it was inserted, that means the period for each record to stay in the DB is 3 days and after that will be deleted , is that possible ?? if yes how can it be changed??
Regards

I think you ought to insert a timestamp to record the datetime it was inserted (eg TSINSERT).
After this, you can delete all the records older than three days:
"DELETE FROM TABLE WHERE TSINSERT < " & Now.AddDays(-3).ToString
Another thing which you might do is to schedule a job on the database to automatically do the cleaning for you.
(but i have no experience in that so far)

Date Insertion problem

I am using BLL for inserting my data. I have some date filed and I have declare that field in BLL as date only. But still it is giving me error string in wrong format. My database is in SQL Server2005

can u give me a code, how you are declared date in BLL and how you are assining the values to that date variable in BLL, it may be your date variable string is not in proper format.

|||

================================Codes in BLL =============================================================================

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert,True)> _

PublicFunction AddTempEmployee(ByVal DDKSFileNoAsString,ByVal firstNameAsString,ByVal middleNameAsString,ByVal lastNameAsString, _

ByVal dateOfBirthAsDate,ByVal maritualStatusAsInteger,ByVal nationalityAsString,ByVal passportNumberAsString, _

ByVal passportIssuePlaceAsString,ByVal passportIssueDateAsDate,ByVal passportExpiryDateAsDate,ByVal civilIDNumberAsString, _

ByVal civilIDExpiryDateAsDate,ByVal residenceNumberAsString,ByVal homePhoneNumberAsString, _

ByVal alternatePhoneNumberAsString,ByVal kuwaitHomeAddressAsString)AsBoolean

Dim tempEmployeesAsNew DDKS.TempEmployeeDataTableDim tempEmployeeAs DDKS.TempEmployeeRow = tempEmployees.NewTempEmployeeRow

tempEmployee.DDKSFileNo = DDKSFileNo

tempEmployee.FirstName = firstName

tempEmployee.MiddleName = middleName

tempEmployee.LastName = lastName

tempEmployee.DateOfBirth = dateOfBirth

tempEmployee.MaritualStatus = maritualStatus

tempEmployee.Nationality = nationality

tempEmployee.PassportNumber = passportNumber

tempEmployee.PassportIssueDate = passportIssueDate

tempEmployee.PassportExpiryDate = passportExpiryDate

tempEmployee.CivilIDNumber = civilIDNumber

tempEmployee.CivilIDExpiryDate = civilIDExpiryDate

tempEmployee.ResidenceNumber = residenceNumber

tempEmployee.HomePhoneNumber = homePhoneNumber

tempEmployee.AlternatePhoneNumber = alternatePhoneNumber

tempEmployee.KuwaitHomeAddress = kuwaitHomeAddress

tempEmployees.AddTempEmployeeRow(tempEmployee)

Dim rowAffectedAsInteger = Adapter.Update(tempEmployees)

Return 1

EndFunction

========================================= Ends here ===============================================================================

===============================Codes in aspx file==========================================================================================================

<tdstyle="width:150px; height: 27px;"><cc1:MaskedTextBoxID="txDateOfBirth"runat="server"Text='<%# Bind("DateOfBirth", "{0:d}") %>'Mask="99-99-9999"ReadOnly="True"></cc1:MaskedTextBox>

=====================================ends here=====================================================================================================

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

DDKsFileNo String
Firstname String
MiddleName String
LastName String
DateofBirth Date
MaritualStatus Integer (I think this is Boolen or bit)
nationality String
PassportNumber String
PassportIssuePlace String
PassportIssueDate Date
PassportExpiryDate Date
CivilIDnumber string
CivilIDExpiryDate Date
residenceNumber String
HomePhoneNumber String
alternatePhoneNumber string
kuwaitHomeAddress string

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

DDKsFileNo String
Firstname String
MiddleName String
LastName String
DateofBirth Date
MaritualStatus Integer
nationality String
PassportNumber String
PassportIssuePlace String
PassportIssueDate Date
PassportExpiryDate Date
CivilIDnumber string
CivilIDExpiryDate Date
residenceNumber String
HomePhoneNumber String
alternatePhoneNumber string
kuwaitHomeAddress string

|||

chk whether you are passing correct values to correct varibale,,

this is not a solution, but sometime we do small mistakes,

chk whether ur passing string to string variables, date to date varibles and integer to integer, and also chk the ur datatypes of the columns for those fields.

DDKsFileNo String
Firstname String
MiddleName String
LastName String
DateofBirth Date
MaritualStatus Integer
nationality String
PassportNumber String
PassportIssuePlace String
PassportIssueDate Date
PassportExpiryDate Date
CivilIDnumber string
CivilIDExpiryDate Date
residenceNumber String
HomePhoneNumber String
alternatePhoneNumber string
kuwaitHomeAddress string

|||

My problem got caught it something like i was using "-" as seprator in application and my regional setting was using "/" so i change same in my application and start working.

Thanx for help

date insert problem

hi...

my form has a text box which displays system date.

i am inserting date into MS SQL Server from this date textbox.

but it displays me error..

String was not recognized as a valid DateTime.

Line 154: myCommand1.ExecuteNonQuery()

i have written code as

myCommand1.Parameters.Add(New SqlParameter("@.date", SqlDbType.DateTime, 8))

myCommand1.Parameters("@.date").Value = FormatDateTime(datetxt.Text, DateFormat.GeneralDate)

and also tried to change date format with many other ways.

how should i solve this problem?

i also want to take time form a user with the help of web form and want to store it in other field called 'timein' and 'timeout'....

FormatDateTime returns a string. Use DateTime.Parse instead to create a valid datetime, and assign the datetime object parsed. Alternatively, you can use Convert.ToDateTime, but you get an exception for invalid dates. Tie your textbox in to a CompareValidator to ensure valid dates from the browser. I recommend turning off client validation for date validation as it does not validate medium date (e.g. 22-Apr-2007) correctly|||

Heh, who the hell ever types 22-Apr-2007? Personally, I wouldn't consider it valid input to begin with.

Since you have already stated that your sqldbtype is a datetime, just shove the string into the parameter.

EX:

cmd.parameter("@.MyDateTime).value=MyDateString

That of course assumes that you have already validated it as a valid date/time string format. Just make sure that you validate it both client and server side. Too many people I know drop the validators down on the client, then forget to wrap their "save" in a If page.IsValid(), and it works... Until a hacker comes along and posts some invalid data to the server.

Oh, and I would drop off the size parameter on the sqlparameter. Not sure if 8 is even the correct size for a datetime, but it's ignored anyway since datetime is a fixed size.

|||

The reason I suggest the use of medium date format is this. I've workd for a number of world-wide companies over many years, often on large projects. Hundreds of thousands of pounds have been wasted by developers and databases engineers, simply because of a US/UK date confusion, or forgetting to set the date locale to UK, then, some months later, finding a database full of data that is invalid, then having to fix it, and having systems off-line.

Medium date also still works in the local language, if you set the locale of a web site (e.g. Norway).

Developers should also be listening to business, who increasing, and in my view, rightly, want medium format. The whole of the rest of the world does not live in the US, that is why our web sites should be locale aware

Software development is also a team effort. It isn't down to the developer alone to ensure a web-site isn't hacked. Architects, Managers and Testers are all there to make it happen (safely).

As always, these are my opinions and suggestions, soI don't expect everyone to like or agree with them.

Happy coding all...

|||

That's all well and good, and I agree that applications should be built with globalization in mind, but that really has nothing to do with medium dates. We don't use them in the US, and they aren't used anywhere else in the world except for geeky tech documents. If you want to use a truly international standard, then use the ISO format (YYYY-MM-DD). It's the same format in every culture.

As for having incorrect dates in your database, that's why you should always use the datetime datatype. The value is the value no matter what culture you are in, infact I normally store all date/times in a database based on UTC. That way no matter where you are in the world, I can tell you what time and date a specific event occurred localized in your specific date format, and give you the time relative to your timezone.

Now, it may be in your company, that they have decided that medium format is best, but I can say that is an oddity, and not a rule. There is a standard date format, it's the ISO format that was approved by the international standards organization, and when people need a format that they don't want translated to their native culture's format, that is the one that should be used. After all, 01-Apr-2006 isn't correct for any other place but the US. They don't have "April" let alone "Apr" in other countries.

|||

More business documents in the UK are using Medium and even Long Dates than previously. You don't see YYYY-MM-DD used in the UK as most people would find it geeky (whatever that means). In my software I generally let the user override their locale anyway, and set a date format for the web site based on ISO8601, ISO, UK/US, etc.

Medium and Long dates do correctly translate into the local language. E.g. I just tried today's (long format - since Med format displays the same result) date on a simple web page, which returned 12-febrero-2006 when I changed the culture from UK English to international Spanish (es-ES).

BTW., It wasn't my database or even my company that had the incorrect dates. I spent 15 years contracting around the UK, working for various major companies. What I observed (and sometimes got asked for advice on) were issues where UK formatted dates (parsed as text using dd/mm/yy) were stored in a database that was assuming US format since the default language had never been changed. Eventually the systems broke, which gave rise to many issues and fixing. What I advised is this. If they had used medium format from the start, the issue would not have arose, because the text would have been corretcly converted into the underlying datetime type.

I am sure we can disagree long into the night about date formats. However, it's a minor issue, and you are correct to raise the point that storing dates in the underlying format is the correct way to go.