Update Oracle date fields with VB6 date variables

[Palmero]

See the following attached code to give me an idea on how to insert VB date variables in Oracle V8i date fields.

Dim start_time As Date
Dim end_time As Date
Dim Return_code as integer

start_time = Now()
Return_code = My_proc(Transaction, response, err)
end_time = Now()

duration = DateDiff("s", start_time, end_time)


QSQL = "Update MY_TABLE set duration=" & duration & ",start_time='" & start_time & "',end_time='" & end_time & "'"
Set Squery = Cn.CreateQuery("", QSQL)
Squery.Execute


THE UPDATE DOES NOT WORK.
I have the execution error : 40002
S1000:[Oracle][ODBC][Ora]ORA-01830


Thanks in advance for your idea.

Palmero

 

[comaboy]

Don't know about Oracle, but the delimeters look wrong. For instance in Access this works:

"SELECT * FROM tblTable WHERE fldNAME like '*smith*'"

Try the same string over ADO in VB and it fails - You need to use an SQL compliant string as in

"SELECT * FROM tblTable WHERE fldNAME like '%smith%'"

I think you need to change

.....start_time='" & start_time & "',.....
to
.....start_time=#" & start_time & "#,.....

However, I'm no Oracle expert, so the # date delimeter could be wrong.

Hope this helps, and any Oracle experts, please feel free to kick me if I'm wrong !