Forums (Obsolete)

Member

kj.S1ngh - 5/28/2013 11:35:03 AM

Conversion failed when converting the nvarchar value 'en-us' to data type int

I am having problem a line of code on my homepage as shown below:

Conversion failed when converting the nvarchar value 'en-us' to data type int.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'en-us' to data type int.

Source Error: 

Line 21:         DataSet ds = null;
Line 22:         CMS.DocumentEngine.TreeProvider tree = new CMS.DocumentEngine.TreeProvider();
Line 23:         ds = tree.SelectNodes("CJNI", "/NewsAndEvents/Latest-News/%", "en-us", true, "CMS.News;CMS.bookingevent;", "", "NodeLevel, NodeOrder");
Line 24: 
Line 25:         if (ds.Tables[0].Rows.Count >= 3)

The above code was working fine up until version 6.0 but somehow fails in version 7.0.

Not exactly sure why it is converting the nvarchar value to data type int?

Can somebody please advise on this please?

Thanks


Kentico Support	kentico_filipl - 5/29/2013 6:40:34 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Hello, Regrettably, this seems to be a bug in the current version. I'm very sorry for this inconvenience. We will try to fix it in the next version or in the next hotfix package for this particular Kentico CMS version. Best regards, Filip Ligac


Kentico Support	kentico_filipl - 5/29/2013 9:24:19 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Hello, Was there any stack trace message displayed? It would be really useful if you could send us any more additional information about this error. Thank you in advance. Best regards, Filip Ligac

Member

kj.S1ngh - 5/30/2013 7:04:34 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi Filip,

Sorry for the late reply. I have recently found there are issues with Kentico V6 of the site, so I am to revert back to V6 and step through the code to resolve the issue.

Apologies I don't have the stack trace available but as far as I remember it contained the following SQL query:

SELECT * FROM View_CONTENT_news_Joined
WHERE SiteName = 'CJNI' 
AND NodeAliasPath = '/NewsandEvents/Latest-News/%'
AND DocumentCulture = 'en-us'
AND NodeLevel = 3
AND NodeOrder = 1

And the above query was producing the error below:

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'en-us' to data type int.

Hope this is helpful?


Kentico Support	kentico_filipl - 6/2/2013 10:30:39 PM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Hi, Thank you for additional information regarding this issue. Would it be possible for you to apply the latest hotfix for version 7 (7.0.38) to eliminate the possibility it is a bug in a hotfix version you are currently using? Best regards, Filip Ligac


Member	kj.S1ngh - 6/3/2013 3:44:53 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Thanks filip for the assistance. I will apply the hotfix.


Kentico Support	kentico_filipl - 6/3/2013 3:55:16 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Hi, Please let me know if this suggestion helps you resolve the issue or not. Best regards, Filip Ligac

Member

kj.S1ngh - 6/3/2013 4:11:45 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi Filip,

I will apply this hotfix once I am able to resolve an issue with the Kentico 6 upgrade.

Unfortunately, I had to revert back to Kentico 6 as I came across a database issue again with the same line of code

protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = null;
        CMS.TreeEngine.TreeProvider tree = new CMS.TreeEngine.TreeProvider();
        ds = tree.SelectNodes("Sitename", "/NewsAndEvents/Latest-News/%", "en-Us", true, "CMS.News;CMS.BookingEvent;", "", "NodeLevel, NodeOrder");

The DataSet in the above code comes back as Null. I stepped into the code and noticed that the query that was sent to the database as shown below wasn't
fetching any result back?

SELECT  * FROM View_CONTENT_news_Joined 
WHERE (NodeSiteID = 3) AND (Published = 1) 
AND (DocumentCulture = 'en-US')
AND (NodeAliasPath LIKE N'/NewsAndEvents/Latest-News/%')

On running the query again on View_Content_news_Joined I noticed that few columns contained the wrong data type? For e.g. Document ID column had an 'nVarchar' and vice versa.

It looks like the data had moved to the right into the wrong columns.

Member

kj.S1ngh - 6/3/2013 6:18:50 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi Filip,

Can you advice me on what the Hotfixes were?

The reason I am asking this is because, I suspect the problem may have been
in the Kentico 6 upgrade.

Basically once I upgraded from Kentico 5.5R2 to 6, the script below identified that the table returned contained data inside the wrong columns as mentioned in my previous post.

SELECT  * FROM View_CONTENT_news_Joined 
WHERE (NodeSiteID = 3) AND (Published = 1) 
AND (DocumentCulture = 'en-US')
AND (NodeAliasPath LIKE N'/NewsAndEvents/Latest-News/%')

And due to this the error in Kentico 7 would have caused

failed when converting the nvarchar value 'en-us' to data type int

the problem?

I am not sure if problem in Kentico 6 is related to Kentico 7 upgrade?

Kentico Support

kentico_filipl - 6/5/2013 7:58:40 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi,

Well, if the upgrade script was not completed successfully during upgrade from v5.5R2 to v6, the entire issue could be connected to this error.

If that is the case, can you still trace back the original error message which was displayed during the upgrade from v5.5R2 to v6? It would be quite useful to determine the cause.

But just to confirm, this code was working fine in v5.5R2, then you upgraded to v6 (which ended with errors) and it was still working until you upgraded to v7?

Best regards,
Filip Ligac

Member

kj.S1ngh - 6/6/2013 10:58:25 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi Filip,

The upgrade was successful but somehow the database was messed up during upgrade from 5.5R2 to 6.0 where as mentioned data were in the wrong fields.

Anyhow I downloaded a new instance of Kentico CMS 6.0 and imported the site from 5.5v2 into 6.0.

All is well now and the database columns contain the correct data.

Thanks for the help.


Kentico Support	kentico_filipl - 6/10/2013 5:24:50 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Hi, So did installing a new instance help you resolve the issue with SelectNodes() method as well? Best regards, Filip Ligac

Member

kj.S1ngh - 6/10/2013 5:49:42 AM

RE:Conversion failed when converting the nvarchar value 'en-us' to data type int

Hi Filip,

I am not sure about in Kentico 7 but the main issue was from Kentico 6 where the data was corrupt. So I installed a new instance on Kentico 6 imported the site and them upgraded from there onto Kentico 7.

I am not sure why the database got messed up but installing a new instance on Kentico 6 was the only way to fix the issue.

Regards,
KJ


Member	kj.S1ngh - 6/10/2013 5:51:03 AM RE:Conversion failed when converting the nvarchar value 'en-us' to data type int Also to mention I applied your Hotfix in kentico 7 so I guess that fixed it aswell.