Object cannot be cast from DBNull to other types

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

Answer 1

User-1942430961 posted

  if (_reader["USER_ID" != System.DBNull.Value)
                        UserDetail_UserID =Convert.ToInt32(_reader["USER_ID"]);

write all like this.

Answer 2

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.

Answer 3

User269602965 posted

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

Answer 4

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;

}

Answer 5

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. 

Answer 6

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.

Answer 7

User269602965 posted

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

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

 

 

Answer 8

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.