Ask Your Question

Trouble with SQL INSERT in macro in OOBase

asked 2019-09-29 18:18:54 +0200

jrolland gravatar image

updated 2019-09-29 18:33:53 +0200

Opaque gravatar image

I have looked at many examples of how to execute SQL commands within a macro, but can't get this to work.

It throws a: BASIC runtime error. An exception occurred Type: Message: Wrong data type: java.lang.IllegalArgumentException

Below is the code:

'create "connection and sql statement" to allow sql commands to be executed

DIM oStatement AS OBJECT

oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object

'create, and format SQL INSERT command with "variables" as VALUES

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', '" & stDateValue & "', '" & stProvidersValue & "')" 


oStatement.ExecuteUpdate(sSQL) <----- this line generates the error

Any ideas? Is it a SQL formatting issue?

My variables all contain, and print their contents correctly.

Help, and thanks in advance.

Edit info [Opaque]: Put code into preformatted text

edit retag flag offensive close merge delete


Hello @jrolland,

You have a print statement for sSQL. Could you provide the result of that statement? On the surface, the date field may be a part of the problem. Dates must be in the format of YYYY-MM-DD. There may be other things but it is not known what field types are in the table or how the variables are constructed.

Please do not use an answer to respond. Either edit original question or add a comment.

Ratslinger gravatar imageRatslinger ( 2019-09-29 18:35:44 +0200 )edit

The variable(s) contents print to my screen as follows: stIDvalue = '280' stProvidersValue = 'Rig #2' stDateValue = '20192909' stRemarksValue = 'test this now' I included the hyphens because when I print the INSERT line they are there. If I just print the variables alone the hyphens are not printed.

My table, "tbl_TREATMENTS" field list is as follows: treatment_ID (integer, auto increment) fromWELLS_ID (integer) treatment_record (text varchar) record_date (date) serviced_by_name (text varchar)

jrolland gravatar imagejrolland ( 2019-09-29 19:08:31 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-09-29 19:28:41 +0200

Ratslinger gravatar image

updated 2019-09-29 19:29:43 +0200


Based upon the data in the comments the problem is in the date field. It is in the incorrect format. Locale does not matter here. It must be in YYYY-MM-DD format including the hyphens. Your date is YYYYDDMM without the hyphens.

Tested in Tools->SQL:

image description

Status #2 was using your date; status #3 is with corrected date format.

edit flag offensive delete link more


Just a note. With or without hyphens, your date produces the same error.

Ratslinger gravatar imageRatslinger ( 2019-09-29 19:42:18 +0200 )edit

I tried setting the "date" VALUE in my SQL statement to a 'literal' formatted as 2019-09-29.

Now I get the error: Type: Message: Column Not Found: 2019-09-29

What can be happening? Is there any other info that I can send that will help figure this out?

Thanks, again

jrolland gravatar imagejrolland ( 2019-09-29 21:09:48 +0200 )edit


If you set the date to a literal, did you remove the quotes? The error seems to think you have designated a field in the statement. Try:

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', '2019-09-29' , '" & stProvidersValue & "')"

Edit: If you still have further problems, post a sample Base file in your edited question. Be sure to remove any personal or confidential information.

Ratslinger gravatar imageRatslinger ( 2019-09-29 21:36:10 +0200 )edit

OK. Using the "literal" value with proper syntax inserted the record correctly. Thank you!

Can you point me in the right direction to "format" my date variable value from YYYYMMDD to YYYY-MM-DD? I've looked and looked but find almost nothing about setting up formatting for strings.

A little nudge/direction would be appreciated.

Thanks so much, your expertise was invaluable.

jrolland gravatar imagejrolland ( 2019-09-29 22:22:19 +0200 )edit

Where are you getting the date from? What is the original format? If something like DD/MM/YY (or YYYY) or MM/DD/YY then you can use FORMAT directly:

sSQL = "INSERT INTO ""tbl_TREATMENTS"" (""fromWELLS_ID"", ""treatment_record"", ""record_date"", ""serviced_by_name"") VALUES ('" & stIDvalue & "', '" & stRemarksValue & "', " & Format(YOUR_DATE_FIELD, "'YYYY-MM-DD'") & " , '" & stProvidersValue & "')"
Ratslinger gravatar imageRatslinger ( 2019-09-29 22:29:28 +0200 )edit

Here is a little more crude of a method using your YYYYMMDD value:

reformDate = Left(stDateValue,4) & "-" & Right(Left(stDateValue,6),2) & "-" & Right(stDateValue,2)

then use reformDate in SQL.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

Ratslinger gravatar imageRatslinger ( 2019-09-29 22:48:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-09-29 18:18:54 +0200

Seen: 99 times

Last updated: Sep 29 '19