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.
Error converting data type nvarchar to datetime.
