I am trying to edit and send an "hourly" data value from an Excel spreadsheet to our Oracle based database. Originally the data values on the spreadsheet were queried from the database in question. Each value in the hourly table of the database has a start date and an endate time field that must be sent back to the database with the value of the edited data. I am using a stored procedure (see code below) to send the data value and other associated factual and config info to do this (see code below). Every time I run the code to transfer an houlry edited value from Excel to the database the program stops when it gets to the portion of the code that deals with the date. A message such as "hourly" interval values cannot have the same start and end date. Apparently the database interprets the date I am providing as a top of the day midnight value and truncates any hour or minute portions of the date provided...thin ks that every date/time provided in the top of the day. That is why the database thinks I am sending the same date/time for both the start and end time. I have tried many combinations of syntax to make this work...but to no avail. Please see code I am using below: Any suggestions? Thanks in advance.
[CODE=vb]Set cmd = New ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "modify_r_b ase"
cmd.Parameters. Append cmd.CreateParam eter("site_data type_id", adVarChar, adParamInput, Val(Len(sdid$)) , sdid$)
cmd.Parameters. Append cmd.CreateParam eter("interval" , adVarChar, adParamInput, Val(Len(interva l$)), interval$)
cmd.Parameters. Append cmd.CreateParam eter("start_dat e_time", adDate, adParamInput, 8, startdate)
cmd.Parameters. Append cmd.CreateParam eter("end_date_ time", adDate, adParamInput, 8, enddate)
cmd.Parameters. Append cmd.CreateParam eter("value", adVarChar, adParamInput, Str_len, str_value)
cmd.Parameters. Append cmd.CreateParam eter("agen_id", adVarChar, adParamInput, 2, agenid$)
cmd.Parameters. Append cmd.CreateParam eter("overwrite _flag", adVarChar, adParamInput, 1, overwrite$)
cmd.Parameters. Append cmd.CreateParam eter("validatio n", adVarChar, adParamInput, 1, "")
cmd.Parameters. Append cmd.CreateParam eter("collectio n_system_id", adVarChar, adParamInput, 2, collectionid$)
cmd.Parameters. Append cmd.CreateParam eter("loading_a pplicaion_id", adVarChar, adParamInput, 2, loadapp$)
cmd.Parameters. Append cmd.CreateParam eter("method_id ", adVarChar, adParamInput, 2, methodidnum$)
cmd.Parameters. Append cmd.CreateParam eter("computati on_id", adVarChar, adParamInput, 1, compid$)
cmd.Parameters. Append cmd.CreateParam eter("do_update _Y_or_N", adVarChar, adParamInput, 1, "Y")
Stop
cmd.Execute
Set cmd.ActiveConne ction = Nothing[/CODE]
[CODE=vb]Set cmd = New ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "modify_r_b ase"
cmd.Parameters. Append cmd.CreateParam eter("site_data type_id", adVarChar, adParamInput, Val(Len(sdid$)) , sdid$)
cmd.Parameters. Append cmd.CreateParam eter("interval" , adVarChar, adParamInput, Val(Len(interva l$)), interval$)
cmd.Parameters. Append cmd.CreateParam eter("start_dat e_time", adDate, adParamInput, 8, startdate)
cmd.Parameters. Append cmd.CreateParam eter("end_date_ time", adDate, adParamInput, 8, enddate)
cmd.Parameters. Append cmd.CreateParam eter("value", adVarChar, adParamInput, Str_len, str_value)
cmd.Parameters. Append cmd.CreateParam eter("agen_id", adVarChar, adParamInput, 2, agenid$)
cmd.Parameters. Append cmd.CreateParam eter("overwrite _flag", adVarChar, adParamInput, 1, overwrite$)
cmd.Parameters. Append cmd.CreateParam eter("validatio n", adVarChar, adParamInput, 1, "")
cmd.Parameters. Append cmd.CreateParam eter("collectio n_system_id", adVarChar, adParamInput, 2, collectionid$)
cmd.Parameters. Append cmd.CreateParam eter("loading_a pplicaion_id", adVarChar, adParamInput, 2, loadapp$)
cmd.Parameters. Append cmd.CreateParam eter("method_id ", adVarChar, adParamInput, 2, methodidnum$)
cmd.Parameters. Append cmd.CreateParam eter("computati on_id", adVarChar, adParamInput, 1, compid$)
cmd.Parameters. Append cmd.CreateParam eter("do_update _Y_or_N", adVarChar, adParamInput, 1, "Y")
Stop
cmd.Execute
Set cmd.ActiveConne ction = Nothing[/CODE]
Comment