c# / DataSet: Setting DataRowView value to DBNull

shlabadoo

Hello,

I searched for quite a while for an answer to this, but with no joy. All the answeres seem to be how to deal with getting a DBNull value out of a database, not to put it in . Perhaps I am not using the right search terms, but here we go.

Background:
I have a database with 2 tables with the following pertinent fields:

EMPLOYEE
- EMPLOYEE_ID (pk) (string)
VEHICLE
- VEHICLE_ID (pk) (int)
- EMPLOYEE_ID (fk) (string)

These are loaded up in to a typed data set called vEHICLE_MILEAGEDataSet
Inside of the VEHICLE table, the foreign key, EMPLOYEE_ID is nullable.

My problem is this: I want to be able to un-assign an employee from a vehicle, and to do that, I would set EMPLOYEE_ID to DBNull in the VEHICLE table getting the currently selected vehicle.

Here is my code to do this (with the unimportant bits taken out):

Expand|Select|Wrap|Line Numbers

  
VEHICLE_MILEAGEDataSet.VEHICLERow vehRow = (employeeVehicleBindingSource.Current as DataRowView).Row as VEHICLE_MILEAGEDataSet.VEHICLERow;
 
if (vehRow != null)

{

//Place a Null in the EmployeeID column for the current vehicle

vehRow.EMPLOYEE_ID = DBNull.Value;

}
 
//commit the changes

this.Validate(); 

this.employeeVehicleBindingSource.EndEdit();

this.vEHICLETableAdapter.Update(this.vEHICLE_MILEAGEDataSet.VEHICLE);
 
//refresh the data

this.vEHICLETableAdapter.Fill(this.vEHICLE_MILEAGEDataSet.VEHICLE);

So, obviously the major problem is the attempt to set a string equal to DBNull. I get the error "Cannot convert type 'System.DBNull' to 'string' "

I tried using DBNull.Value.ToString(); but I think that is sending an empty string, which wouldn't match any of the entries in the EMPLOYEE table, and give an constraints exception.

So how do I do this? It seems like it should be pretty simple, but it has me stumped.

Thanks,

-dan

shlabadoo

Ok, I am not finding much to do it the way I was originally intending, but perhaps I can do this another way.

Instead of worrying about putting a Null into the database at the DataSet level, perhaps I could modify the TableAdapter's Update to check for an empty string during the update and put the null there.

I know how to set up a Command to do this, but how would I connect this to the Table adapter? Where does this code need to be? Or do I need to do this in pure SQL?

Here is the proposed command:

Expand|Select|Wrap|Line Numbers

 public static int UpdateVehicle(int vehicleID, string employeeID)

        {

            string sql = @"

                UPDATE VEHICLE

                SET

                    EMPLOYEE_ID = @employeeID

                WHERE

                    VEHICLE_ID = @vehicleID

            ";

            SqlConnection conn = ConnectionManager.GetConnectionObject();

            conn.Open();

            using (SqlCommand command = new SqlCommand(sql, conn))

            {

                command.CommandType = CommandType.Text;

                command.Parameters.Add("@vehicleID", SqlDbType.Int).Value = vehicleID;
 
                //Check to see if the employeeID is empty, if it is, use a NULL instead

                if (employeeID == String.Empty)

                {

                    command.Parameters.Add("@employeeID", SqlDbType.VarChar).Value = DBNull.Value;

                }

                else

                {

                    command.Parameters.Add("@employeeID", SqlDbType.VarChar).Value = employeeID;
 
                }

                return (command.ExecuteNonQuery());

            }

Thanks,

-dan

alokmishra2003

Its working..
DBNull.Value did the trick for me..

Plater

I think its because you are addressing the column in a "string" context, instead of an object context as it is normally done. I didn't recognize your dataAdapter style of code

c# / DataSet: Setting DataRowView value to DBNull

re: c# / DataSet: Setting DataRowView value to DBNull

re: c# / DataSet: Setting DataRowView value to DBNull

re: c# / DataSet: Setting DataRowView value to DBNull

Similar .NET Framework bytes

/bytes/development

/bytes/it

/bytes/about