cancel
Showing results for 
Search instead for 
Did you mean: 

How to pass Date as in parameter for stored proc

SOLVED
Highlighted
Community Leader

Re: How to pass Date as in parameter for stored proc

 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.

Highlighted
Occasional Contributor

Re: How to pass Date as in parameter for stored proc

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?

Highlighted
Occasional Contributor

Re: How to pass Date as in parameter for stored proc

@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

Highlighted
Community Leader

Re: How to pass Date as in parameter for stored proc

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

Highlighted
Occasional Contributor

Re: How to pass Date as in parameter for stored proc

It's Empty Object

Occasional Contributor

Re: How to pass Date as in parameter for stored proc

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

Highlighted
Community Leader

Re: How to pass Date as in parameter for stored proc

Great to hear that.

 

Can you share code with the community ?

Highlighted
Occasional Contributor

Re: How to pass Date as in parameter for stored proc

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

 

View solution in original post

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors