Pass date to ADODB.Command parameter from JavaScript

Question

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).

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.

I have a stored procedure in SQL Server:

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

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();

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.

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

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) @@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)'

It works for me. If the problem still exists after the correcting typo, you may want to share your connection string. Some drivers / providers may lead to problems like this. — Kul-Tigin
– Kul-Tigin, Commented Dec 23, 2013 at 19:31
Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DatabaseName;Integrated Security=SSPI — 15ee8f99-57ff-4f92-890c-b56153
– 15ee8f99-57ff-4f92-890c-b56153, Commented Dec 23, 2013 at 19:32
A good resource for cases like this is Carl Prothman - Data Type Mapping it shows that adDBTimeStamp is equivalent to datetime and smalldatetime in SQL Server. — user692942
– user692942, Commented Dec 24, 2013 at 9:29

15ee8f99-57ff-4f92-890c-b56153 · Accepted Answer · 2013-12-24 17:30:38Z

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

rs.Fields.Item("d").Value returns a variant of type adDBTimeStamp.

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.

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

So:

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));
}

Collectives™ on Stack Overflow

Pass date to ADODB.Command parameter from JavaScript

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related