How to pass Date as in parameter for stored proc

[Naresh_Surya]

I tried below code

// Adding a return parameter
SProc.Parameters.AddParameter()
SProc.Parameters.Items(0).name = "RETURN_VALUE"
SProc.Parameters.Items(0).DataType = adInteger
SProc.Parameters.Items(0).Direction = adParamReturnValue
SProc.Parameters.Items(0).Value = null

//Adding an in parameter
SProc.Parameters.AddParameter()
SProc.Parameters.Items(1).name = "@timeOfBuild"
SProc.Parameters.Items(1).DataType = adDBDateTime  // what should we pass here I tried adDBTime and adDBTimeStamp also
SProc.Parameters.Items(1).Value = "2020-05-19"
SProc.CommandTimeout = 120;
SProc.ExecProc();

Can someone please help me here? I am getting adDBDateTime is not defined. Thanks in advance

[Wamboo]

 conn.CommandType = adCmdText;
  conn.CommandText = sql;
  conn.CommandTimeout = 6000;

adCmdText - You don't have to specify this, this parameter is the type of function to be executed.

CommandTimeout - This is the default time to run the script. Increase it as you wish.

[Naresh_Surya]

Thank you, I have tried passing EXEC command as sql and looks like it ran didn't print FAIL message. But I tried to print exe also which was empty.

Any way that we can print response from stored procedure?

[Naresh_Surya]

@Wamboo , Thank you for taking time to help here. Though I accepted your solution. I am not able to validate the response from Stored proc and also doesn't know if it's executed or not.

var sql = "EXEC <stored procname> @date = N'"+Date+"'"

var conn = ADO.CreateCommand();
conn.ActiveConnection = "Provider=SQLOLEDB;Initial Catalog=<>;User ID=<>;Password=<>;Data Source= \"*\" ";
conn.CommandType = adCmdText;
conn.CommandText = sql;
conn.CommandTimeout = 65000;

try {
var RecSet = conn.Execute();
while (!RecSet.EOF)
{
Log.Message(RecSet.Fields.Item("Return Value").Value);
RecSet.MoveNext();
};
// Log.Message(exe);
} catch(exception) {
Log.Message(exception);
Log.Message("FAIL");
}

Exception at while block saying unable to perform operation on closed object. If I increase the timeout to 70000 it's giving query timeout exception.

Can you please tell me what I am missing here

[Wamboo]

what Do you have in "var RecSet = conn.Execute();" while debugging?

[Naresh_Surya]

It's Empty Object

[Naresh_Surya]

@Wamboo , Thank you for your time. But I have got a way to fix this issue with my initial code ADO.createADOStoredroc() method. I have passed Date string by converting it to date format using aqConvert which fixed.

[Wamboo]

Great to hear that.

Can you share code with the community ?

[Naresh_Surya · Accepted solution]

Sure @Wamboo .

// Adding a return parameter
SProc.Parameters.AddParameter()
SProc.Parameters.Items(0).name = "RETURN_VALUE"
SProc.Parameters.Items(0).DataType = adInteger
SProc.Parameters.Items(0).Direction = adParamReturnValue
SProc.Parameters.Items(0).Value = null

//Adding an in parameter
SProc.Parameters.AddParameter()
SProc.Parameters.Items(1).name = "@timeOfBuild"
SProc.Parameters.Items(1).DataType = adDate
SProc.Parameters.Items(1).Value = aqConvert.strtoDate("2020-05-19")
SProc.CommandTimeout = 75;
SProc.ExecProc();