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_MILEAGE DataSet
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):

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.To String(); 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:

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]

Thanks Dan..

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