Inserting a Timestamp into a Table

HCL Domino Domino Forum
1

In a Notes db, I have code that’s intended to insert the current date/time into a table of a relational db in the format yyyy-mm-dd hh:mm:ss.

The date/time is captured in a string in the proper format as follows. (I confirm this with a messagebox).

Dim varTimeStamp As Variant

Dim strTimeStamp As String

varTimeStamp = Now()

strTimeStamp = “'” & Format( varTimeStamp, “yyyy-mm-dd hh:mm:ss”) & “'”

However, an insert statement fails when it refers to strTimeStamp as follows:

{’ , } & {strTimeStamp} & { , '}

I’ve tried variations of the above code and all have failed, usually with the error ‘ODBC could not complete the requested operation.’

If the date/time is hard-coded as follows, the insert is successful.

{’ , } & {‘2008-10-31 18:00:00’} & { , '}

I’d appreciate any ideas on what is required to insert into a table the current date/time in the format yyyy-mm-dd hh:mm:ss.

Thanks.

2

Subject: RE: Inserting a Timestamp into a Table

an insert statement fails when it refers to strTimeStamp as follows: {’ , } & {strTimeStamp} & { , '}

To refer to a variable in LotusScript, do not put it in quotes. You used the literal string “strTimeStamp” instead of the variable value.

Also, your hardcoded command that works, has the date in single quotes, and the above expression does not add single quotes in. Try this (observe the added chars in red):

… {', ‘} & strTimeStamp & {’, '} …

As a general rule in LotusScript, if you need a string put together, you must specify exactly how the string is assembled. The Write statement (note: the statement, not the method of NotesStream) is the only exception; it’ll throw in quotes and commas for you, not necessarily in the way you would like, which is why I never use this statement.

In this case, you could have seen the problem yourself by checking your inputs more. You used msgbox to display the value of strTimeStamp, and that was a good move. But strTimeStamp was not the final value you sent to Insert method. It usually makes sense, when you’re building a complex string, to store the entire value in a temporary string variable, so that you can use the debugger or msgbox to see the exact value you’re about to send to the method.

Dim strTmp$

strTmp = … & {, ‘} & strTimeStamp & {’, '} & …

Msgbox strTmp

Call odbc.Execute(strTmp, etc