使用ADODB.Recordset在Javascript中制作Insert语句

Question

如何使用javascript ADODB.Recordset对象执行INSERT语句？

这是我试图运行的代码：

/* Getting access to the database */
var connection = new ActiveXObject("ADODB.Connection");
var connectionstring = "Data Source=srvp7rnd-herm;Initial Catalog=hermes;User ID=hermes;Password=hermes;Provider=SQLOLEDB";
connection.Open(connectionstring);

/* JavaScript obect to access a SQL query's results */
var rs = new ActiveXObject("ADODB.Recordset");

/* Getting the current MAX(id) from the database */
rs.Open("SELECT MAX(id) FROM Screen_Template", connection);
rs.MoveFirst;
var maxID = rs.Fields.Item(0);
maxID = maxID + 1;

/* TODO: Get the last UID */
var sql = "INSERT INTO Screen_Template(template_name, OpCo, env, template_xml, language, id, title, role, UID) VALUES (" + templateName + "," + opco + "," + env + "," + "<hello>hello</hello>" + ",eng," + maxID + ",Hermes SMS message composer," + "manag, 10)";
alert(sql);
rs.Open(sql, connection);

/* Closing the connections */
rs.close;
connection.close;

但当我试图运行该代码时，它会给我一条错误消息。

Answer 1

请尝试使用此代码。如果Screen_Template列类型为Varchar，则必须在变量后附加"'“。如果来自警报( SQL )的sql语句的格式与您的表模式一致，那么它应该是正确的。希望这能有所帮助。

   /* Getting access to the database */
    var connection = new ActiveXObject("ADODB.Connection");
    var connectionstring = "Data Source=srvp7rnd-herm;Initial Catalog=hermes;User ID=hermes;Password=hermes;Provider=SQLOLEDB";
    connection.Open(connectionstring);

    /* JavaScript obect to access a SQL query's results */
    var rs = new ActiveXObject("ADODB.Recordset");

    /* Getting the current MAX(id) from the database */
    rs.Open("SELECT MAX(id) FROM Screen_Template", connection);
    rs.MoveFirst;
    var maxID = rs.Fields.Item(0);
    maxID = maxID + 1;
    rs.close;

    /* TODO: Get the last UID */
    var sql = "INSERT INTO Screen_Template(template_name, OpCo, env, template_xml, language, id, title, role, UID) VALUES ('" + templateName + "','" + opco + "','" + env + "'," +"'<hello>hello</hello>'" + ",'eng'," + maxID + ",'Hermes SMS message composer'," + "'manag', 10)";
    alert(sql);
    rs.Open(sql, connection);

    /* Closing the connections */
    //rs.close;
    connection.close;