I have a Web Api using express and Tedious to store some data on Azure SQL database. With nvarchar
types and int
types it works well, but when I try to save DateTime
value I get an error message:
Insert into Proxy (Ip, RequisitionDate)
values ('1', '2016-05-18 3:32:21' )
Error:
RequestError: Validation failed for parameter 'RequisitionDate'. Invalid date.] message: 'Validation failed for parameter \'RequisitionDate\'. Invalid date.', code: 'EPARAM' }
Well, the interesting thing is that
Insert into Proxy (Ip, RequisitionDate)
values ('1', '2016-05-18 3:32:21')
is the query that i execute in node.js api:
var query = "Insert into Proxy (Ip,RequisitionDate) values ( '"+ ip + "', '"+ date + "' )";
console.log(query); // Insert into Proxy (Ip,RequisitionDate) values ( '1', '2016-05-18 3:32:21' )
request = new Request(query, function(err) {
if (err) {
console.log(err);}
});
request.addParameter('Ip', TYPES.NVarChar,'SQL Server Express 2014');
request.addParameter('RequisitionDate', TYPES.DateTime , 'SQLEXPRESS2014');
connection.execSql(request);
}
If I execute the query direct on the SqlManager Studio, it works ok.
It seems that you haven't set the correct datetime
value in addParameter
function. According the API reference, the function is used as request.addParameter(name, type, value, [options])
.
Please try the following code:
var query = "Insert into Proxy (Ip,RequisitionDate) values ( @ip , @date)";
request = new Request(query, function(err) {
if (err) {
console.log(err);}
});
request.addParameter('ip', TYPES.NVarChar,'<ip value>');
request.addParameter('date', TYPES.DateTime , new Date());
// or the set the special date, refer to https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
// request.addParameter('RequisitionDate', TYPES.DateTime , new Date(2016,05,19,3,32,21))
connection.execSql(request);
}
Any further concern, please feel free to let me know.