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?
Kevin SmithAsked:
Who is Participating?
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.