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

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

How do I change the SelectParameter to accept a datetime sql type?

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :ASP.Net

Hi all, 

If I ran my sproc from query analyzer, I get the correct results(data is filtered by datetime), i.e:

 exec Uds.Dashboard_GetJobs '9/20/2010 11:01:23 PM'

However, if I ran it from the Asp.Net page, (ALL records are showed). Looking at the SQL Profiler I see the following call:
 exec sp_executesql N'exec Uds.Dashboard_GetJobs',N'@date nvarchar(21)',@date=N'9/20/2010 11:01:23 PM'

Running both statements above in the query analyzer it WILL return me different results. Why???

Someone mentioned that it looks like I don't have correct data type specified for SQLParameter in .Net application.
I was asked to create something like: cmd.Parameters.Add("@Date",SqlDbType.DateTime).Value = ..

Well, my code in the asp.net is in this form:

   <asp:SqlDataSource ID="sourceRealtimeLogging" runat="server"
      ConnectionString="<%$ ConnectionStrings:UctConnectionString %>"
      SelectCommand="exec Uds.Dashboard_GetJobs">
        <asp:ControlParameter ControlID="UpdatedTimeUTC" Name="date" PropertyName=&quo

View Complete Post

More Related Resource Links

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Password change control won`t accept new password


Hi guys

I am developing a shopping cart with asp.net and sqlexpress. I am using aspnet authentication components to create a backend page. I designed the site to let in only authorized users in. 

Yesterday, I forget my password to login to backend and asked system recover my password. with recover password of the asp.net I was able to receive the temp password. I took that and logged in, of course asp.net forward me to password change component which I am having problem with. 

Every time I change my password , password change shows that I was successfull but after clicking on the continiue button password change component comes back. I went to properties of password change component and entered the main default page as destination but it is not helping. 

I close the IE clear the cache , even restart the machine. When I login always taking me to the password change page.

So, I need your help with this. Please let me know your inputs.


How to change The DateTime Automatically according to User TimeZone Offset?

I have an application in which we are displaying DateTime in many pages. we want to display these DateTime's according to UserTime zone. UserTimeZone will be in database. All DateTime values will be saved int UTC format. My Requirement is I want set the TimeZoneOffset of user in ServerSide based on the User TimeZoneOffset All the DateTime values needs to be Added/Substracted while showing the DateTime values to user. I dont want to manully add/substract TimeZoneOffset value in all pages. Is there any way the DateTime  automatically change to the User Timezone.  How can I do this? Thanks in Advance ChellamNaidu

Read an XML file, setting uname/pw, Accept, Content-Type

Hi; To read some REST XML data we need to set the username, password, and tell it for the http request Accept: application/xml Content-Type: application/xml Is there a sample anywhere showing how to do this? thanks - daveVery funny video - Reporting as a Metaphor

Help to resolve an error "The conversion of a char data type to a datetime data type resulted in an

Why am I getting an error when executing this: select convert(datetime, '2010-09-12T18:11:48', 120) The message is: "Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I don’t get the error if I remove “T” from the string. But I need it to work with “T”.

can't change varchar(13) back to a datetime.

SQL Server 2005  I had a date field that was acting weird.  I decided it didn't really need to be a datetime type and changed it to a varchar(13) which it happily let me do.  Opps, I really do want it to be a date time field...  But now it won't let me take it back.  Oddly, it also won't let me change the data in the field, or delete the data in the field.  When I try to change the data I get the following... String or binary data would be truncated.  The statement has been terminated. When I try to modify the field I get the following. first i get a warning... - Warning: Data might be lost converting column 'EODate' from 'nchar(13)'. then i get an error when I say yes 'ExecutiveOrder' table - Unable to modify table.   Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. Ok, I'm not the sharpest tool in the shed...  Any suggestions?  Thanks for your help.

Handling Type namespace/assembly change during serialization

We needed to move a class from one assembly to another due to referencing issues.  However, we have Workflows in the field where this type is serialized into them and we get the Failed to load type error.  I was wondering if there was anything like AssemblyResolve for handling type load failures where we can correct the type and load the correct one?

Error converting data type nvarchar to datetime.

hi all, I simply explain my application.plz try to understand. In my applicationi want to generate one complaint id.one customer may give two or more complaints at a time.suppose,he give two or more pnr numbers and their status and prioirty may differs.for this complaint i need to generate one common complaint id.how it is possible..?with some constant rows. am getting error in Class File near CLUser_Logged_DTClass File using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Web.Configuration; /// <summary> /// Summary description for BalComplaints /// </summary> public class BalComplaints { public BalComplaints() { // // TODO: Add constructor logic here // } int SNo, Complaint_Type_ID, Caller_Type_ID, No_of_Articles, Article_ID, CLPriority_ID, Status_ID, Origin_ID, Destination_ID; public int SNo1 { get { return SNo; } set { SNo = value; } } public int Caller_Type_ID1 { get { return Caller_Type_ID; } set { Caller_Type_ID = value; } } public int Destination_ID1 { get { return Destination_ID; } set { Destination

Where is 'Change query type' (Enterprise manager) equivalent in the Management Studio


In sql server 2000 Enterprise manager, there was a ‘Change Query type’ icon and on clicking it, it gave you the option of changing the current query to a

Insert into
Insert From
Create Table

I can’t seem to find this in SQL Server Management Studio, does it exist somewhere or is there an equivalent way of doing it. Any help would be appreciated.




Data Base Properties column Identity value will not accept value change


In the tutorial I am doing I have been requested to change a field in the colums properties of a table.  The ID column is currentl displaying NO and NO again for "is aIdentity.  I can Highlight the values but cannot change to yes.  either in the properties talbe or in the properties window on the right hand side of IDE interface.  How do I change that value?


SSRS Change field properties dependent on the render type


I've built a report that has background images with text over the top, which when rendered as Word or Excel the images don't show and because the client has requested the text is white against the image, the text isn't visible.

What I'd like to do is run code within the report that identifies the render type and if xls will change the text color through an expression.

Any ideas would be greatful.


Alerts - Change Type section


In the "Specify the type of changes that you want to be alerted to" area, I want to set ONLY the "Alert Me About" to: ADDED ITEMS and CHANGED ITEMS. I do not want to know about DELETED ITEMS. Can you only choose one? I seem to only be able to do that so I am stuck with the ALL CHANGES setting.  My Outlook Inbox can sometimes be filled with 40 deleted items when our site editor gets busy cleaning house. They claim you can make it so I stop seeing all the deleted alerts, but I have yet to figure it out.

Suggestions? Tricks? Please reply if you have figured out the magic. Thanks. :)

(Oh and I am still in version 2003 but we are upgrading to 2010 in a few months and am praying it has a fix.)

Change the content type for multiple list items


I have a document library which is configured to manage content types.  Users will be expected to upload multiple documents into the library.  Then they will be expected to choose the content type of the file and fill in the required metadata.  The only difference in these content types is the template which will be used to create new files of the specific content type so the columns are identical for each content type.

I have created a public view with inline editing enabled but Content Type is not an editable field.

The problem I have is that when I enable Inline editing, the users don't have an opportunity to select the content type, even though the files have just been uploaded and have not been checked in.

My question is this; how can users change the content type of multiple items without having to go through the complete New Item form? 

With literally thousaands of documents to be imported, having the users fill out a separate form for each document when many will have the same column values is a show stopper for our rollout.  It will take too much time.  The project will be a failure. 

I've also tried to create a datagrid view but the same problem exists.  Content type is not an editable column value.

Thanks for helping.


Programmatically change DisplayName/ Title for custom content type


I've created a custom content type with the following code.

if (currentWeb.ContentTypes["My Content Type"] == null)
  var myContentType =
    new SPContentType(currentWeb.AvailableContentTypes[new SPContentTypeId("0x01")],
              currentWeb.ContentTypes, "My Content Type");


After that I add this content type to a generic list. If I now click on "New element" on my list ribbon I can add a new item with displayname "My Content Type". How can I change this value as I want to name my content types like "xxx content type" but don't want to see "xxx content type" in the "New element" action.

I expected the name of the content type to be some kind of internal and I'm looking for something to be change the displayname or title to be able to display something different. If I didn't made myself clear, I'll try to be more explicit. Thanks in advance.

How to obtain all digits from a DateTime type when calling cmd.ExecuteScalar() ?


Hello there,

I hope this is the right forum for this question.
The statement , object obj = cmd.ExecuteScalar()  is returning  2010-10-22 18:25:36
However is not bringing the last 3 digits (miliseconds).

This is actually calling the following SQL query: select max(timeStamp) from Table1 which returns 2010-10-22 18:25:36.713 on query analyzer. As you can see "713" is not being included ...

How do I bring this value?

Thank you!





System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.


 Hi All,

I have recently moved a ASP.NET webserver from windows XP to
windows server 2008.
The website runs fine apart from anywhere that pulls a date
or uses the date from a JS date picker etc.

The error I am receiving is:

System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.

Source Error:

Line 79:         cmd1.Parameters.AddWithValue("@EndDate", lblSearch2.Text);
Line 80:         conn.Open();
Line 81:         SqlDataReader reader = cmd1.ExecuteReader();
Line 82:         if (reader.HasRows)
Line 83:         {

Source File: c:\inetpub\wwwroot\folderx\pagex.aspx.cs    Line: 81

Stack Trace:

[SqlException (0x80131904): Error converting data type nvarchar to datetime.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951450
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849003
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject s

SQL Execution Error, Specified type is not registered on the target, when adding DateTime parameters


I have SQLServer 2008r2 installed on a Windows Server 2008 R2 machine.  I'm trying to build a report using SSRS - and as soon as I try to add some DateTime parameters to the query and execute, I get

SQL Execution Error.

Executed SQL Statement:  blah blah Error

Source: System.Data

Error Message: Specified type is not registered on the target server.System.DateTime, mscorlig, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089.

I tried taking the suggestion from another thread and installing the .NET Framework 2.0 from: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=B44A0000-ACF8-4FA1-AFFB-40E78D788B00, but I received the message that it is already installed.  How do I get past this error?

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