cancel
Showing results for 
Search instead for 
Did you mean: 

How to pass Date as in parameter for stored proc

SOLVED
Highlighted
Occasional Contributor

How to pass Date as in parameter for stored proc

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Leader

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

Try to run it with this peace of code:

 

  var sql = 
  `
  // sql or procedure
  `;
  
  var conn = ADO.CreateCommand();
  conn.ActiveConnection = connectionString;
  conn.CommandType = adCmdText;
  conn.CommandText = sql;
  conn.CommandTimeout = 6000;
  
  try {
    var exe = conn.Execute();
  } catch(exception) {
    Log.Message("FAIL");
  }

View solution in original post

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

17 REPLIES 17
Highlighted
Community Leader

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

Hi,

 

Try to check this article

 

https://support.smartbear.com/viewarticle/65800/

 

maybe this will help You.

Highlighted
Occasional Contributor

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

Hi Thanks for that link. I have tried two TestComplete ADO constants for Date/DateTime mentioned but i am running into below issues

Tried adDate as Datatype but got into "application uses a value of the wrong type for the current operation" issue.

Tried adDBTimeStamp but got into "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

 

Can you confirm if it's because I am passing date in String format? If so, how can we pass the same?

 

 

 

Highlighted
Community Leader

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

If I understand you correctly. You're trying to run a procedure that already exists in the database, right?

 

If so... You must pass a parameter with the correct dataType.

 

Connect to the database and check the type of value in the column you're looking for.

Highlighted
Occasional Contributor

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

@Wamboo , Thanks for your reply. Yes, I am trying to execute a Stored proc which is available in DB.

 

I checked the parameter DataType in DB and is "datetime"

As mentioned in below screenshot I tried those two TestComplete constants but I was getting the errors mentioned in my previous comment.

Naresh_Surya_0-1589892653762.png

Am I missing anything here?

Highlighted
Community Leader

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

What database are You using?

Highlighted
Occasional Contributor

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

Microsoft SQL Server

Highlighted
Community Leader

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

Try to run it with this peace of code:

 

  var sql = 
  `
  // sql or procedure
  `;
  
  var conn = ADO.CreateCommand();
  conn.ActiveConnection = connectionString;
  conn.CommandType = adCmdText;
  conn.CommandText = sql;
  conn.CommandTimeout = 6000;
  
  try {
    var exe = conn.Execute();
  } catch(exception) {
    Log.Message("FAIL");
  }

View solution in original post

Highlighted
Occasional Contributor

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

Sorry, I am new to dealing with stored procs. 

I assigned  stored proc path to sql.

ActiveConnection = Connection String(includes catalog, username/password)

I didn't understand what to place at adCmdText. 

 

Highlighted
Occasional Contributor

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

I've seen we can execute stored proc using Query and tried executing SQL query 

EXEC <stored proc name> @date=N'date'; 

It returned query timedout wrror as my stored proc takes more than 30 secs (close to one minute to get executed). Is there any way to mention querytimeout as we do commandtimeout in stored proc?

New Here?
Join us and watch the welcome video:
Announcements
Script Wanted
Let's start July with new challenges!

Create a script to pass custom command-line arguments as a test parameter for the TechCorner tag!

Will you dare to take the challenge?


Top Kudoed Authors