SQL insert with JScript arrays and adodb
Hello,
I have a problem with the following function:
function sqlInsert()
{
// static name of the database table
var table = "Names";
// calls the function which opens the database connection
var connection = openDatabaseConnection();
// calls the function which opens table "Names"
var recordset = openRecordset(connection, table);
// array object with table column names as strings
var columns = new Array("forename", "surname");
// array object with values to insert as strings
var values = new Array("John", "Doe");
// writes the message to the test log
// "forename = John and surname = Doe"
Log.Message(
columns[0] + " = " + values[0] + " and " +
columns[1] + " = " + values[1]
);
// calls adodb AddNew function on the opened recordset
// to insert the values into the table
recordset.AddNew(columns, values);
// calls a function to update the recordset,
// close the recordset and close the database connection
updateAndClose = updateAndClose(recordset, connection);
}In this state the function works just fine, a new tupel is inserted into table "Names" with correct values in the correct columns.
However the whole log message only was for error searching purposes and I would like to remove it, but if I remove it and run the function I only get an empty new tupel in table "Names".
In debug mode the arrays for columns and values always look like shown in the following image, no matter if the code to create the log message is active or commented out.
Could anyone explain to me please, why I get an emtpy tupel, when the arrays are not used to create a log message before feeding them into the AddNew function?
Hi,
You need to pass either strings or the safeArray arrays. You code doesn’t work as you pass the JScript array the the AddNew method. You can use two approaches:
- Update your table in the following way:
recordset.AddNew(); recordset("forename") = "John"; recordset("surname") = "Doe"; recordset.Update;- Convert a JScript array to safeArray in the following way:
function ArrayToSafeArray(array) { var dictionary = new ActiveXObject("Scripting.Dictionary"); for (var i = 0; i < array.length; i++) { dictionary.add(i, array[i]); } return dictionary.Items(); } var columns = new Array("forename", "surname"); var values = new Array("John", "Doe"); recordset.AddNew(ArrayToSafeArray(columns), ArrayToSafeArray(values)); recordset.Update();
