CeceJ Report post Posted 06/18/2004 06:17 PM I am trying to get a date out of a VBS program and write it to a Access database. in a long integer field then the result is 0. in a text field the result is 1.66333998669328E-04 in a date field the result is 12/30/1899 (short date format) my vbs program is below Dim vardate Dim vartime Dim varhour Dim varmin Dim int_min Dim int_year Dim int_month Dim int_day Dim int_hour Dim Date1 int_hour = $RV_Hour int_day = $RV_Date int_month = $RV_Month int_year = $RV_Year Select Case $RV_Minute ' the case statement below converts the minutes to the 15 minute interval minutes Case 0,1,2,3,4,5,6,7 varmin = "0" int_min = 0 Case 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 varmin = "15" int_min = 15 Case 23,24,25,26,27,28,29,30,31,32,33,34,35,36,37 varmin = "30" int_min = 30 Case 38,39,40,41,42,43,44,45,46,47,48,49,50,51,52 varmin = "45" int_min = 45 Case 53,54,55,56,57,58,59 varmin = "0" int_min = 0 int_hour = $RV_hour + 1 End Select If int_hour = 24 Then int_hour = 0 int_day = int_day + 1 End If Date1 = DateSerial(int_year,int_month,int_day) 'DateSerial is a built in VBS function - it adjusts to the new date if int_day has changed varhour = cstr(int_hour) varmin = cstr(int_min) ' will need to update the database with int_min, which is the adjusted minute amount; int_hour and Date1 wscript.echo varhour wscript.echo varmin set vg = CreateObject("VoiceGuide.CommandLink") vg.RvSet $RV_LINEID, "NewHour", varhour set vg = Nothing set vg = CreateObject("VoiceGuide.CommandLink") vg.RvSet $RV_LINEID, "NewDate", Date1 set vg = Nothing set vg = CreateObject("VoiceGuide.CommandLink") vg.RvSet $RV_LINEID, "NewMin", varmin set vg = Nothing The log is below ( i only included what I think is important) 124601.49 7 [RunVb] Running VB Script... 124601.49 7 RVreplace start (strlen>500) 124601.51 7 RVns [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[scriptsPath]{U:\My Documents\VoiceGuide\}[$RV_STARTTIME]{6/18/2004 12:45:37 PM}[$RV_DEVICEID]{7}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{9018697787}[CallerID_Input]{9018697787}[CallerID]{9018697787} CallerIDWasSentRequestSSN]{111111111}[CallerIDWasSentRequestSSN_PathTaken]{succe s}[CheckForValidSSN_RowCount]{1}[CheckForValidSSN_1_1]{27}[CheckForValidSSN_2_1] JORGCM }[CheckForValidSSN_3_1]{111111111}[CheckForValidSSN_4_1]{xxxxxxxxx }[CheckForValidSSN_5_1]{xxxxxx x. }[CheckForValidSSN_6_1]{2/11/2002}[CheckForValidSSN_7_1]{HELP DESK/PROGRAMMER }[CheckForValidSSN_8_1]{FT }[CheckForValidSSN_9_1]{}[CheckForValidSSN_10_1]{F}[CheckForValidSSN_11_1]{C}[Ch ckForValidSSN_12_1]{9016858923}[CheckForValidSSN_13_1]{xxx xxxxxxxxx- HUSBAND }[CheckForValidSSN_14_1]{6/24/1961}[CheckForValidSSN_15_1]{S}[CheckForValidSSN_16_1]{S}[MakeSureNotTerminated_ nput]{=""}[MakeSureNotTerminated]{=""}[RecordName]{U:\My Documents\VoiceGuide\RecordedVoicesAtClockInOut\0618124549_7_9018697787.wav}[RecordName_RecLen100ms]{31}[RecordName_EndRecC use]{timeout}[QueryForStatusIn_Out_RowCount]{1}[QueryForStatusIn_Out_1_1]{OUT}[Q eryForStatusIn_Out_2_1]{118}[Eval_IfComingToWork_Input]{"OUT" ="OUT"}[Eval_IfComingToWork]{True}[ifYouAreComingToWork]{1}[setClockinStatusToIN_ owCount]{1} 124601.51 7 eng copy of script ran: vbs_7_1.vbs 124601.54 7 Run Script waiting... (shellid=2372, process=1556) 124601.54 7 TimeoutSet 1 EV_TIMEOUT_CHECKONSTATE 124602.55 7 Timer fired EV_TIMEOUT_CHECKONSTATE 124602.55 7 ScriptEventCode 9007 iLineState=1501 124602.55 7 LsRunWaitTillFinished EV_TIMEOUT_CHECKONSTATE 124602.57 7 task still running (shellid=2372, process=1556) 124602.57 7 TimeoutSet 1 EV_TIMEOUT_CHECKONSTATE 124603.68 7 Timer fired EV_TIMEOUT_CHECKONSTATE 124603.68 7 ScriptEventCode 9007 iLineState=1501 124603.68 7 LsRunWaitTillFinished EV_TIMEOUT_CHECKONSTATE 124603.70 7 task still running (shellid=2372, process=1556) 124603.70 7 TimeoutSet 1 EV_TIMEOUT_CHECKONSTATE 124604.27 7 cl RvSet NewHour, 12 124604.27 7 rv add [NewHour]{12} 124604.29 7 cl RvSet NewDate, 6/18/2004 124604.29 7 rv add [NewDate]{6/18/2004} 124604.29 7 cl RvSet NewMin, 45 124604.29 7 rv add [NewMin]{45} 124604.80 7 Timer fired EV_TIMEOUT_CHECKONSTATE 124604.80 7 ScriptEventCode 9007 iLineState=1501 124604.80 7 LsRunWaitTillFinished EV_TIMEOUT_CHECKONSTATE 124604.80 7 task completed (shellid=2372, process=1556) 124604.82 7 No information returned from program to VG (no VGRUNRESULT_7.TXT or SHLRESLT.TXT) 124604.82 7 TimeoutClear 124604.82 7 [updateVoiceguideLogClockingIn] DB Query 124604.84 7 db Jet mode used (To use ODBC mode specify ODBC Connect string) 124605.10 7 RVreplace start (strlen>500) 124605.10 7 RVns [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[scriptsPath]{U:\My Documents\VoiceGuide\}[$RV_STARTTIME]{6/18/2004 12:45:37 PM}[$RV_DEVICEID]{7}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{9018697787}[CallerID_Input]{9018697787}[CallerID]{9018697787} CallerIDWasSentRequestSSN]{111111111}[CallerIDWasSentRequestSSN_PathTaken]{succe s}[CheckForValidSSN_RowCount]{1}[CheckForValidSSN_1_1]{27}[CheckForValidSSN_2_1] Jxxxxxxx }[CheckForValidSSN_3_1]{111111111}[CheckForValidSSN_4_1]{xxxxxxxx }[CheckForValidSSN_5_1]{aaaaaaaaa a. }[CheckForValidSSN_6_1]{2/11/2002}[CheckForValidSSN_7_1]{HELP DESK/PROGRAMMER }[CheckForValidSSN_8_1]{FT }[CheckForValidSSN_9_1]{}[CheckForValidSSN_10_1]{F}[CheckForValidSSN_11_1]{C}[Ch ckForValidSSN_12_1]{1234567899}[CheckForValidSSN_13_1]{xxx xxxxxxxx - HUSBAND }[CheckForValidSSN_14_1]{6/24/1961}[CheckForValidSSN_15_1]{S}[CheckForValidSSN_16_1]{S}[MakeSureNotTerminated_ nput]{=""}[MakeSureNotTerminated]{=""}[RecordName]{U:\My Documents\VoiceGuide\RecordedVoicesAtClockInOut\0618124549_7_9018697787.wav}[RecordName_RecLen100ms]{31}[RecordName_EndRecC use]{timeout}[QueryForStatusIn_Out_RowCount]{1}[QueryForStatusIn_Out_1_1]{OUT}[Q eryForStatusIn_Out_2_1]{118}[Eval_IfComingToWork_Input]{"OUT" ="OUT"}[Eval_IfComingToWork]{True}[ifYouAreComingToWork]{1}[setClockinStatusToIN_ owCount]{1}[NewHour]{12}[NewDate]{6/18/2004}[NewMin]{45} 124605.12 7 db About to execute a Modify type operation [insert into voiceguide_log (ssn,calldatetime,calldate,callstat,cid,calltime,seq,calldate_new,calltime_hour, alltime_min,calltime_time,channel_no,ssnseq,wavefilename,New_date) values ('111111111','6/18/2004 12:45:37 PM','6'+"/"+'18'+"/"+'2004','IN','9018697787','12'+":"+'46',118,6/18/2004,12,45,6/18/2004,7,'111111111'+cstr(118),'U:\My Documents\VoiceGuide\RecordedVoicesAtClockInOut\0618124549_7_9018697787.wav',6/18/2004)] 124606.46 7 db Modify operation returned OK 124606.46 7 rv add [updateVoiceguideLogClockingIn_RowCount]{1} 124607.76 7 TimeoutClear note the date is listed correctly in the values part of the insert statement calltime is the long integer field calldate_new is the text field and New_date is the short date field Share this post Link to post
SupportTeam Report post Posted 06/18/2004 09:10 PM What is your actual problem/question? Is it that you cannot get your SQL statement correct to insert a date/time field into the database? I'd suggest reading your database manual to find out how to specify the date/time in the SQL statement so that it is correctly read in by the database. For MS Access a date/time field specified like this will work: strDateTime = "#" & FormatDateTime(Now, vbShortDate) & " " & FormatDateTime(Now, vbLongTime) & "#" Share this post Link to post
