Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 17th, 2004, 06:40 AM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error converting data type nvarchar to datetime.

First, I am relatively new to ASP.NET so bear with me :).

I am trying to insert a string into a datetime column on a MSSQL 2000 SP3a when I encounter this error (see subject). My system locale is 1044 (Norwegian) and I experienced the same error in ASP 3.0 but I were able to work around it by declearing a LCID=1044 session variable, however in ASP.NET 1.1 I have had no such luck. When i execute the stored procedure in Query Analyzer it works fine.

I am fairly sure that it has something to do with system locales but I cant find anything on the web that covers this.

Anyway the code is as follows,

First I create the date,

Code:
          msgDateStart.text = now()    
        dim intHour as integer = Hour(now)
        dim intMinute as integer = Minute(now)
        dim intSecond as integer = Second(now)
        dim intDay as integer = Day(now) +5
        dim intMonth as integer = Month(now)
        dim intYear as integer = Year(now)

        msgDateEnd.text =  intDay & "." & intMonth & "." & intYear & " " & intHour & ":" & intMinute & ":" & intSecond


then I send the data to a stored procedure

Code:
    sub addArticle(obj as object, e as eventargs)
        dim con as SqlConnection
        dim cmd as SqlCommand
        dim strMessageType as integer = 1
        dim strUserID as integer = 1

        con = New SqlConnection(ConfigurationSettings.AppSettings("connPhoenixTest"))
        con.Open()
        cmd = New SqlCommand
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "uspNewMessage"
            With cmd.Parameters
            .Add("@msgType", strMessageType)
            .Add("@msgTitle", msgTitle.text)
            .Add("@msgHeader", msgHeader.text)
            .Add("@msgBody", msgBody.text)
            .Add("@msgUserID", strUserID)
            .Add("@msgPublished", msgPublished.selecteditem.value)
            .Add("@msgFrontPage", msgFrontPage.selecteditem.value)
            .Add("@msgImportance", msgImportance.selecteditem.value)
            .Add("@msgResponsibleUser", msgResponsibleUser.selecteditem.value)
            .Add("@msgDateStart", msgDateStart.text)
            .Add("@msgDateEnd", msgDateEnd.text)
        End With
            cmd.Parameters("@msgType").Direction = ParameterDirection.Input
            cmd.Parameters("@msgTitle").Direction = ParameterDirection.Input
            cmd.Parameters("@msgHeader").Direction = ParameterDirection.Input
            cmd.Parameters("@msgBody").Direction = ParameterDirection.Input
            cmd.Parameters("@msgUserID").Direction = ParameterDirection.Input
            cmd.Parameters("@msgPublished").Direction = ParameterDirection.Input
            cmd.Parameters("@msgFrontPage").Direction = ParameterDirection.Input
            cmd.Parameters("@msgImportance").Direction = ParameterDirection.Input
            cmd.Parameters("@msgResponsibleUser").Direction = ParameterDirection.Input
            cmd.Parameters("@msgDateStart").Direction = ParameterDirection.Input
            cmd.Parameters("@msgDateEnd").Direction = ParameterDirection.Input
            cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Close()
        con.Dispose()
    end sub
the stored procedure looks like this,

Code:
CREATE PROCEDURE uspNewMessage
    @msgType [int],     
    @msgTitle [varchar] (150),
    @msgHeader [varchar] (250), 
    @msgBody [text],
    @msgUserID [int],
    @msgDateWritten [datetime] = NULL, 
    @msgPublished [bit],
    @msgFrontPage [bit],
    @msgDateStart [datetime] = NULL,
    @msgDateEnd [datetime] = NULL,
    @msgResponsibleUser [int] = NULL,
    @msgImportance [int] = NULL

AS
BEGIN    
    SET @msgDateWritten = GetDate()

END

BEGIN
    INSERT INTO msgArticle(
        [msgType], 
        [msgTitle], 
        [msgHeader], 
        [msgBody], 
        [msgUserID], 
        [msgDateWritten], 
        [msgPublished], 
        [msgFrontPage], 
        [msgDateStart], 
        [msgDateEnd],
        [msgResponsibleUser],
        [msgImportance]        
        )
    VALUES (
        @msgType,     
        @msgTitle,
        @msgHeader, 
        @msgBody,
        @msgUserID, 
        convert(datetime, @msgDateWritten, 103), 
        @msgPublished,
        @msgFrontPage,
        convert(datetime, @msgDateStart, 103),
        convert(datetime, @msgDateEnd, 103),
        @msgResponsibleUser,
        @msgImportance
        )
END
GO


Andreas
--
I had forgotten that!
My mind has an amazing capacity to protect itself...

Patience, Grasshopper, and all shall be revealed.
 
Old August 17th, 2004, 08:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

With .NET it's important to get the type right. You can't send a string for a date.. it will error (as you see).. you must convert the string to a date first - then send it to the Stored Proc.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee
 
Old August 18th, 2004, 03:03 AM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the pointer!

Dim datDateStart as DateTime = Convert.ToDateTime(msgDateStart.text)
Dim datDateEnd as DateTime = Convert.ToDateTime(msgDateEnd.text)

Solved the problem.

Best Regards,

Andreas
--
I had forgotten that!
My mind has an amazing capacity to protect itself...

Patience, Grasshopper, and all shall be revealed.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error converting datetime from character st vinod_yadav1919 SQL Server 2000 2 September 10th, 2010 10:11 AM
Error converting data type varchar to int. - SQL baysaa SQL Server ASP 1 March 16th, 2008 11:56 PM
Syntax error converting datetime from character st kkrish SQL Server 2000 11 September 6th, 2006 06:46 AM
Syntax error converting datetime from character st Uppa SQL Server 2000 2 February 8th, 2005 08:01 PM
Error converting data type varchar to float. Trojan_uk SQL Server ASP 5 April 27th, 2004 02:56 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.