Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

How do I get my insert code to force a time in a datetime field?

I have a form that inserts data into a ms sql database from an asp page.  One of the fields is a date time field and it does fine, but I want it to always do the time as 8am instead of the default 12am.  How do I do that in relation to the insert code that Dreamweaver throws in?
0
Kevin Smith
Asked:
Kevin Smith
  • 6
  • 3
  • 3
  • +2
2 Solutions
 
navaidfarooquiCommented:
check your ASP code it must be throwing the 12 am value there, if it doesn't try force it to throw 8AM...
0
 
Kevin SmithAuthor Commented:
Yes, that's what I would like it to do.
0
 
GundogTrainerCommented:
Can you post the default dreamweaver code, it may just need to be appended with
& "08:00" as a datetime field with no time will normaly just assume midnight if its not stated (00:00.000)
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
navaidfarooquiCommented:
oh so you want me to tell u the code... can u post your ASP code here?
0
 
Kevin SmithAuthor Commented:

Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_newhire_STRING
    MM_editCmd.CommandText = "UPDATE dbo.tbl_newhire SET namefirst = ?, namelast = ?, [position] = ?, division = ?, last4ssn = ?, startdate = ?, reportsto = ?, manager = ?, assignedbuddy = ?, location = ?, inform = ?, [forms] = ?, infotopayroll = ?, businesscards = ?, hrnotes = ?, office = ?, deskworkarea = ?, keys = ?, badge = ?, facnotes = ?, neededtrainging = ?, manuals = ?, protequipment = ?, safenotes = ?, desktop = ?, laptop = ?, dockingstationg = ?, keyboardmouse = ?, rightsaccess = ?, email = ?, specialtysoftware = ?, notes = ?, blackberry = ?, es400 = ?, mobiletecuser = ?, deskphone = ?, rsatoken = ?, itnotes = ?, vehicle = ?, dmvreport = ?, carallowance = ?, personalcareuse = ?, fleetnotes = ?, othernotes = ?, signoffhr = ?, signofffac = ?, signoffsafe = ?, signoffit = ?, signofffleet = ?, completednewhire = ?, offerlettersenton = ?, afterhours = ?, offerletteraccepted = ?, middlename = ?, knownasfirst = ?, knownaslast = ?, hr_referral = ? WHERE hireID = ?" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 75, Request.Form("namefirst")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 75, Request.Form("namelast")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 50, Request.Form("position")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 50, Request.Form("division")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 50, Request.Form("last4ssn")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 133, 1, -1, MM_IIF(Request.Form("startdate"), Request.Form("startdate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 5, 1, -1, MM_IIF(Request.Form("reportsto"), Request.Form("reportsto"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param10", 5, 1, -1, MM_IIF(Request.Form("manager"), Request.Form("manager"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param11", 5, 1, -1, MM_IIF(Request.Form("assignedbuddy"), Request.Form("assignedbuddy"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param12", 202, 1, 50, Request.Form("location")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param14", 5, 1, -1, MM_IIF(Request.Form("inform"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param15", 5, 1, -1, MM_IIF(Request.Form("forms"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param16", 5, 1, -1, MM_IIF(Request.Form("infotopayroll"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param17", 5, 1, -1, MM_IIF(Request.Form("businesscards"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param18", 202, 1, -1, Request.Form("hrnotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param19", 5, 1, -1, MM_IIF(Request.Form("office"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param20", 5, 1, -1, MM_IIF(Request.Form("deskworkarea"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param21", 5, 1, -1, MM_IIF(Request.Form("keys"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param22", 5, 1, -1, MM_IIF(Request.Form("badge"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param23", 202, 1, -1, Request.Form("facnotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param24", 5, 1, -1, MM_IIF(Request.Form("neededtrainging"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param25", 5, 1, -1, MM_IIF(Request.Form("manuals"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param26", 5, 1, -1, MM_IIF(Request.Form("protequipment"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param27", 202, 1, -1, Request.Form("safenotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param28", 5, 1, -1, MM_IIF(Request.Form("desktop"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param29", 5, 1, -1, MM_IIF(Request.Form("laptop"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param30", 5, 1, -1, MM_IIF(Request.Form("dockingstationg"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param31", 5, 1, -1, MM_IIF(Request.Form("keyboardmouse"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param32", 5, 1, -1, MM_IIF(Request.Form("rightsaccess"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param33", 5, 1, -1, MM_IIF(Request.Form("email"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param34", 5, 1, -1, MM_IIF(Request.Form("specialtysoftware"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param35", 202, 1, -1, Request.Form("notes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param36", 5, 1, -1, MM_IIF(Request.Form("blackberry"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param37", 5, 1, -1, MM_IIF(Request.Form("es400"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param38", 5, 1, -1, MM_IIF(Request.Form("mobiletecuser"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param39", 5, 1, -1, MM_IIF(Request.Form("deskphone"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param40", 5, 1, -1, MM_IIF(Request.Form("rsatoken"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param41", 202, 1, -1, Request.Form("itnotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param42", 5, 1, -1, MM_IIF(Request.Form("vehicle"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param43", 5, 1, -1, MM_IIF(Request.Form("dmvreport"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param44", 5, 1, -1, MM_IIF(Request.Form("carallowance"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param45", 5, 1, -1, MM_IIF(Request.Form("personalcareuse"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param46", 202, 1, -1, Request.Form("fleetnotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param47", 202, 1, -1, Request.Form("othernotes")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param49", 5, 1, -1, MM_IIF(Request.Form("signoffhr"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param51", 5, 1, -1, MM_IIF(Request.Form("signofffac"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param53", 5, 1, -1, MM_IIF(Request.Form("signoffsafe"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param55", 5, 1, -1, MM_IIF(Request.Form("signoffit"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param57", 5, 1, -1, MM_IIF(Request.Form("signofffleet"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param59", 5, 1, -1, MM_IIF(Request.Form("completednewhire"), 1, 0)) ' adDouble
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param62", 133, 1, -1, MM_IIF(Request.Form("offerlettersenton"), Request.Form("offerlettersenton"), null)) ' adDBTimeStamp
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param63", 5, 1, -1, MM_IIF(Request.Form("afterhours"), 1, 0)) ' adDouble
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param64", 133, 1, -1, MM_IIF(Request.Form("offerletteraccepted"), Request.Form("offerletteraccepted"), null)) ' adDBTimeStamp
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param65", 202, 1, 75, Request.Form("middlename")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param66", 202, 1, 75, Request.Form("knownasfirst")) ' adVarWChar
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param67", 202, 1, 75, Request.Form("knownaslast")) ' adVarWChar
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param68", 5, 1, -1, MM_IIF(Request.Form("hr_referral"), 1, 0)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param69", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

Open in new window

0
 
GundogTrainerCommented:
Depends what Dreamweavers is inserting but something like this should work:

<%response.write(date() & "08:00:00")%>.
0
 
GundogTrainerCommented:
Sorry didnt see you had posted the code:

Could you confirm the field in question is the startdate:  in the line 12

Depending how it is formated you may need to append the hour to it or add 8 hours to the startdate if it is passing a full datetime field.

Append a time:
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 133, 1, -1, MM_IIF(Request.Form("startdate"), Request.Form("startdate") & " 08:00", null)) ' adDBTimeStamp

or

Add 8 hours to the existing datetime
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 133, 1, -1, MM_IIF(Request.Form("startdate"), dateadd(h,8,Request.Form("startdate")), null)) ' adDBTimeStamp
0
 
worthyking1Commented:
Replace line # 12 with these two lines instead:

TheStartDate = CStr(FormatDateTime(Request.Form("startdate"),2) & " 8:00:00 AM")
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 133, 1, -1,MM_IIF(TheStartDate, TheStartDate, null)) ' adDBTimeStamp

That will format the var FormatDateTime to a VB Short Date (MM/DD/YY) and manually add the time onto it in the correct format, and then pass it in as a parameter.
0
 
Kevin SmithAuthor Commented:
Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'FormatDateTime'

/newhire/nh_updateh.asp, line 59  (line 59 is "TheStartDate...")
0
 
worthyking1Commented:
Makes no sense. FormatDateTime takes two properties: the date and the date type (integer), unless your value is null.

Put this on line 58 and see what the value of your form element is:

Response.Write "startdate: " & Request.Form("startdate") & "<br>"

It should be a datetime value. If not then you need to validate you rform better to ensure a correct value is entered. If you're allowing it to be blank then you'll need to modify line 59 to this (to account for a blank value):

If TheStartDate  <> "" then TheStartDate = CStr(FormatDateTime(Request.Form("startdate"),2) & " 8:00:00 AM") Else TheStartDate  = CStr(FormatDateTime(Date(NOW),2) &  " 8:00:00 AM")
0
 
Anthony PerkinsCommented:
>> One of the fields is a date time field and it does fine, but I want it to always do the time as 8am instead of the default 12am.  <<
That would be because you are not passing the correct data type
adDBTimeStamp = 135 and not 133.

In othere words use 135.


0
 
Kevin SmithAuthor Commented:
that still does it as 12-12am...
0
 
Kevin SmithAuthor Commented:
wait...nope...was not on my test page.  I'm still getting

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'FormatDateTime'

/newhire/nh_updateH.asp, line 59     (line 59 is "TheStartDate...")
0
 
worthyking1Commented:
Did you read my post above?  Didn't get any response from you on it.

>>>>>>>>>
Makes no sense. FormatDateTime takes two properties: the date and the date type (integer), unless your value is null.

Put this on line 58 and see what the value of your form element is:

Response.Write "startdate: " & Request.Form("startdate") & "<br>"

It should be a datetime value. If not then you need to validate you rform better to ensure a correct value is entered. If you're allowing it to be blank then you'll need to modify line 59 to this (to account for a blank value):

If TheStartDate  <> "" then TheStartDate = CStr(FormatDateTime(Request.Form("startdate"),2) & " 8:00:00 AM") Else TheStartDate  = CStr(FormatDateTime(Date(NOW),2) &  " 8:00:00 AM")
<<<<<<<<<<<<<
0
 
Anthony PerkinsCommented:
>>I'm still getting Microsoft VBScript runtime error '800a01c2' <<
Then obviously that is unrelated to your original question.

0
 
Kevin SmithAuthor Commented:
If I type in (example) 5/11/2011 08:00:00 it does it correctly.  The response write just says whatever I type in the box whether it's valid or not.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now