none
Specified cast is not valid. RRS feed

  • Question

  • I'm having problems inserting data into my table using my dataadapter. Similar to many others, but a tad different. I'm using VS 2008 with C# and .NET 3.5. I've created a strongly-typed dataset that works perfect for updates, but really hates my inserts.

    My initial variables...

    PROF_REFERENCESTableAdapter ProfileRefAdaptor;
    ApplicantInfo.PROF_REFERENCESDataTable ProfileRefTable;
    
    private ApplicantInfo.PROF_REFERENCESRow ProfileRefRow
    {
      get { return (ApplicantInfo.PROF_REFERENCESRow)Session["ProfileRefRow"]; }
      set { Session["ProfileRefRow"] = value; }
    }
    

    I initiate my update datarow as follows:

    ProfileRefTable = new ApplicantInfo.PROF_REFERENCESDataTable();
    ProfileRefAdaptor = new PROF_REFERENCESTableAdapter();
    ProfileRefAdaptor.FillByID(ProfileRefTable, proRefID);
    

    and my insert datarow as follows:

    ProfileRefTable = new ApplicantInfo.PROF_REFERENCESDataTable();
    ProfileRefRow = ProfileRefTable.NewPROF_REFERENCESRow();
    

    and here is my save button code

    protected void SaveButton_Click(object sender, EventArgs e)
    {
      ApplicantAdaptor = new APPLICANTTableAdapter();
    
      ProfileRefRow.ApplicantID = (int)ApplicantAdaptor.GetIDFromUN(System.Web.HttpContext.Current.User.Identity.Name);
    
      if (String.IsNullOrEmpty(ProfRefFirstName.Text))
      {
        ProfileRefRow.SetProfRefFirstNameNull();
      }
      else
      {
        ProfileRefRow.ProfRefFirstName = ProfRefFirstName.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefLastName.Text))
      {
        ProfileRefRow.SetProfRefLastNameNull();
      }
      else
      {
        ProfileRefRow.ProfRefLastName = ProfRefLastName.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefTitle.Text))
      {
        ProfileRefRow.SetProfRefTitleNull();
      }
      else
      {
        ProfileRefRow.ProfRefTitle = ProfRefTitle.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefAddr.Text))
      {
        ProfileRefRow.SetProfRefAddrNull();
      }
      else
      {
        ProfileRefRow.ProfRefAddr = ProfRefAddr.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefCity.Text))
      {
        ProfileRefRow.SetProfRefCityNull();
      }
      else
      {
        ProfileRefRow.ProfRefCity = ProfRefCity.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefState.SelectedValue))
      {
        ProfileRefRow.SetProfRefStateNull();
      }
      else
      {
        ProfileRefRow.ProfRefState = Convert.ToInt32(ProfRefState.SelectedValue);
      }
    
      if (String.IsNullOrEmpty(ProfRefZip.Text))
      {
        ProfileRefRow.SetProfRefZipNull();
      }
      else
      {
        ProfileRefRow.ProfRefZip = ProfRefZip.Text.ToString().Trim();
      }
    
      if (String.IsNullOrEmpty(ProfRefPhone.Text))
      {
        ProfileRefRow.SetProfRefPhoneNull();
      }
      else
      {
        ProfileRefRow.ProfRefPhone = ProfRefPhone.Text.ToString().Trim();
      }
    
      ProfileRefAdaptor = new PROF_REFERENCESTableAdapter();
    
      if (EditMode)
      {
        ProfileRefAdaptor.Update(ProfileRefRow);
      }
      else
      {
        ProfileRefAdaptor.Insert(ProfileRefRow.ApplicantID, ProfileRefRow.ProfRefFirstName,
          ProfileRefRow.ProfRefLastName, ProfileRefRow.ProfRefTitle, ProfileRefRow.ProfRefAddr,
          ProfileRefRow.ProfRefCity, ProfileRefRow.ProfRefState, ProfileRefRow.ProfRefZip,
          ProfileRefRow.ProfRefPhone);
      }
    
      Response.Redirect("ProfileMain.aspx#References");
    }
    

    My updates work perfectly....I can update columns to null all day long. My inserts, however, are a different story. I figured out (after reading and reading) that I could set the string DataColumn "NullValue" attribute to "(Null)", but I am unable to do that for any other datatype.

    When I push the insert I get the following error.

    Specified cast is not valid. 
    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.InvalidCastException: Specified cast is not valid.
    
    Source Error: 
    
    
    Line 5101:        get {
    Line 5102:          try {
    Line 5103:            return ((int)(this[this.tablePROF_REFERENCES.ProfRefStateColumn]));
    Line 5104:          }
    Line 5105:          catch (global::System.InvalidCastException e) {
     
    
    Source File: C:\Visual Studio 2008\Projects\HREmploymentApp\HREmploymentApp\ApplicantInfo.Designer.cs  Line: 5103 
    
    Stack Trace: 
    
    
    [InvalidCastException: Specified cast is not valid.]
      HREmploymentApp.PROF_REFERENCESRow.get_ProfRefState() in C:\Visual Studio 2008\Projects\HREmploymentApp\HREmploymentApp\ApplicantInfo.Designer.cs:5103
    
    [StrongTypingException: The value for column 'ProfRefState' in table 'PROF_REFERENCES' is DBNull.]
      HREmploymentApp.PROF_REFERENCESRow.get_ProfRefState() in C:\Visual Studio 2008\Projects\HREmploymentApp\HREmploymentApp\ApplicantInfo.Designer.cs:5106
      HREmploymentApp.EditProfReferences.SaveButton_Click(Object sender, EventArgs e) in C:\Visual Studio 2008\Projects\HREmploymentApp\HREmploymentApp\EditProfReferences.aspx.cs:251
      System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
      System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
      System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
      System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
    
     
    
    

    Where on earth am I going wrong? Also, I'm a little confused as to why DataAdaptor.Update(DataRow) is allowed, but DataAdaptor.Insert(DataRow) is not. Am I just missing something?

    Monday, August 2, 2010 5:00 PM

Answers

  • So for anyone else out there having trouble with this. If you make any changes to the main query of the dataset, it will change ALL your DataColumn "NullValue" attributes back to "Throw Exception". I opted not to change them all back and amended my insert statement as follows:

    ProfileRefAdaptor.Insert(
     ProfileRefRow.ApplicantID, 
     ProfileRefRow.ProfRefFirstName,
     ProfileRefRow.ProfRefLastName, 
     (ProfileRefRow.IsProfRefTitleNull()) ? null : ProfileRefRow.ProfRefTitle,
     (ProfileRefRow.IsProfRefAddrNull()) ? null : ProfileRefRow.ProfRefAddr,
     (ProfileRefRow.IsProfRefCityNull()) ? null : ProfileRefRow.ProfRefCity,
     (ProfileRefRow.IsProfRefStateNull()) ? null : (int?)ProfileRefRow.ProfRefState, 
     (ProfileRefRow.IsProfRefZipNull()) ? null : ProfileRefRow.ProfRefZip,
     ProfileRefRow.ProfRefPhone);
    

    I change my queries all the time. I'm bound to change something and forget to change all those back at some point and have widespread errors to deal with. I'll just save myself the trouble and check the strings too. (All others have required field validators). I'd like to meet the person that decided to make "Throw exception" the default.... *rolls eyes*

    Good luck to anyone else dealing with this.

    • Marked as answer by Sykiemikey Tuesday, August 3, 2010 12:19 PM
    Monday, August 2, 2010 10:39 PM

All replies

  • I thought I should add that AllowDBNull is set to true AND the DefaultValue is <DBNull> for the ProfRefState DataColumn.
    Monday, August 2, 2010 5:10 PM
  • So I found a solution....tell me if it is the best solution please.

    I replace my insert statement with..

    ProfileRefAdaptor.Insert(ProfileRefRow.ApplicantID, ProfileRefRow.ProfRefFirstName,
     ProfileRefRow.ProfRefLastName, ProfileRefRow.ProfRefTitle, ProfileRefRow.ProfRefAddr,
     ProfileRefRow.ProfRefCity, (ProfileRefRow.IsProfRefStateNull())?null:(int?)ProfileRefRow.ProfRefState, 
     ProfileRefRow.ProfRefZip, ProfileRefRow.ProfRefPhone);
    

    So that it checks the variable for a null value and sends null if it is or (int?)ProfileRefRow.ProfRefState if it isn't.

    Shouldn't that all be built in like it is for the String variables? Is this a bug? If so, will it be fixed ever? I'm seeing this issue go back at least since .net 2.0.

    Monday, August 2, 2010 6:29 PM
  • So for anyone else out there having trouble with this. If you make any changes to the main query of the dataset, it will change ALL your DataColumn "NullValue" attributes back to "Throw Exception". I opted not to change them all back and amended my insert statement as follows:

    ProfileRefAdaptor.Insert(
     ProfileRefRow.ApplicantID, 
     ProfileRefRow.ProfRefFirstName,
     ProfileRefRow.ProfRefLastName, 
     (ProfileRefRow.IsProfRefTitleNull()) ? null : ProfileRefRow.ProfRefTitle,
     (ProfileRefRow.IsProfRefAddrNull()) ? null : ProfileRefRow.ProfRefAddr,
     (ProfileRefRow.IsProfRefCityNull()) ? null : ProfileRefRow.ProfRefCity,
     (ProfileRefRow.IsProfRefStateNull()) ? null : (int?)ProfileRefRow.ProfRefState, 
     (ProfileRefRow.IsProfRefZipNull()) ? null : ProfileRefRow.ProfRefZip,
     ProfileRefRow.ProfRefPhone);
    

    I change my queries all the time. I'm bound to change something and forget to change all those back at some point and have widespread errors to deal with. I'll just save myself the trouble and check the strings too. (All others have required field validators). I'd like to meet the person that decided to make "Throw exception" the default.... *rolls eyes*

    Good luck to anyone else dealing with this.

    • Marked as answer by Sykiemikey Tuesday, August 3, 2010 12:19 PM
    Monday, August 2, 2010 10:39 PM