传递日期从JavaScript ADODB.Command参数 [英] Pass date to ADODB.Command parameter from JavaScript

问题描述

使用营造怀旧经典ADO，的不的ADO.NET，你怎么传递的日期值从JavaScript中的存储过程？该JS是在IIS7一个传统的ASP页面运行。 SQL Server是2012（1）。

Using old-timey classic ADO, not ADO.NET, how do you pass a date value to a stored procedure from JavaScript? The JS is running in a classic ASP page on IIS7. SQL Server is 2012 (1).

解决：请参见下面回答。长话短说，结果被通过JSON stringifier的忽视与变异日期值的属性找到自己的方式回到我身边。

SOLVED: See answer below. Long story short, the results were finding their way back to me via a JSON stringifier that ignored properties with variant date values.

我有一个存储过程在SQL Server：

I have a stored procedure in SQL Server:

create procedure test(@n int, @d datetime)
as
begin
    select @n n, @d d;
end;

和我有一个传统的ASP网页一些JavaScript code：

And I have some JavaScript code in a classic ASP page:

var conn = new ActiveXObject("ADODB.Connection");
var cmd = new ActiveXObject("ADODB.Command");

conn.Open(connectionString);

cmd.ActiveConnection = conn;

cmd.CommandType = adCmdStoredProc;
cmd.CommandText = 'dbo.test';

cmd.Parameters.Append(cmd.CreateParameter('@n', adInteger, adParamInput, 4, 123));

var param = cmd.CreateParameter('@d', adDate, adParamInput);

param.Value = (new Date('01/01/2000')).getVarDate();

cmd.Parameters.Append(param);

var rs = cmd.Execute();

我回来从SP总是有 @n （123以上）的预期值，并始终有空为 @d 。 的connectionString ，因为它叫SP，这绝对是我认为我打电话的SP必须是OK;如果我做了更改，它们体现在哪些回来。

What I get back from the SP always has the expected value for @n (123, above), and always has null for @d. connectionString must be OK because it does call the SP, and it's definitely the SP I think I'm calling; if I make changes to it, they are reflected in what comes back.

我得到的 getVarDate（）从<一个href=\"http://stackoverflow.com/questions/7348805/what-is-equivalent-of-datetime-tooadate-in-javascript/7349381#7349381\">Eric在这里利珀特的回答的。我也曾尝试 adDBDate 用各种数据类型。

I get the getVarDate() from Eric Lippert's answer here. I have also tried adDBDate with various data types.

我写的用于测试目的上面的测试SP;你可能已经注意到，它并没有做很多有益的工作。在生产中，我有一个日期传递给现有的SP。该SP的细节不借给清晰得多这个问题。我的可以的，如果我绝对必须写接收日期作为字符串并将其转换成一个包装SP。但我想明白什么是错在这里，我们有足够多的半多余的SP已经塞满了更多的数据库。而这仅仅是做事情可怕的方式。

I wrote the above test SP for testing purposes; you may have noticed that it doesn't do very much useful work. In production, I have to pass a date to an existing SP. The details of that SP wouldn't lend much clarity to this question. I can, if I absolutely must, write a wrapper SP that receives a date as a string and converts it. But I want to understand what is wrong here, and we have more than enough semi-redundant SPs cluttering up the database already. And that's just a horrible way to do things.

（1）@@版本='的Microsoft SQL Server 2012中（SP1） - 11.0.3381.0（X64）2013年8月23日20时08分13秒的版权所有（c）Microsoft公司企业版（64位）的Windows NT 6.0 （建设6002：Service Pack 2的）（管理程序）

(1) @@version = 'Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64) Aug 23 2013 20:08:13 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)'

推荐答案

想通了;我真的错了，我甚至没有提到实际上是导致问题的一部分。

Figured it out; I was so wrong, I didn't even mention the part that was actually causing the problem.

rs.Fields.Item（D）。值返回类型的变体 adDBTimeStamp 。

这是该公司作为一个Web服务，并返回JSON的ASP和的我使用中的JSON stringifier会忽略与 adDBTimeStamp 值的属性。一切都从DB精细回来，后来越来越下降。

This is in an ASP that's acting as a web service and returning JSON, and the JSON stringifier I'm using just ignores properties with adDBTimeStamp values. Everything was coming back fine from the DB, then getting dropped later.

这实际上原来，ADODB.Command的 CreateParameter 方法是有关处理日期非常乐于助人。

It actually turns out that ADODB.Command's CreateParameter method is very obliging about handling dates.

所以：

var rs = RecordSetToObjArray(cmd.Execute();

//  ...

//  Convert variant dates into something the JSON stringifier groks. 
function GetADOFieldValue(field) {
    switch (field.Type) {
        case adDBTimeStamp:
        case adDate:
        case adDBDate:
        case adDBTime:
        case adFileTime:
            if ('undefined' === '' + field.Value)
                return null;
            return new Date('' + field.Value);

        default:
            return field.Value;
    }
}

//  Given recordset from ADODBCommand.Execute(), return as array of JSON 
//  objects. 
//  Also convert variant dates into something the JSON stringifier groks. 
//  If an SP returns multiple recordsets, that's on you. 
function RecordSetToObjArray(rs) {
    var rtn = [];
    var fieldNames = [];

    for (var i = 0; i < rs.Fields.Count; ++i) {
        fieldNames.push(rs.Fields.Item(i).Name);
    }

    rtn.FieldNames = fieldNames;

    while (!rs.EOF) {
        var rec = {};

        for (var i = 0; i < fieldNames.length; ++i) {
            rec[fieldNames[i]] = GetADOFieldValue(rs.Fields.Item(fieldNames[i]));
        }
        rtn.push(rec);
        rs.MoveNext();
    }

    return rtn;
}

function RecordSetToScalar(rs) {
    if (rs.RecordCount == 0 || rs.Fields.Count == 0)
        return null;
    return GetADOFieldValue(rs.Fields.Item(0));
}

这篇关于传递日期从JavaScript ADODB.Command参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！