Ask Your Question
0

Execute SQL with variable

asked 2020-06-11 21:34:26 +0200

Tripple Delta gravatar image

Hello,

In order to learn more about macros I'm trying to execute an SQL statement from a button. I stripped down a database to the absolute minimum to explain my problem.

In the form frm_prefs I created a button to update the field InvoiceYear. The last line in the macro seems to be wrong and I've been looking around for a solution without luck.

Maybe someone here can point me in the right direction?

Thanks.

C:\fakepath\Invoice.odb

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-06-11 22:13:33 +0200

Ratslinger gravatar image

Hello,

There are multiple problems.

You are trying to update a record which doesn't exist. Preferences table where ID = 1

There is no error checking in the code to help detect problems. See answer in this post for direction -> CalcBASIC+Non-registred HSQLDB : How to deal with .odb.lck ?

In your SQL statement:

""PefID"" = 1

should be:

""PrefID"" = 1

When using a variable within the SQL it is not within the string but joined to it. Also helpful to construct the string separate from the execution. Some strings can get quite long.

Original:

oStatement.execute("UPDATE ""Preferences"" SET ""InvoiceYear"" = 'InputVal' WHERE ""PefID"" = 1 ")

Repaired:

sSQL = "UPDATE ""Preferences"" SET ""InvoiceYear"" = '" & InputVal & "'WHERE ""PrefID"" = 1 "
oStatement.execute(sSQL)

Tested as OK with these changes made.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-06-11 21:34:26 +0200

Seen: 43 times

Last updated: Jun 11