This is a migrated thread and some comments may be shown as answers.

Output parameter type 'DATETIME' is not a valid SQL type

7 Answers 118 Views

Databases and Data Types

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Tony Girling asked on 26 Feb 2010, 11:54 AM

I have a stored procedure that returns a DateTime result in an output parameter named 'weekEnding'.
The code I'm using to get the result is...

    
            using (var scope = GetNewObjectScope()) 
        
                        { 
        
                            // Humm!! following throws exception on Execute --- "Param type 'DATETIME' is not a valid SQL type." 
        
                            var query = scope.GetSqlQuery("Common.DetermineCurrentActualsWeekEnding ?", null, "out.datetime weekEnding"); 
        
                            var queryqueryResult = query.Execute(null); 
        
                            reportWeek = (DateTime)queryResult.OutParameter["weekEnding"]; 
        
                        }

When I run the code I get a runtime exception:-
Telerik.OpenAccess.OpenAccessException.Failure.Internal, reason; "Param type 'DATETIME' is not a valid SQL type."

Ideas?
Tony.

7 Answers, 1 is accepted

answered on 04 Mar 2010, 10:17 AM

Hello Tony Girling,

You should not use datetime but timestamp when you are defining the parameters. Having this in mind, your code would look like this:

using (var scope = GetNewObjectScope())

{

// Humm!! following throws exception on Execute --- "Param type 'DATETIME' is not a valid SQL type."

var query = scope.GetSqlQuery("Common.DetermineCurrentActualsWeekEnding ?", null, "out.timestamp weekEnding");

var queryqueryResult = query.Execute(null);

reportWeek = (DateTime)queryResult.OutParameter["weekEnding"];

}

Hope that helps.

Sincerely yours,
Petar
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

answered on 08 Mar 2010, 11:26 AM

Petar,

Thanks for your reply. I didn't spot the TIMESTAMP type.

But my query still fails (ignoring the obvious typo in the code example; query typed twice in queryqueryResult).

The GetSqlQuery result has 'query.BackendQuery' threw an exception of type 'Telerik.OpenAccess.Exceptions.InvalidOperationException. Base message:- {"Expected 1 parameters, have 0"}

The stored proc takes no input parameters and only has one output parameter (the TIMESTAMP).

I'm clearly not building the query correctly but cannot find an example or documentation that shows me how.

Any pointers accepted.

Cheers,
Tony.

answered on 11 Mar 2010, 07:51 PM

Hello Tony Girling,

You will have to pass a parameter regardless of the fact that it will not be used. The parameter value will be replaced with the value from the SQL server. So changing your code to something similar to this should do the trick:

scope.GetSqlQuery("Common.DetermineCurrentActualsWeekEnding ?", null, "out.timestamp weekEnding");

var queryqueryResult = query.Execute(DateTime.Now);

reportWeek = (DateTime)queryResult.OutParameter["weekEnding"];

Greetings,
Petar
the Telerik team

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Output parameter type 'DATETIME' is not a valid SQL type

7 Answers, 1 is accepted