2. ASPRunner.NET General questions
  3. [SOLVED] Updating master table when new detail record is created.
This topic is locked
[SOLVED]

Updating master table when new detail record is created.

 		3/25/2024 8:04:19 PM
ASPRunner.NET General questions
M
 Markos Okihisa author

I'm stuck trying to update a field called "Status" in my master table when I create a new record in my detail table.

Both the master and detail tables have a field called "ToolID" and are linked.

I have my update code in the "After record added" section and it works when I manually enter the tool ID number "0004" but doesn't seem to work when I try to pass the number using values["ToolID"].

// DB.Exec("update ToolList set Status = 'OUT' WHERE ToolID = '" + "0004" + "'");

DB.Exec("update ToolList set Status = 'OUT' WHERE ToolID = '" + values["ToolID"] + "'");

Any insight or assistance would be greatly appreciated..
Thank you in advance!

img alt

admin 3/26/2024

I have a couple of typical suggestions here.

  1. Print SQL query on the page instead of executing it. This way you can see what exactly went wrong.
  2. Use Database API's Update() method instead of crafting the SQL stetement manually.

M
 		Markos Okihisa author 3/26/2024

Thank you!!!

Using the API's Update() method worked. The master table is now consistenly being updated with the status when a new record is created in the detail table.

img alt

E
 		Eric Chan 4/3/2024

Your problem is related to the XVar object behaviour. You have to add the method ".ToString()" to convert the XVar object to a String before applying the string concatenation '+'.

There are several ways to solve your problem:

  1. DB.Exec("update ToolList set Status = 'OUT' WHERE ToolID = '" + values["ToolID"].ToString() + "'"); // solve the issue in your original SQL statement
  2. DB.Exec(DB.PrepareSQL("update ToolList set Status = 'OUT' WHERE ToolID = ':1'", values["ToolID"])); // apply the DB.PrepareSQL function, pass in the XVar object(s) directly
  3. DB.Exec(DB.PrepareSQL("update ToolList set Status = 'OUT' WHERE ToolID = ':new.ToolID'")); // apply the DB.PrepareSQL function with build-in sql variable(s)

Although solution 1 is worked, solution 2 and 3 should be the better choices because they may prevent the SQL injection.