Auto fill field using Row_Inserting

[JAW]

Hello,
I want to populate a field from a different table based on the selection of another field. So I have the ResidentID and the ResidentName in a Residents table. I am inserting records for the Resident and I want to get the ResidentName from the Resident Table using the ResidentID when adding a record. Here is what I have tried but it is not working;

rsnew["ResidentName"] = ew_ExecuteScalar("SELECT * FROM Residents WHERE ResidentID = (Convert.ToInt32("rsnew["ResidentId"]")");

Can someone pleased tell me what is wrong with the code above. It would be greatly appreciated.

Thanks in advance,
JW

[vincenthung]

You need to concat the SQL string with the rsnew["ResidentId"].

Change your code as below then try again. (Assuming you are using C#)
rsnew["<Field Name>"] = ew_ExecuteScalar("SELECT <Field> FROM <Table> WHERE <ID Field> = " + Convert.ToString(rsnew["<Field Name>"]));

To debug your SQL, you can use ew_Response.Write() and ew_Response.End() to write your SQL to check it is work as your expected.

For example:
var sSql = "Select * From <Table>";
ew_Response.Write(sSql);
ew_Response.End();