.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

DATE Output Parameters for SPROC

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :ASP.Net

Hello everyone,

I have a question.

How do we define date output parameters for a stored procedure?

before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.


I am repeating that I do I am not referring to the sql definition .

I would appreciate your help.

Thank you

View Complete Post

More Related Resource Links

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

Entity Framework and stored procedures with OUTPUT parameters (suggest API)

EF does handle stored procedures with OUTPUT parameters, but the generated methods are not the easiest to work with or safest you could ask for. The ObjectParameter objects you have to create, pass to the method and extract the value from are not type checked and reallyObjectParameter nameParam = new ObjectParameter( "Name", typeof(string)); ObjectParameter ageParam = new ObjectParameter( "Age", typeof(int)); context.GetPersonsNameAndAge( personId, nameParam, ageParam); string name = Convert.IsDBNull(nameParam.Value) ? null : (string)nameParam.Value; int? age = Convert.IsDBNull(ageParam.Value) ? null : (int?)ageParam.Value; is not what I would want to have to write.The ObjectParameter objects do make some sense for stored procedures that return both a resultset and some OUTPUT parameters, because in that case the OUTPUT parameters are only available after the resultset has been consumed and therefore you need some objects that will be populated after the ObjectResult<...> gets processed but for the stored procedures that do not return any resultset, there is no reason not to create the ObjectParameter objects within the method and extract the values there as well. So that the procedure above may be called asstring name; int? age; context.GetPersonsNameAndAge( personId, out name, out age); Things are not so simple for procedures returning both. I'm

Interesting problem with getting OUTPUT parameters from SQL Server using C#

 I have the following SP: Create procedure dbo.example(@test INT OUTPUT)ASBEGINSET @test = 5SELECT * from UsersENDGO  and when I try calling it from C# using an SqlCommand with adding an output parameter and getting it's value, I get that the value is NULL. (I should mention that I use ExecuteReader)However,  if I alter the SP and remove the select line, the c# code works and returns the value 5. 

how to access output parameters from workflow

hi, i am using sequential work flow, now i need to acess the reuslt from the workflow to the host(in my case windows forms).  i can access workflowcompletedeventargs , yes i can acess in the workflow completed event but i need those values to be set to my FORM (it si giving me thread exception) if i  try to acess my form object in workflow completed event can any one give me an example   thanks shankar.

Allow Date parameters entered with or without "/"

We have a lot of SSRS reports with date parameters of format "M/d/yyyy".  Now, Our users request to be allowed to enter those dates with or without "/". Is that doable? If yes, how?

Interesting problem with getting OUTPUT parameters from SQL Server using ASP


I had an sqldatasource. I had set its parameter as follows

<asp:SqlDataSource ID="FvDs" runat="server"
InsertCommand="INSERT INTO [Type] ([ShortName], [LongName], [Version]) VALUES (@ShortName, @LongName, @Version);

Then i had retrived the value in OnInserted event of the datasource

protected void FvDs_OnInserted(object sender, SqlDataSourceStatusEventArgs e)
        FvDs.SelectParameters["ID"].DefaultValue = e.Command.Parameters["@ID"].Value.ToString(); 

For the above code it is working fine.

But now i changed the code as follows:

<asp:SqlDataSource ID="FvDs" runat="server"

and here is the code for my stored procedure

ALTER PROCEDURE dbo.usp_InsertData
	@ShortName varchar(20),
	@LongName varchar(50),
	@Version varchar(20),
			INSERT INTO [Type] ([ShortName], [LongName], [Version]) VALUES (@ShortName, @LongName, @Version)

SSRS - Date Format & Parameters Refresh.



I am using SSRS 2005 with Enesys Add on Tool for reports.

I have used date format as "dd-MMM-yyyy" in the application and used the same in the report.

Issue : 1

I want to convert this format into a format where i can sort the date fields as and when exported to excel sheets.

Issue : 2

Some of the values are null and when exported to excel cant see the null values. I am using Format function in the conversion so that if the value is nothing, show nothing or show the value.

Need to resolve this as when the report is exported to excel i can see null values or squares in the columns when applying filters.

Issue : 3

The input paremeters are refreshed everytime i select a new value in other parameters. There is no dependency on the input parameters.

Please help me out in resolving these issues ASAP.

Need to submit these today EOD.

Thanks in Advance. 


Separate Date and Time report parameters in SSRS 2005



Is it possible to declare separate report parameters in SSRS 2005 as Date and Time?

I want the user to be able to enter a date and a time.

When I declare the parameters in my stored procedure as datetime, in SSRS the parameter just shows as a Date only.


sproc calling sproc - output value problems


I have the following two sprocs:

USE [Ecotech]
ALTER PROCEDURE [dbo].[Ecotech_AddUser]
@aspnet_id uniqueidentifier,
     @last_name nvarchar(255),
     @first_name nvarchar(255),
     @address1 nvarchar(255),
     @address2 nvarchar(255), 
     @zip nvarchar(10),
     @city nvarchar(255),
     @tlf_mobile nvarchar(16),
     @tlf_home nvarchar(16),
     @tlf_office nvarchar(16),
     @country nvarchar(16),
     @email nvarchar(50),
     @status int output

    if (select COUNT(*) from ecotech_users where aspnet_id = @aspnet_id) > 0
            set @status = 0
            into ecotech_users 

How to call a Stored procedures with varchar output parameters in LINQ


I have a stored procedure with the following structure:

CREATE procedure EditFeriodigiesForm
@EditID int,
@producer varchar(50) output,
@IssuedDate datetime output,
@stadio varchar(50) output,
@Method varchar(50) output,
@Monades_N varchar(50) output,
@Monades_P varchar(50) output,
@Monades_K varchar(50) output,
@comments varchar(100) output,
@responsible varchar(50) output
Begin Try
	Begin tran
SELECT @IssuedDate=IssuedDate,@producer=ProducerID,@stadio=KalliergitikoStadio,@Method=Method,@Monades_N=Monades_N,@Monades_P=Monades_P,@Monades_K=Monades_K,@comments=Comments,@responsible=Responsible from ferti_odigies WHERE FertOdigiesID=@EditID
end try
	Begin catch
	if @@error>0
declare @ErrMsg varchar(4000),
		@ErrSeverity int
Select @ErrMsg=Error_Message(),

Raiserror(@ErrMsg ,@ErrSeverity,1)

end catch 

Now i need to call it in LINQ.I have done the mapping thing and the SPROC is ready to be called in my form.aspx.cs form, as following:
  int id = Edit_id;
            int? producer;
            DateTime date =Convert.ToDateTime(txtdate.Text);
            string stadio;
            string method="";
            string monadesn="";
            string monadesp="";
            string mo

string to datetime conversion using date parameters in SSRS


Hello, I have a database field that I am pulling that is type string, but the format of each value is mm/dd/yyyy.  I want to convert this to a datetime format (minus the time) so that i can compare this value to a parameter date range (begin date and end date) that are type datetime so that i can use the calendar control in my report.  i've tried in my select statement to use select.....convert(datetime, stringfieldname, 101), but I get an error that says "Conversion failed when converting datetime to character string". 

I am also open to converting the datetime parameters to strings to do the comparison, but not sure the best way to do this either.


how to convert date type in SQL query output ?


In my database when anyone registers - By default the registration time is saved as UTC universal time zone.

But i want to display everything on the website in (GMT-05:00) Eastern Time (US & Canada)

I am using this SQL QUERY to display registration date:

SELECT TOP (5) Username, CustomerID,  RegistrationDate = CONVERT(VARCHAR(12),RegistrationDate,107)  FROM Customer ORDER BY RegistrationDate DESC

And, i am displaying it like this:

<asp:Label ID="nameLabeldate" runat="server" Text='<%# Eval("RegistrationDate") %>' />

The problem is the output is getting displayed as : 10/20/2010 1:36:01 PM

I know RegistrationDate is datetime stamp that's why i am getting output in this format.

But i want to display RegistrationDate as: Oct 20, 2010 at 1:36pm -> according to (GMT-05:00) Eastern Time (US & Canada) ORDER BY RegistrationDate in Desc (top most will be last registered user)

I need help with sql query by which i should be able to accoplish my desired output..please help...

Input and output parameters for stored procedures in VB.NET


I have several VB.NET functions which pass and receive values from executed stored procedures using parameters. However, the latest method I am using seems to be a little too specific on the datatype and data length of the parameters for my liking (i.e. LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource). For example:

Public Function LogError(ByVal strErrSource As String, ByVal strErrCode As String, ByVal strErrDesc As String) As String  
    Dim AerialConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("aerialConnectionString").ConnectionString)  
    Dim RetVal As SqlParameter  
        Dim LogActivityCommand = New SqlCommand("spLogError", AerialConnection)  
        LogActivityCommand.CommandType = CommandType.StoredProcedure  
        'Add input parameters  
        LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource &

SSRS Date Format in Parameters



Can we change the date format in the default parameters pane.(For eg : When we create a new parameter and select datatype as date time in report parameters.)

Default settings for this date picker control is mm/dd/yyyy.

I need to change the date to dd/mm/yyyy.

Is it possible to the change this date format to dd/mm/yyyy or any other custom format?

Thanks in Advance.

DAAB how to get multiple output parameters ?


A stored procedure is returning 2 output parameter. I tried to catch them in DAL using DAAB but couldn't succeed. Actually control did not returned on next lines. Please guide me.


using (DbCommand dbCommand = db.GetSqlStringCommand("p_AddLeaveApplication"))
                dbCommand.CommandType = CommandType.StoredProcedure;
                db.AddInParameter(dbCommand, "@EmployeeId", DbType.Int16, leave.EmployeeId);
                db.AddInParameter(dbCommand, "@DateTo", DbType.Date,string.Format("'{0:yyyy-MM-dd HH:mm:ss}'",  leave.DateTo));
                db.AddInParameter(dbCommand, "@DateFrom", DbType.Date, string.Format("'{0:yyyy-MM-dd HH:mm:ss}'", leave.DateFrom));                    
                db.AddInParameter(dbCommand, "@LeaveType", DbType.String, leave.LeaveType);
                db.AddOutParameter(dbCommand, "@Result", DbType.Int16, 1);
                db.AddOutParameter(dbCommand, "@LeaveId", DbType.Int16, 1);
                LeaveId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@LeaveId"));
                return result = Convert.ToInt16(db.GetParameterValue(dbCommand, "@Result"));

Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend