 |
|
|
|
Joined: 9/16/2004
Posts: 225
|
|
|
Hi all, I have a problem with a module that has just started happening. See below. I know this isn't a core DNN module but I'm seriously out of time as the site is supposed to go live more or less now. It's the DotNet Jedi Bussiness Directory and the Classifieds Directory both throwing the same error when trying to add a listing.
A critical error has occurred.
Error converting data type nvarchar to datetime.
In the event lod I get two entries:
Page Load Exception
AssemblyVersion: 04.05.05
PortalID: 0
PortalName: Bike Mad :: The home of bike enthusiasts
UserID: 2
UserName: admin
ActiveTabID: 70
ActiveTabName: Business Directory
RawURL: /Default.aspx?tabid=70&uc=ia&parentid=0&ItemID=0
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer: http://www.bikemad.co.nz/Default.aspx?tabid=70&uc=ia&parentid=0&ItemID=0
UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.11) Gecko/20071127 Firefox/2.0.0.11
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 40a1d591-b674-4dba-86c0-5de9bb1d429d
InnerException: Error converting data type nvarchar to datetime.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Data.SqlClient.SqlConnection.OnError
StackTrace:
Message: DotNetNuke.Services.Exceptions.PageLoadException: Error converting data type nvarchar to datetime. ---> System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.bs_ClassifiedsDB.bs_AddClassifieds(Int32 ModuleId, String UserName, String bs_BusinessName, String bs_Unit, String bs_Street, String bs_City, String bs_Region, String bs_Country, String bs_Postal, String bs_Phone, String bs_Fax, String bs_Email, String Message, String bs_Website, String bs_Hours, String bs_PaymentType, Byte[] Image, String ImageContentType, Int32 ImageContentSize, Byte[] Image1, String ImageContentType1, Int32 ImageContentSize1, Byte[] Image2, String ImageContentType2, Int32 ImageContentSize2, Byte[] Image3, String ImageContentType3, Int32 ImageContentSize3, Byte[] Image4, String ImageContentType4, Int32 ImageContentSize4, Byte[] Image5, String ImageContentType5, Int32 ImageContentSize5, String bs_Keywords, String bs_Promotion, String PosterPassword, String ExpireDate, Boolean Authed, Boolean Featured, Int32 CategoryID) at DotNetNuke.bs_AddItem.cmdUpdate_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---
Source:
Server Name: PRODUCTION
and
General Exception:
AssemblyVersion: 04.05.05
PortalID: 0
PortalName: Bike Mad :: The home of bike enthusiasts
UserID: 2
UserName: admin
ActiveTabID: 70
ActiveTabName: Business Directory
RawURL: /BusinessDirectory/tabid/70/Default.aspx
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer: http://www.bikemad.co.nz/MyProfile/tabid/60/Default.aspx
UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.11) Gecko/20071127 Firefox/2.0.0.11
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: b52581ee-72d6-4b1b-9219-24e1d77a7c57
InnerException: Thread was being aborted.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Threading.Thread.AbortInternal
StackTrace:
Message: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpResponse.Redirect(String url, Boolean endResponse) at System.Web.HttpResponse.Redirect(String url) at DotNetNuke.bs_Dispatch.Page_Init(Object sender, EventArgs e) at System.Web.UI.Control.OnInit(EventArgs e) at System.Web.UI.UserControl.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.AddedControl(Control control, Int32 index) at System.Web.UI.ControlCollection.Add(Control child) at DotNetNuke.UI.Skins.Skin.InjectModule(Control objPane, ModuleInfo objModule, PortalSettings PortalSettings)
Source:
Server Name: PRODUCTION
If anyone knows how to fix this you would be saving my life.
Thanks
Andrew
|
| |
|
|
 | |
|
|
|
That is an SQL error that often occurs in T-SQL statements when concatenating strings, or when passed as a parameter and a CONVERT or CAST is not used.
You really should consult the vendor(s) of the module for support, but if you choose not to, check the Stored Procedures that are getting called by those modules. There will likely be at least one instance where this is occurring. Here are examples to fix this:
DECLARE @SomeString NVARCHAR
SET @SomeString = 'This is a concatenated string and this is a date: ' + @DateString
SET @SomeString = 'This is a concatenated string and this is a date: ' + CAST(@DateString AS DATETIME)
-- another example... (and this is most likely the problem you are experiencing)
SELECT * FROM [dbo].[tablename] WHERE [datetime_field] > @SomeString
SELECT * FROM [dbo].[tablename] WHERE [datetime_field] > CAST(@SomeString AS DATETIME)
|
| |
|
|
| |
|
|
|
Joined: 9/16/2004
Posts: 225
|
|
|
Ok thanks for your reply, I don't seem to ever get a reply from the vendor on support issues so I think this is my best bet on getting this sorted.
Here's the stored procedure that inserts the data into the database but i can't see if this is correct or if it needs adjusting based on what you suggest.
CREATE PROCEDURE dbo.cs_AddClassifieds
@ModuleID int,
@UserName nvarchar(100),
@Title nvarchar(150),
@Spec_SelectCheckboxFeature ntext,
@Image image,
@ImageContentType nvarchar(50),
@ImageContentSize int,
@Image1 image,
@ImageContentType1 nvarchar(50),
@ImageContentSize1 int,
@Image2 image,
@ImageContentType2 nvarchar(50),
@ImageContentSize2 int,
@Image3 image,
@ImageContentType3 nvarchar(50),
@ImageContentSize3 int,
@Image4 image,
@ImageContentType4 nvarchar(50),
@ImageContentSize4 int,
@Image5 image,
@ImageContentType5 nvarchar(50),
@ImageContentSize5 int,
@Author nvarchar(150),
@ContactEmail nvarchar(150),
@ContactInfo nvarchar(2000),
@PosterPassword nvarchar(50),
@ExpireDate datetime,
@Authed bit,
@Message ntext,
@Price nvarchar(50),
@CategoryID int,
@Featured bit,
@ItemID int OUTPUT
as
insert into cs_ClassifiedsItem(
ModuleID,
CreatedByUser,
CreatedDate,
Title,
Spec_SelectCheckboxFeature,
Image,
ImageContentType,
ImageContentSize,
Image1,
ImageContentType1,
ImageContentSize1,
Image2,
ImageContentType2,
ImageContentSize2,
Image3,
ImageContentType3,
ImageContentSize3,
Image4,
ImageContentType4,
ImageContentSize4,
Image5,
ImageContentType5,
ImageContentSize5,
Author,
ContactEmail,
ContactInfo,
PosterPassword,
ExpireDate,
Authed,
Message,
Price,
Featured,
CategoryID
)
values (
@ModuleID,
@UserName,
getdate(),
@Title,
@Spec_SelectCheckboxFeature,
@Image,
@ImageContentType,
@ImageContentSize,
@Image1,
@ImageContentType1,
@ImageContentSize1,
@Image2,
@ImageContentType2,
@ImageContentSize2,
@Image3,
@ImageContentType3,
@ImageContentSize3,
@Image4,
@ImageContentType4,
@ImageContentSize4,
@Image5,
@ImageContentType5,
@ImageContentSize5,
@Author,
@ContactEmail,
@ContactInfo,
@PosterPassword,
@ExpireDate,
@Authed,
@Message,
@Price,
@Featured,
@CategoryID
)
SELECT
@ItemID = @@Identity
GO
|
| |
|
|
| |
|
|
|
I don't see anything immediately wrong with that SP. Can you paste the table structure into the thread? If you are using SQL Server Management Studio, you can right click on the [cs_ClassifiedsItem] table and choose "Script Table As", then "CREATE to", and finally "New Query Window". Then you can copy that T-SQL script and put it in this thread.
|
| |
|
|
| |
|
|
|
Joined: 9/16/2004
Posts: 225
|
|
|
Hi Will,
Thank you for your help. I have had some support from Dotnet Jedi (right on christmas too I might add) regarding this issue. Basically my database was set to use British English instead of English US. This was the issue with the datetime format error. Really strange as the modules worked when I first installed and tested them, then they just stopped at some point. We ran an sql query against the database to switch the language to English US and everything seems to be fine now.
Andrew
|
| |
|
|
| |