locked
how to convert existing SQL Table values from NVARCHAR to VARCHAR? RRS feed

  • Question

  • User2142845853 posted

    I have an existing sql database and a table used for months with alot of data.  But now one of the columns must be expanded for just text, 2000 characters to about 7000.

    PROBLEM: initially the table was setup with many columns of NVARCHAR which means UNICODE, 2 bytes per char.  Finding out now that its running out of room for the new columns and now one has to be expanded 3x-4x.  I tried to CAST it as nvarchar(6000) ... etc...  NO!  the max limit is 8000 bytes so nvarchar(4000) = 8000 or so; 

    Tried to cast from nvarchar(2000) to varchar(6000) and copy across but its all null in that field.  There are over 500+ records so doing it manually is out (at least the last resort)

    What is required to make the conversion?

    thanks

    Thursday, September 26, 2019 12:11 AM

Answers

  • User-719153870 posted

    Hi rogersbr,

    Can you share us the data type of your current column?

    I mean what is the exact number of this 'nnn' in your current column's datatype nvarchar(nnn)?

    I have built a demo table and everything worked fine:

    create table tab1
    (
    id int identity(1,1),
    name nvarchar(max),
    )
    
    --insert into tab1 values('a')
    
    declare @i int
    declare @s nvarchar(max)
    set @i=1
    set @s=''
    while @i<=8000
    begin
    set @s+='s'
    set @i+=1
    end
    insert into tab1 values(@s)
    
    select * from tab1
    select LEN(name) from tab1 where id=1
    --select LEN(name) from tab1 where id=3
    
    ALTER TABLE tab1 ALTER COLUMN name VARCHAR (max)
    select * from tab1

    Sorry I could't reproduce the current problem, please provide more information.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 26, 2019 6:01 AM
  • User475983607 posted

    rogersbr

    This error only happens when the StringLength(size) field is exceeded.  What I expect is that if the length is 7002 chars it fills the applicable error message so the user can change the value.  All it does is throw the exception at the dropdown list.

    I do not see code that fills the dropdown options.  Did you forget to populate the ViewData or the model property that hold the list Items?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 8, 2019 5:23 PM
  • User-821857111 posted

    See this code here:

    {
                if (ModelState.IsValid)  
                {
                    db.Entry(AstrReport).State = EntityState.Modified;
                    db.SaveChanges();
                    TempData["Message"] = "Edited " + AstrReport.ReportNumber;
                    return RedirectToAction("Edit");
                }
                return View(AstrReport);
            }

    It tends to confirm what I suggested 3 weeks ago:


    Here's my guess - you are unable to save data that exceeds the limit you have put on the column, and are possibly hiding an exception that gets raised. Your application continues to execute, but it is now in an unknown state. It is trying to render a view for which there should be data to populate a dropdown, but the data doesn't exist because of the previous exception. 

    At the moment, when the ModelState is invalid, you simply return the View. I suspect that the view relies on one or more DropdownLists. You don't appear to be repopulating the dropdownlist(s) when the ModelState is invalid. so the page is trying to render, but there is no data for the lists. That's why you get the error The ViewData item that has the key 'NameASTRtype' is of type 'System.String' but must be of type 'IEnumerable<SelectListItem>'. I would expect to see code like this:

    if (ModelState.IsValid)  
    {
        db.Entry(AstrReport).State = EntityState.Modified;
        db.SaveChanges();
        TempData["Message"] = "Edited " + AstrReport.ReportNumber;
        return RedirectToAction("Edit");
    }
    // repopulate dropdownlists etc
    ViewData["NameASTRtype"] = <some selectlist items> return View(AstrReport);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 24, 2019 6:37 AM

All replies

  • User-719153870 posted

    Hi rogersbr,

    According to your description, if you are trying to expand capacity in sql by changing a NVARCHAR() column to a VARCHAR() column, you might want to take a look at the SQL ALTER TABLE Statement.

    Use the ALTER TABLE - ALTER/MODIFY COLUMN, like below:

    ALTER TABLE Users ALTER COLUMN UName VARCHAR (6000)

    And you will get:

    Hope this could help, or if i misunderstood anything, please feel free to let me know.

    Best Regard,

    Yang Shen

    Thursday, September 26, 2019 1:35 AM
  • User2142845853 posted

    Yes it has to alter the table, what will happen to the 100s of entries for that column?  They are stored as NVARCHAR unicode 16 bits, but all the existing values must be converted down to VARCHAR 8 bits.  Will this command wipe out the values then re-define this column?  Or will it convert each value and save in the new table?  

    Am assuming this has to be cast to a new table? is sql smart enough to change the entire table and save it?

    Thursday, September 26, 2019 3:04 AM
  • User2142845853 posted

    I ran the command but it got a warning saying this varchar(6000) will exceed the maximum allowable size of 8040 bytes ? 

    Is it possible to run a command that finds any nvarchar(nnn) and changes it to varchar(nnn) ?   does each column have to be manually changed? 

    Thursday, September 26, 2019 3:16 AM
  • User-719153870 posted

    Hi rogersbr,

    Can you share us the data type of your current column?

    I mean what is the exact number of this 'nnn' in your current column's datatype nvarchar(nnn)?

    I have built a demo table and everything worked fine:

    create table tab1
    (
    id int identity(1,1),
    name nvarchar(max),
    )
    
    --insert into tab1 values('a')
    
    declare @i int
    declare @s nvarchar(max)
    set @i=1
    set @s=''
    while @i<=8000
    begin
    set @s+='s'
    set @i+=1
    end
    insert into tab1 values(@s)
    
    select * from tab1
    select LEN(name) from tab1 where id=1
    --select LEN(name) from tab1 where id=3
    
    ALTER TABLE tab1 ALTER COLUMN name VARCHAR (max)
    select * from tab1

    Sorry I could't reproduce the current problem, please provide more information.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 26, 2019 6:01 AM
  • User2142845853 posted

    Ok I think I could modify the database by using the DESIGN mode.  I changed many of the vars from NVARCHAR to VARCHAR and reduced the overall size significantly.

    BUT when I changed one string for a big text box, it was nvarchar(2048) and made it varchar(6000)  so even with that the overall size went from 1.7Mb to .8Mb after all of the changes.  so I tried 2300 chars? saved it fine.  tried 4500 chars? fine tried 5200 chars now it failed, Visual Studio trapped a fault for a completely different control, a dropdown list saying the items must be type dropdownlist, not type String.  

    This has worked untouched for over a year, the dropdown list is all there no problems but when I pushed the size limit hoping to see my error message defined in the model? of max size 6000, it caused some other control to fail.  Seems like its corrupting data now? Any ideas?

    Most all fields are type String, knowing how weak and sensitive SQL is, I just save in string then convert to int, double or? as needed later. 

    Is there a test or analysis tool that can check the table and all? thanks

    Thursday, September 26, 2019 3:31 PM
  • User475983607 posted

    Whether you can convert from NVARCHAR to VARCHAR depends on your data.  If all the character data fits in a byte (UNICODE 0-65,535) then you're okay. 

    Use NVARCHAR(MAX) and VARCHAR(MAX) if you need more than 8000 bytes.  VARCHAR(MAX) can store 2 GB.

    Thursday, September 26, 2019 6:37 PM
  • User753101303 posted

    Hi,

    One step at a time please so that we don't have to try to shot at a moving target. "saying the items must be type dropdownlist, not type String" seems to show that you changed your table successfully but ran into some UI related error? 

    The exact error message could help. For now it seems you try to assign a string variable to a dropdown control rather than it irs property value. Seeing the line of code in addition to the error message could help.

    Edit:  not directly related but after you may want to address "Most all fields are type String, knowing how weak and sensitive SQL is, I just save in string then convert to int, double or? as needed later" which seems based on some very wrong assumption. A db handles data types just fine. A common problem is confusing the "native" value stored in your db or in a C# variable and how this value is converted to a string for being shown which depends on which country convention is used.

    Thursday, September 26, 2019 7:21 PM
  • User2142845853 posted

    Hi,

    One step at a time please so that we don't have to try to shot at a moving target. "saying the items must be type dropdownlist, not type String" seems to show that you changed your table successfully but ran into some UI related error? 

    The exact error message could help. For now it seems you try to assign a string variable to a dropdown control rather than it irs property value. Seeing the line of code in addition to the error message could help.

    Edit:  not directly related but after you may want to address "Most all fields are type String, knowing how weak and sensitive SQL is, I just save in string then convert to int, double or? as needed later" which seems based on some very wrong assumption. A db handles data types just fine. A common problem is confusing the "native" value stored in your db or in a C# variable and how this value is converted to a string for being shown which depends on which country convention is used.

    I changed the type, but got a very strange response.  I made one field 6000 (chars) and tested it.

    i saved 4000 chars just fine

    i saved 5300 or whatever it was and now the debugger trapped something that said variable (whatever) must be type dropdown list.

    That is what happened when testing this out. i didnt try changing anything else

    as if its so weak and fragile it cant even store the values without corrupting something else.  have been designing it so that there's a table just for this lousy field of 6000 chars since it cant co exist with other variables and not throw errors/exceptions

    Monday, September 30, 2019 8:36 PM
  • User475983607 posted

    i saved 5300 or whatever it was and now the debugger trapped something that said variable (whatever) must be type dropdown list.

    That is what happened when testing this out. i didnt try changing anything else

    Sounds like a coding error but we cannot see the code.

    as if its so weak and fragile it cant even store the values without corrupting something else.  have been designing it so that there's a table just for this lousy field of 6000 chars since it cant co exist with other variables and not throw errors/exceptions

    You have a lot of unique issues with .NET, Visual Studio, and SQL.  This thread and your many others indicate the whole framework is fragile.  Perhaps try somehting else.

    Monday, September 30, 2019 8:59 PM
  • User753101303 posted

    Once again the EXACT error message would allow to directly understand which error you see rather than trying to guess.

    For now I see no direct relation between a db engine, a varchar column and a https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.dropdownlist?view=netframework-4.8 control and I have a hard time to imagine which error message you are talking about...

    Edit: for now my guess is that you try to assign a string to a drop down which should show a message such as "Cannot cast string to System.Web.UI.WebControls.DropDownList". It seems you are  using VB.NET in which case you may want to use Option Strict On: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement

    Monday, September 30, 2019 9:17 PM
  • User2142845853 posted

    Once again the EXACT error message would allow to directly understand which error you see rather than trying to guess.

    For now I see no direct relation between a db engine, a varchar column and a https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.dropdownlist?view=netframework-4.8 control and I have a hard time to imagine which error message you are talking about...

    Edit: for now my guess is that you try to assign a string to a drop down which should show a message such as "Cannot cast string to System.Web.UI.WebControls.DropDownList". It seems you are  using VB.NET in which case you may want to use Option Strict On: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement

    No its c#.  There is nothing wrong with the variable assignment. When there are too many bytes used thats when the debugger flags the exception.  The table is pushing the limit for size;  I will look to get the exact error message.  I just posted what takes place, it shouldnt throw such an exception. The column is set for 6000 bytes, type is varchar and confirmed its size is 6k bytes.  But put in 5300 chars, save, exception. 4200 chars? it saves just fine.  why.  Not much to the code, auto generated Edit, if Model state is ok, db.Save();  -> exception. 

    The dropdown has run for years, standard dropdown and the contents are simple like yes/no, saving the value to a string variable in the Model. It hasnt changed at all. When the one variable went from 2000 bytes to 6000 it just seemed that there was data corruption like the conversion did not complete properly. So since this is non standard the problem may be from changing the sizes of variables after the table has been in use.  

    Id expect an internal error like data will be lost

    Tuesday, October 1, 2019 4:41 AM
  • User753101303 posted

    rogersbr

    db.Save();  -> exception


    Ok so we know it is any error that could happen on DbContext.Save and that mention a "drop down list". I still don't get which error it could be.

    Please understand that when trying to get help about solving an error it makes sense to include right away:
    - an accurate enough description such as "I have a null reference exception" or even just the exact error message such as:
    'Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.DropDownList" etc...
    - the line of code on which it happens

    so that we can move right away at trying to solve this error rather than having first to guess  to guess which error you see. Knowing that it worked until now or that something was done before or that it works on another machine etc... is basically irrelevant and could or could be not related. 

    In short you should always start from the error message that happens...

    Edit: for example if you have a SqL Exception with a message such as "string or binary data would be truncated", we know right away youc column was indeed not wide enough and you could perhaps add a length control to report that to the user if it happens again that someone enters too much text.

    etc.. with a bit of experience the error message tells you about the problem and this is from where we should start rather than trying to guess….

    Tuesday, October 1, 2019 6:58 AM
  • User475983607 posted

    No its c#.  There is nothing wrong with the variable assignment. When there are too many bytes used thats when the debugger flags the exception.  The table is pushing the limit for size;  I will look to get the exact error message.  I just posted what takes place, it shouldnt throw such an exception. The column is set for 6000 bytes, type is varchar and confirmed its size is 6k bytes.  But put in 5300 chars, save, exception. 4200 chars? it saves just fine.  why.  Not much to the code, auto generated Edit, if Model state is ok, db.Save();  -> exception. 

    The dropdown has run for years, standard dropdown and the contents are simple like yes/no, saving the value to a string variable in the Model. It hasnt changed at all. When the one variable went from 2000 bytes to 6000 it just seemed that there was data corruption like the conversion did not complete properly. So since this is non standard the problem may be from changing the sizes of variables after the table has been in use.  

    Id expect an internal error like data will be lost

    Your posts are all very similar, you make a change that causes issues in unrelated code or APIs.  Then you come up with an obscure opinion of what's causing the error.  Usually you blame .NET or SQL or Identity or Visual Studio.  However, when you share the code and the actual error message, 100% of time it is bug in your code or a hole in your understanding.  Please share your code, steps to reproduce the error, and expected results.

    Tuesday, October 1, 2019 11:30 AM
  • User2142845853 posted

    I told you what the error is and when it happens, lets stick to the details or dont reply to me if you just want to argue. When too many chars are saved in the newly expanded string variable that was resized?  Here is the error message.  OK?  Are you ready?    Im going to show you the error now:

    The ViewData item that has the key 'NameASTRtype' is of type 'System.String' but must be of type 'IEnumerable<SelectListItem>'.

    Ok that was the error!

    Now you want to compare your career to mine? Or talk about the issue? You dont have to reply you know, if you dont understand this technology and say its impossible there is no such error? then find someone else to annoy.   I posted what happened, I told you when, where and why it happened and instead of behave like a professional, you feel the need to talk trash or start an argument? As I said PatriceSc, just stick to the technology or find someone else to annoy. Do you understand?

    Only one person took the time to write a professional answer and it wasnt you.

    Duplicate it? No you dont want to because it takes effort and knowledge of the technology.  If its too much for you to do then just move along!  You dont know why the SQL did this but feel the need to act superior?  I just want to implement a solution thats stable.  Adding another table to handle the increased size of a variable seems like a suitable workaround, no one commented on. 

    Tuesday, October 1, 2019 5:04 PM
  • User2142845853 posted

    Hi rogersbr,

    Can you share us the data type of your current column?

    I mean what is the exact number of this 'nnn' in your current column's datatype nvarchar(nnn)?

    I have built a demo table and everything worked fine:

    create table tab1
    (
    id int identity(1,1),
    name nvarchar(max),
    )
    
    --insert into tab1 values('a')
    
    declare @i int
    declare @s nvarchar(max)
    set @i=1
    set @s=''
    while @i<=8000
    begin
    set @s+='s'
    set @i+=1
    end
    insert into tab1 values(@s)
    
    select * from tab1
    select LEN(name) from tab1 where id=1
    --select LEN(name) from tab1 where id=3
    
    ALTER TABLE tab1 ALTER COLUMN name VARCHAR (max)
    select * from tab1

    Sorry I could't reproduce the current problem, please provide more information.

    Best Regard,

    Yang Shen

    Hi Yang,

    Thanks for the answer.  It may require a table with much more data, pushing the size limits.  The one variable had a size of 2000 chars in NVARCHAR so with 2 bytes/char it took up 4000 bytes.  I changed it to VARCHAR(6000) and when saving in the 5k range (somewhere dont know the exact #) it throws an odd exception in the IDE about another control being the wrong type. It looks like the data is corrupting something else.   Since there have been no other replies related to this, the answer may be just to make another table and not risk corrupting the existing table.  If it was a brand new, empty table? that would be different.

    Tuesday, October 1, 2019 5:10 PM
  • User475983607 posted

    The ViewData item that has the key 'NameASTRtype' is of type 'System.String' but must be of type 'IEnumerable<SelectListItem>'.

    Ok that was the error!

    This is a very common error for beginning developers.  It  that means HTML dropdown helper has an empty IEnumerable<SelectListItem> input parameter.  When this happens the helper looks to the ViewData for a key, NameASTRtype in this case, which matches the dropdown Id.

    Now you want to compare your career to mine? Or talk about the issue? You dont have to reply you know, if you dont understand this technology and say its impossible there is no such error? then find someone else to annoy.   I posted what happened, I told you when, where and why it happened and instead of behave like a professional, you feel the need to talk trash or start an argument? As I said PatriceSc, just stick to the technology or find someone else to annoy. Do you understand?

    I'm not comparing careers, just the fact that when you post the actual code or error message we find the issue is in your code not SQL, Visual Studio, or .NET.

    Duplicate it? No you dont want to because it takes effort and knowledge of the technology.  If its too much for you to do then just move along!  You dont know why the SQL did this but feel the need to act superior?  I just want to implement a solution thats stable.  Adding another table to handle the increased size of a variable seems like a suitable workaround, no one commented on. 

    SQL did not cause this error.  Your code did.  The community cannot help you unless you share your code.

    Tuesday, October 1, 2019 6:17 PM
  • User-821857111 posted

    I changed it to VARCHAR(6000) and when saving in the 5k range (somewhere dont know the exact #) it throws an odd exception in the IDE about another control being the wrong type.

    Here's my guess - you are unable to save data that exceeds the limit you have put on the column, and are possibly hiding an exception that gets raised. Your application continues to execute, but it is now in an unknown state. It is trying to render a view for which there should be data to populate a dropdown, but the data doesn't exist because of the previous exception. 

    My recommendation is that you add a breakpoint to the db.Save line, and step through the code (F10) to see what your application is doing.

    FWIW, once you get to '000s of chars in your string fields, you might as well use varchar(max) rather than applying an arbitrary limit and hoping that you (or your users) don't break it.

    Wednesday, October 2, 2019 11:03 AM
  • User2142845853 posted

    rogersbr

    I changed it to VARCHAR(6000) and when saving in the 5k range (somewhere dont know the exact #) it throws an odd exception in the IDE about another control being the wrong type.

    Here's my guess - you are unable to save data that exceeds the limit you have put on the column, and are possibly hiding an exception that gets raised. Your application continues to execute, but it is now in an unknown state. It is trying to render a view for which there should be data to populate a dropdown, but the data doesn't exist because of the previous exception. 

    My recommendation is that you add a breakpoint to the db.Save line, and step through the code (F10) to see what your application is doing.

    FWIW, once you get to '000s of chars in your string fields, you might as well use varchar(max) rather than applying an arbitrary limit and hoping that you (or your users) don't break it.

    Will try that, it had consistently hit the breakpoint on that item but will do the step from db.Save.  

    Thought that saving data was important, in this case users will only rarely 1 in 30?  1 in 50? hit the max # does that have any bearing on using a size vs.  just varchar(MAX)?

    Wonder if changing this type will affect the error anyway.

    Thursday, October 3, 2019 11:53 PM
  • User-821857111 posted

    There are only two reasons for specifying a size on a varchar field: first, because you want to be able to apply an index to the column (you can't on varchar(max)); and second, because your business rules require you to constrain the length of the input. The 6000 limit you have applied seems arbitrary and not based on any business need. So it seems pointless, and potentially troublesome, especially if you foresee users exceeding it through normal use of your system. 

    Wonder if changing this type will affect the error anyway.
    There's one way to find out. 

    Friday, October 4, 2019 6:35 AM
  • User753101303 posted

    The error you talked earlier seems not directly related to a size problem and I still don't get how a SelectListItem would be related to this kind of long text field (SQL Server shows a "string or binary data would be truncated error" if you try to save too much text to a column).For this point you usually define the size you want and if "max" is the last option you can use that (you can still limit the size on the app side).

    For your previous message the ViewBag being a "dictionary" it is easy to store a value whose type doesn't match what you expect later. I prefer to use strongly typed models.

    As  It told the line of code that shows this error could help. For now it seems you are trying to generate a drop down using the dropdownlist helper and it fails because depending on some circumstances you trying to build he option list from a single string rather than from a list of drop down options.

    For example if this list is returned as an object and that an exception is turned into a string message or whatever, you would have this kind of problem. If you show where you have that it should be easy to show the difference between a line of code that works and one that reproduces the same issue.
    You have to look at the variable that doesn't have the expected type and see how it is populated to see what it is wrongly populated.

    Edit: or maybe if the list is empty you are using another helper with a wrong overload? Seeing the line of code to get which overload you are using could possibly help to show you how someone could have the same error than you.

    Friday, October 4, 2019 8:45 AM
  • User2142845853 posted

    There are only two reasons for specifying a size on a varchar field: first, because you want to be able to apply an index to the column (you can't on varchar(max)); and second, because your business rules require you to constrain the length of the input. The 6000 limit you have applied seems arbitrary and not based on any business need. So it seems pointless, and potentially troublesome, especially if you foresee users exceeding it through normal use of your system. 

    I was given the requirement to store about 1000 words.  Since on average there are 5-6 chars per word, 6 x 1000 = 6000 w/space, but up to VARCHAR(MAX) it goes.

    In the model there is a size constraint with a custom error message, but its not even getting there. 

    Friday, October 4, 2019 3:09 PM
  • User2142845853 posted

    Ok stepping thru it seems that if the ModelState is not valid it just returns, but uses the model passed in from the HTTP Post Edit method, so it will return what it got

    {
                if (ModelState.IsValid)  
                {
                    db.Entry(AstrReport).State = EntityState.Modified;
                    db.SaveChanges();
                    TempData["Message"] = "Edited " + AstrReport.ReportNumber;
                    return RedirectToAction("Edit");
                }
                return View(AstrReport);
            }

    Model doesnt work right, never shows any error message

    [StringLength(7001, ErrorMessage = "Maximum length is 7001 Characters")]
    [Display(Name = "Text Info - Detailed")]
    public string txtDetailedExplanation { get; set; }

    Its now set to VARCHAR(MAX)

    This error only happens when the StringLength(size) field is exceeded.  What I expect is that if the length is 7002 chars it fills the applicable error message so the user can change the value.  All it does is throw the exception at the dropdown list.  So if I set the StringLength(7000) and put 7000 chars its fine.  7001 = exception.  Set model = (7001) and save 7001 its fine.  save 7002 and throws the exception.

    Changed to [StringLength(7001)]   with no custom error message.  Same.

    Tuesday, October 8, 2019 4:38 PM
  • User753101303 posted

    And you are using ValidationXXX methods in your view to render placeholders to show an error summary or a field level validation message ? 

    See https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/adding-validation#validation-error-ui-in-aspnet-mvc and scroll down to the 2nd code sample...

    Tuesday, October 8, 2019 5:11 PM
  • User475983607 posted

    rogersbr

    This error only happens when the StringLength(size) field is exceeded.  What I expect is that if the length is 7002 chars it fills the applicable error message so the user can change the value.  All it does is throw the exception at the dropdown list.

    I do not see code that fills the dropdown options.  Did you forget to populate the ViewData or the model property that hold the list Items?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 8, 2019 5:23 PM
  • User2142845853 posted

    There are only two reasons for specifying a size on a varchar field: first, because you want to be able to apply an index to the column (you can't on varchar(max)); and second, because your business rules require you to constrain the length of the input. The 6000 limit you have applied seems arbitrary and not based on any business need. So it seems pointless, and potentially troublesome, especially if you foresee users exceeding it through normal use of your system. 

    rogersbr

    Wonder if changing this type will affect the error anyway.

    There's one way to find out. 

    (am back on this problem now)

    No, changing the type to VARCHAR(MAX) had no effect.  The problem is related to the maximum size setting. if it exceeds that value? The error is thrown.  If the var is set to 7000 chars and you enter 7000 it saves. Enter 7001 and it throws the error  Set the size to 7001 and max size is 7001, then it accepts 7001 chars just fine.  Enter 7002 chars and it throws the error, it will not display the error message. Setting the breakpoint in the Browser, doesnt stop on a <div>

    Thursday, October 24, 2019 2:29 AM
  • User-821857111 posted

    See this code here:

    {
                if (ModelState.IsValid)  
                {
                    db.Entry(AstrReport).State = EntityState.Modified;
                    db.SaveChanges();
                    TempData["Message"] = "Edited " + AstrReport.ReportNumber;
                    return RedirectToAction("Edit");
                }
                return View(AstrReport);
            }

    It tends to confirm what I suggested 3 weeks ago:


    Here's my guess - you are unable to save data that exceeds the limit you have put on the column, and are possibly hiding an exception that gets raised. Your application continues to execute, but it is now in an unknown state. It is trying to render a view for which there should be data to populate a dropdown, but the data doesn't exist because of the previous exception. 

    At the moment, when the ModelState is invalid, you simply return the View. I suspect that the view relies on one or more DropdownLists. You don't appear to be repopulating the dropdownlist(s) when the ModelState is invalid. so the page is trying to render, but there is no data for the lists. That's why you get the error The ViewData item that has the key 'NameASTRtype' is of type 'System.String' but must be of type 'IEnumerable<SelectListItem>'. I would expect to see code like this:

    if (ModelState.IsValid)  
    {
        db.Entry(AstrReport).State = EntityState.Modified;
        db.SaveChanges();
        TempData["Message"] = "Edited " + AstrReport.ReportNumber;
        return RedirectToAction("Edit");
    }
    // repopulate dropdownlists etc
    ViewData["NameASTRtype"] = <some selectlist items> return View(AstrReport);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 24, 2019 6:37 AM
  • User753101303 posted

    IMO the problem is that you keep focusing on the change you are doing and try then to guess how it could cause the error you see. which possibly multiple steps in between.

    Instead you should always start from the actual error and the line on code on which it happens to see the exact situation that causes the error. Then you can go back up to the root cause.

    For now the problem is using a string or empty value when a list of SelectListItem is expected. You should start from the error and see why your code doesn't populate any more correctly this value and along the way you should understand why your db change is triggering this behavior.

    Also with the error, the line of code on which it happens or the call stack is always interesting.

    Thursday, October 24, 2019 7:48 AM
  • User2142845853 posted

    See this code here:

    rogersbr

    {
                if (ModelState.IsValid)  
                {
                    db.Entry(AstrReport).State = EntityState.Modified;
                    db.SaveChanges();
                    TempData["Message"] = "Edited " + AstrReport.ReportNumber;
                    return RedirectToAction("Edit");
                }
                return View(AstrReport);
            }

    It tends to confirm what I suggested 3 weeks ago:

    Mikesdotnetting


    Here's my guess - you are unable to save data that exceeds the limit you have put on the column, and are possibly hiding an exception that gets raised. Your application continues to execute, but it is now in an unknown state. It is trying to render a view for which there should be data to populate a dropdown, but the data doesn't exist because of the previous exception. 

    At the moment, when the ModelState is invalid, you simply return the View. I suspect that the view relies on one or more DropdownLists. You don't appear to be repopulating the dropdownlist(s) when the ModelState is invalid. so the page is trying to render, but there is no data for the lists. That's why you get the error The ViewData item that has the key 'NameASTRtype' is of type 'System.String' but must be of type 'IEnumerable<SelectListItem>'. I would expect to see code like this:

    if (ModelState.IsValid)  
    {
        db.Entry(AstrReport).State = EntityState.Modified;
        db.SaveChanges();
        TempData["Message"] = "Edited " + AstrReport.ReportNumber;
        return RedirectToAction("Edit");
    }
    // repopulate dropdownlists etc
    ViewData["NameASTRtype"] = <some selectlist items> return View(AstrReport);

    Ok this will be added. I thought this dropdown list type/content was regenerated by the controller when asking for the View? You say it is not?  I thought it did, and because of a ModelState.IsValid = false or whatever internal state where a Model's type was violated it would enable the Error Message text in the HTML when rendered, no?

    Other items/controls  ON THIS SAME PAGE using the exact same code will show the Error Message if clicking SAVE? There is no dropdown list error FOR THOSE ERRORS.  instead of control23 that shows the red text error, this is control24 that throws an exception.  So if some textbox value is required and the user clicks SAVE it will not submit, but will show the red text of the error message. Fix the value, click SAVE and it saves.  There was never any dropdown list type error.  So just another control and this one errors out.  Altho what you describe is logical. Id love to fully understand how these helper features work at the internal code level but the clock pushes to just apply the helper and move along.  I dont know what its doing internally, just know that a size constraint was put on a control, and this control will not show the red text on a page where others will.

    Thursday, October 24, 2019 2:30 PM
  • User2142845853 posted

    IMO the problem is that you keep focusing on the change you are doing and try then to guess how it could cause the error you see. which possibly multiple steps in between.

    Instead you should always start from the actual error and the line on code on which it happens to see the exact situation that causes the error. Then you can go back up to the root cause.

    For now the problem is using a string or empty value when a list of SelectListItem is expected. You should start from the error and see why your code doesn't populate any more correctly this value and along the way you should understand why your db change is triggering this behavior.

    Also with the error, the line of code on which it happens or the call stack is always interesting.

    I replied to Mikes response, why does one control show the red text then save the form if corrected, but this other control causes an exception.  if redundantly generating the contents of an unrelated dropdownlist is required, I will wire that up and move along

    Thursday, October 24, 2019 2:41 PM
  • User-821857111 posted

    I thought this dropdown list type/content was regenerated by the controller when asking for the View?
    No. The only thing your code does is to return a View. It doesn't prepare any data for that View. The controller is just a home for action methods. Only the code of the action method that's invoked is executed. The ModelState dictionary is completely different to data that populates dropdown lists. It is part of the MVC framework and is automatically populated and returned as part of the view. 

    I can't help with the rest of your questions. Without seeing the relevant code, they seem quite confusing.  I think, with all respect, that is because you are confused about how the MVC framework actually works. Most good books from Wiley brands (e.g. Wrox) or Apress will explain the actual inner workings of the framework, which should help clarify things a lot for you.

    Thursday, October 24, 2019 2:42 PM
  • User-821857111 posted

    if redundantly generating the contents of an unrelated dropdownlist is required, I will wire that up and move along

    It's not redundant! If you want the user to be able to correct their validation errors and resubmit the form, you need your dropdownlists re-populated. You have to add code to do that.

    Thursday, October 24, 2019 2:45 PM
  • User2142845853 posted

    rogersbr

    if redundantly generating the contents of an unrelated dropdownlist is required, I will wire that up and move along


    It's not redundant! If you want the user to be able to correct their validation errors and resubmit the form, you need your dropdownlists re-populated. You have to add code to do that.

    You have this page, textbox1 parameters are violated, red error text for the user to correct and it functions as expected.

    then this other new control a textarea, parameters are violated, throws an exception, no red error text because extra code has to be added to the unrelated dropdowns?   why does one control work with the red error text but another control doesnt?

    If this is the case then every page with a dropdown everywhere in every MVC application will need this extra code added in, yet Ive never seen this mentioned before in any Microsoft text/example/code, in the past 100 years 

    Thursday, October 24, 2019 3:22 PM
  • User475983607 posted

    rogersbr

    You have this page, textbox1 parameters are violated, red error text for the user to correct and it functions as expected.

    then this other new control a textarea, parameters are violated, throws an exception, no red error text because extra code has to be added to the unrelated dropdowns?   why does one control work with the red error text but another control doesnt?

    Share code that reproduces this behavior if you would like a community code review.

    rogersbr

    If this is the case then every page with a dropdown everywhere in every MVC application will need this extra code added in, yet Ive never seen this mentioned before in any Microsoft text/example/code, in the past 100 years 

    Correct MVC does not persist select option like Web Forms and ViewState.  It is up to you to manage the select options state across HTTP requests.  This has been a fundamental concept in MVC since the beginning of the framework.

    Thursday, October 24, 2019 3:44 PM
  • User-821857111 posted

    If this is the case then every page with a dropdown everywhere in every MVC application will need this extra code added in
    It will only be the case if you want to re-present the View in the event that ModelState is invalid, and that view relies on data from somewhere. It's how MVC has always worked.

    Have you added the code? Does it resolve the issue?

    Thursday, October 24, 2019 3:57 PM
  • User2142845853 posted

    rogersbr

    If this is the case then every page with a dropdown everywhere in every MVC application will need this extra code added in

    It will only be the case if you want to re-present the View in the event that ModelState is invalid, and that view relies on data from somewhere. It's how MVC has always worked.

    Have you added the code? Does it resolve the issue?

    Thank you Mike, it works.  So any data from 'somewhere else' given to the View has to be re-generated.  It makes sense. 

    Friday, October 25, 2019 5:15 PM
  • User2142845853 posted

    rogersbr

    You have this page, textbox1 parameters are violated, red error text for the user to correct and it functions as expected.

    then this other new control a textarea, parameters are violated, throws an exception, no red error text because extra code has to be added to the unrelated dropdowns?   why does one control work with the red error text but another control doesnt?

    Share code that reproduces this behavior if you would like a community code review.

    rogersbr

    If this is the case then every page with a dropdown everywhere in every MVC application will need this extra code added in, yet Ive never seen this mentioned before in any Microsoft text/example/code, in the past 100 years 

    Correct MVC does not persist select option like Web Forms and ViewState.  It is up to you to manage the select options state across HTTP requests.  This has been a fundamental concept in MVC since the beginning of the framework.

    It makes sense that external data given to the View has to be refreshed.  It was easy to apply this, problem solved.

    Friday, October 25, 2019 5:22 PM