locked
Object cannot be cast from DBNull to other types RRS feed

  • Question

  • User1312098465 posted

    Hi All,

    Am here once again. Thanks for the supports so far.

    I have an issue. I tried insertng a new record into an Oracle Database using C#. I first retrived the Record (Record ID = 1, User ID = "FB-2011-0001) from the Database and change the the Record Status (Status = "N") of the Retrived record in the Database.

    Secondly, I performed modification on the Retrived Record read through my OracleDataReader and Insert it as a new Record(Record ID = 2, User ID ="FB-2011-0001) into the same Database setting the Record Status ="Y". The objective here is to keep history of the activity perform on each record as a record is uniquely identified by Record ID while the Record is related to User with User ID.

    Find Below my Code

    if

    (_reader.HasRows)

    {

    while

    (_reader.Read())

    {

    // Set the parameters.

    UserDetail_UserID =

    Convert.ToInt32(_reader["USER_ID"

    ]);

    UserDetail_LogonName =

    Convert.ToString(_reader["USER_LOGON_NAME"

    ]);

    UserDetail_EmailAddress =

    Convert.ToString(_reader["EMAIL_ADDRESS"

    ]);

    UserDetail_RecordStatus =

    Convert.ToString(_reader["RECORD_STATUS"

    ]);

    UserDetail_RecordID =

    Convert.ToInt32(_reader["RECORD_ID"

    ]);

    UserDetail_ModifiedBy =

    Convert.ToString(_reader["MODIFIED_BY"

    ]);

    if (_reader["DATE_MODIFIED"] != DBNull

    .Value)

    {

    UserDetail_DateModified =

    Convert.ToDateTime(_reader["DATE_MODIFIED"

    ]);

    }

    else

    {

    UserDetail_DateModified =

    Convert.ToDateTime(null

    );

    }

    }

    }

    I pass the parameters to a method that perform database insertion

    The Command executes successfull. However, it inserts 1/1/0001 in the Database for the "DATE_MODIFIED" Date COLUMN.

    I dont have any idea of what to do and i need you guys assistance. I want the field to remain null and not helping me out with 1/1/0001. The column on the table is a Date column.

    Thanks

    Wednesday, September 21, 2011 4:44 AM

Answers

  • User1312098465 posted

    Problem Solved!

    DateTime? UserTable_DateCreated;

    I made it nullable and it went fine without changing anything again.

    Thanks to you guys for your contributions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2011 4:43 AM

All replies

  • User-1942430961 posted
      if (_reader["USER_ID" != System.DBNull.Value)
                            UserDetail_UserID =Convert.ToInt32(_reader["USER_ID"]);

    write all like this.
    Wednesday, September 21, 2011 6:08 AM
  • User1312098465 posted

    I dont think this will solve my problem. The problem i have is with the Date Column in the database giving me 1/1/0001 when null data is inserted into the column.

    I appreciate the fact that your recommendation makes my application DBNull conscious.

    Still need help here.

    Wednesday, September 21, 2011 7:41 AM
  • User269602965 posted

    show you SQL code and ODP.NET code doing the insertion

    Wednesday, September 21, 2011 12:45 PM
  • User1312098465 posted

    Thanks Lannie. I hope you understand the detal below.

    SQL code (Via DataSet)

    BackEnd

    INSERT INTO "USERS_DETAIL" ("USER_ID", "USER_LOGON_NAME", "EMAIL_ADDRESS", "CREATED_DATE", "CREATED_BY", "STATUS", "USER_ID_NUMBER", "USER_FIRST_NAME", "USER_LAST_NAME",  "APPROVED_BY", "APPROVED_DATE", "RECORD_STATUS", "MODIFIED_BY", "DATE_MODIFIED", "RECORD_ID") VALUES (:USER_ID, :USER_LOGON_NAME, :EMAIL_ADDRESS, :CREATED_DATE, :CREATED_BY, :STATUS, :USER_ID_NUMBER, :USER_FIRST_NAME, :USER_LAST_NAME,:APPROVED_BY, :APPROVED_DATE, :RECORD_STATUS, :MODIFIED_BY, :DATE_MODIFIED, :RECORD_ID)

    Page Code Behind

    public int DeleteSelectedUser(int UserTableRecordID, int UserID, string Token, int UserDetailCode, int UserTableCode, string

    ModifiedBy)

    {

    // 1. Get the Record ID for the UserDetail Table using the User ID

    int

    UserDetailRecordID = MyUsersDetail.GetUsersRecordIDByUsersID(UserID);

    // 2. Connect to the Database

    //string strConn = UserDetailConnectionString.Connection();

    string oradb = ";Data Source=orcl;Persist Security Info=True;User ID=gis; Password=gis; Unicode=True"

    ; ;

    conn =

    new OracleConnection

    (oradb);

    conn.Open();

     

    // 3. Fetch Record to Delete

    // (a) User Table

    UTSQL =

    string.Format("Select * from USERTABLE WHERE RECORD_ID ='{0}'"

    , UserTableRecordID);

    cmd =

    new OracleCommand

    (UTSQL, conn);

    OracleDataReader

    _UTreader = cmd.ExecuteReader();

    if

    (_UTreader.HasRows)

    {

    while

    (_UTreader.Read())

    {

    // Set the parameters.

    UserTable_UserID =

    Convert.ToInt32(_UTreader["USER_ID"

    ]);

    UserTable_LogonName =

    Convert.ToString(_UTreader["USER_LOGON_NAME"

    ]);

    UserTable_UserPassword =

    Convert.ToString(_UTreader["USER_PASSWORD"

    ]);

    UserTable_LockUserFlag =

    Convert.ToString(_UTreader["LOCK_USER_FLAG"

    ]);

    UserTable_DateCreated =

    Convert.ToDateTime(_UTreader["CREATED_DATE"

    ]);

    UserTable_CreatedBy =

    Convert.ToString(_UTreader["CREATED_BY"

    ]);

    UserTable_PasswordResetRequestFlag =

    Convert.ToString(_UTreader["PASS_RESET_REQ_FLAG"

    ]);

    UserTable_Status =

    Convert.ToString(_UTreader["STATUS"

    ]);

    UserTable_EffectiveDate =

    Convert.ToDateTime(_UTreader["EFECTIVE_DATE"

    ]);

    UserTable_ExpiryDate =

    Convert.ToDateTime(_UTreader["EXPIRY_DATE"

    ]);

    UserTable_ApprovedBy =

    Convert.ToString(_UTreader["APPROVED_BY"

    ]);

    if (_UTreader["DATE_APPROVED"] != DBNull

    .Value)

    {

    UserTable_DateApproved =

    Convert.ToDateTime(_UTreader["DATE_APPROVED"

    ]);

    }

    else

    {

    UserTable_DateApproved =

    DateTime

    .MinValue;

    }

    UserTable_RecordStatus =

    Convert.ToString(_UTreader["RECORD_STATUS"

    ]);

    UserTable_RecordID =

    Convert.ToInt32(_UTreader["RECORD_ID"

    ]);

    UserTable_ModifiedBy =

    Convert.ToString(_UTreader["MODIFIED_BY"

    ]);

    if (_UTreader["DATE_MODIFIED"] != DBNull

    .Value)

    {

    UserTable_DateModified =

    Convert.ToDateTime(_UTreader["DATE_MODIFIED"

    ]);

    }

    else

    {

    UserTable_DateModified =

    DateTime

    .MinValue;

    }

    }

    }

    // (b) User Detail Table

    UDSQL =

    string.Format("Select * from USERS_DETAIL WHERE RECORD_ID ='{0}'"

    , UserDetailRecordID);

    cmd =

    new OracleCommand

    (UDSQL, conn);

    OracleDataReader

    _reader = cmd.ExecuteReader();

    if

    (_reader.HasRows)

    {

    while

    (_reader.Read())

    {

    // Set the parameters.

    UserDetail_UserID =

    Convert.ToInt32(_reader["USER_ID"

    ]);

    UserDetail_LogonName =

    Convert.ToString(_reader["USER_LOGON_NAME"

    ]);

    UserDetail_EmailAddress =

    Convert.ToString(_reader["EMAIL_ADDRESS"

    ]);

    UserDetail_DateCreated =

    Convert.ToDateTime(_reader["CREATED_DATE"

    ]);

    UserDetail_CreatedBy =

    Convert.ToString(_reader["CREATED_BY"

    ]);

    UserDetail_Status =

    Convert.ToString(_reader["STATUS"

    ]);

    UserDetail_UserIDNumber =

    Convert.ToString(_reader["USER_ID_NUMBER"

    ]);

    UserDetail_UserFirstName =

    Convert.ToString(_reader["USER_FIRST_NAME"

    ]);

    UserDetail_UserLastName =

    Convert.ToString(_reader["USER_LAST_NAME"

    ]);

    UserDetail_ApprovedBy =

    Convert.ToString(_reader["APPROVED_BY"

    ]);

    if (_reader["APPROVED_DATE"] != DBNull

    .Value)

    {

    UserDetail_DateApproved =

    Convert.ToDateTime(_reader["APPROVED_DATE"

    ]);

    }

    else

    {

    UserDetail_DateApproved =

    Convert.ToDateTime(null

    );

    }

    UserDetail_RecordStatus =

    Convert.ToString(_reader["RECORD_STATUS"

    ]);

    UserDetail_RecordID =

    Convert.ToInt32(_reader["RECORD_ID"

    ]);

    UserDetail_ModifiedBy =

    Convert.ToString(_reader["MODIFIED_BY"

    ]);

    if (_reader["DATE_MODIFIED"] != DBNull

    .Value)

    {

    UserDetail_DateModified =

    Convert.ToDateTime(_reader["DATE_MODIFIED"

    ]);

    }

    else

    {

    UserDetail_DateModified =

    DateTime

    .MinValue;

    }

    }

    }

    conn.Dispose();

    // Call the Delete Method of the UserDetail helper

    string Result = DataAccessLayer.UserDetailHelper

    .DeleteUserByRecordID(UserTableRecordID, UserDetailRecordID, Token, UserDetailCode, UserTableCode, UserDetailRecordID, UserDetail_UserID,

    UserDetail_LogonName, UserDetail_EmailAddress, UserDetail_DateCreated, UserDetail_CreatedBy, UserDetail_Status,

    UserDetail_UserIDNumber, UserDetail_UserFirstName, UserDetail_UserLastName, UserDetail_ApprovedBy, UserDetail_DateApproved,

    UserDetail_RecordStatus, UserDetail_RecordID, UserDetail_ModifiedBy, UserDetail_DateModified, UserTable_UserID, UserTable_LogonName,

    UserTable_UserPassword, UserTable_LockUserFlag, UserTable_DateCreated, UserTable_CreatedBy, UserTable_PasswordResetRequestFlag,

    UserTable_Status, UserTable_EffectiveDate, UserTable_ExpiryDate, UserTable_ApprovedBy, UserTable_DateApproved,

    UserTable_RecordStatus, UserTable_RecordID, UserTable_ModifiedBy, UserTable_DateModified);

    return

    UserDetail_UserID;

    }

    The Helper that Performs the Insertion.

    public static string DeleteUserByRecordID(int UserTableRecordID, int UserDetailTableRecordID, string Token, int UserDetailCode, int UserTableCode, int UserDetailRecordID, int

    UserDetail_UserID,

    string UserDetail_LogonName,string UserDetail_EmailAddress, DateTime UserDetail_DateCreated, string UserDetail_CreatedBy,string

    UserDetail_Status,

    string UserDetail_UserIDNumber,string UserDetail_UserFirstName,string UserDetail_UserLastName,string UserDetail_ApprovedBy,DateTime

    UserDetail_DateApproved,

    string UserDetail_RecordStatus,int UserDetail_RecordID,string UserDetail_ModifiedBy,DateTime UserDetail_DateModified,int UserTable_UserID, string

    UserTable_LogonName,

    string UserTable_UserPassword,string UserTable_LockUserFlag,DateTime UserTable_DateCreated,string UserTable_CreatedBy,string

    UserTable_PasswordResetRequestFlag,

    string UserTable_Status,DateTime UserTable_EffectiveDate,DateTime UserTable_ExpiryDate,string UserTable_ApprovedBy,DateTime

    UserTable_DateApproved,

    string UserTable_RecordStatus,int UserTable_RecordID,string UserTable_ModifiedBy,DateTime

    UserTable_DateModified)

    {

    string MyUserDetailResult = "0"

    ;

    INITIALISE_CODETableAdapter NewCodeDetail = new INITIALISE_CODETableAdapter

    ();

    USERS_DETAILTableAdapter NewUserDetail = new USERS_DETAILTableAdapter

    ();

    USERTABLETableAdapter NewUserTable = new USERTABLETableAdapter

    ();

    //Open Connection

    NewUserDetail.Connection.Open();

    NewCodeDetail.Connection = NewUserDetail.Connection;

    NewUserTable.Connection = NewUserDetail.Connection;

    //Start Transaction

    OracleTransaction

    MyNewTransaction = NewUserDetail.Connection.BeginTransaction();

    //Assign the Transaction to Table Adapter Command objects.

    NewUserDetail.AttachTransaction(MyNewTransaction);

    NewCodeDetail.AttachTransaction(MyNewTransaction);

    NewUserTable.AttachTransaction(MyNewTransaction);

    //Process Updates

    try

    {

    string

    Status, ChangeRecordStatus, NewRecordStatus;

    // Get New Record ID for Record in UserTable Table

    int CurrentUserTableUserIDSequenceNumber = Convert

    .ToInt32(NewCodeDetail.SelectInitialisedCode(UserTableCode));

    int NewUserTable_RecordID = CurrentUserTableUserIDSequenceNumber + 1;

    // User Table Record ID

    // Get New Record ID for |Record in User Detail Table

    int CurrentUserDetailIDSequenceNumber = Convert

    .ToInt32(NewCodeDetail.SelectInitialisedCode(UserDetailCode));

    int

    NewUserDetailTable_RecordID = CurrentUserDetailIDSequenceNumber + 1;

    // Update the current Record in both Tables

    switch

    (Token)

    {

    case "C"

    :

    {

    Status =

    "K"

    ;

    }

    break

    ;

    default

    :

    {

    Status =

    "D"

    ;

    }

    break

    ;

    }

    // Get the Current System Date

    DateTime DateModified = Convert

    .ToDateTime(NewUserDetail.GetTheCurrentDate());

    ChangeRecordStatus =

    "N"

    ;

    NewUserDetail.UpdateRecord(ChangeRecordStatus, UserDetailTableRecordID);

    NewUserTable.UpdateRecord(ChangeRecordStatus, UserTableRecordID);

    //Insert New Record modified.

    NewRecordStatus =

    "Y"

    ;

    UserDetail_Status = Status;

    UserTable_Status = Status;

    NewUserDetail.InsertModifiedRecord(UserDetail_UserID, UserDetail_LogonName, UserDetail_EmailAddress, UserDetail_DateCreated,UserDetail_CreatedBy,

    UserDetail_Status, UserDetail_UserIDNumber,UserDetail_UserFirstName,UserDetail_UserLastName, UserDetail_ApprovedBy,UserDetail_DateApproved,

    UserDetail_RecordStatus, UserDetail_ModifiedBy, DateModified, NewUserDetailTable_RecordID);

    NewUserTable.InsertModifiedRecord(UserTable_UserID, UserTable_LogonName, UserTable_UserPassword,UserTable_LockUserFlag,UserTable_DateCreated,UserTable_CreatedBy,

    UserTable_PasswordResetRequestFlag, UserTable_Status, UserTable_EffectiveDate, UserTable_ExpiryDate, UserTable_RecordStatus, UserTable_DateApproved, UserTable_ApprovedBy,

    UserTable_ModifiedBy, DateModified, NewUserTable_RecordID);

    // Update the UserTable and UserDetail Table Record ID

    NewCodeDetail.UpdateCode(NewUserTable_RecordID, UserTableCode);

    //Update Record ID sequence number for User detail Table

    NewCodeDetail.UpdateCode(NewUserDetailTable_RecordID, UserDetailCode);

    MyNewTransaction.Commit();

    }

    catch (DataException

    e)

    {

    //rollback transactions

    MyNewTransaction.Rollback();

    throw

    (e);

    }

    finally

    {

    NewCodeDetail.Dispose();

    MyNewTransaction.Dispose();

    NewUserDetail.Dispose();

    NewUserTable.Dispose();

    }

    return

    MyUserDetailResult;

    }

    Thursday, September 22, 2011 9:06 AM
  • User269602965 posted

    UserTable_DateModified =Convert.ToDateTime(_UTreader["DATE_MODIFIED"]);

    }

    else

    {

    UserTable_DateModified =DateTime.MinValue;

    }

     

    Put a breakpoint on your two lines, start DEBUG, and see which part of your if statement is generating the bad value. 

    Thursday, September 22, 2011 10:19 AM
  • User1312098465 posted
    Both gives the wrong value. I noticed that the wrong value comes if the Date Column in the database has no value. If a valid date is read it doesn't show this. I already default the column value to Null at the backend.
    Thursday, September 22, 2011 3:16 PM
  • User269602965 posted

    if (_reader["DATE_MODIFIED"] != DBNull.Value)
    {

    Try managing your NULL test with   .IsNullOrEmpty instead of DBNULL.Value

     

     

    Thursday, September 22, 2011 7:36 PM
  • User1312098465 posted

    Problem Solved!

    DateTime? UserTable_DateCreated;

    I made it nullable and it went fine without changing anything again.

    Thanks to you guys for your contributions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2011 4:43 AM