Jump to content
Xtreme .Net Talk

Update command not working with NULL DateTime Valies

pendragon

By pendragon
in Database / XML / Reporting

 Share

Recommended Posts

pendragon Newbie

pendragon

Posted
Posted

Well now I am back at work have managed to find out why my update command doesn't work.

 

private void TestADO_Load(object sender, System.EventArgs e)
{
ds = new DataSet();

SQL = "SELECT SOPOrdNo, SOPAccID, SOPCustRef, SOPEntry, SOPProcess, SOPDate, SOPDelDate, SOPDueDate FROM SOPHeader ORDER BY SOPOrdNo";
cn = new OleDbConnection(dataaccess.SLConString);
cn.Open();

Application.DoEvents();

da = new OleDbDataAdapter(SQL, cn);
da.TableMappings.Add("Table", "SOPHeader");

tbl = ds.Tables.Add("SOPHeader");
col = tbl.Columns.Add("SOPOrdNo", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;

col = tbl.Columns.Add("SOPAccID", typeof(string));
col.AllowDBNull = false;
col.MaxLength = 16;

col = tbl.Columns.Add("SOPCustRef", typeof(string));
col = tbl.Columns.Add("SOPEntry", typeof(string));
col = tbl.Columns.Add("SOPProcess", typeof(string));
col = tbl.Columns.Add("SOPDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDelDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDueDate", typeof(DateTime));
col.AllowDBNull = true;

tbl.PrimaryKey = new DataColumn[] {tbl.Columns["SOPOrdNo"]};

dataaccess.tblSOPHeader = ds.Tables["SOPHeader"];

dataaccess.colSOPOrdNo = dataaccess.tblSOPHeader.Columns["SOPOrdNo"];
dataaccess.colSOPAccID = dataaccess.tblSOPHeader.Columns["SOPAccID"];
dataaccess.colSOPCustRef = dataaccess.tblSOPHeader.Columns["SOPCustRef"];
dataaccess.colSOPEntry = dataaccess.tblSOPHeader.Columns["SOPEntry"];
dataaccess.colSOPProcess = dataaccess.tblSOPHeader.Columns["SOPProcess"];
dataaccess.colSOPDate = dataaccess.tblSOPHeader.Columns["SOPDate"];
dataaccess.colSOPDelDate = dataaccess.tblSOPHeader.Columns["SOPDelDate"];
dataaccess.colSOPDueDate = dataaccess.tblSOPHeader.Columns["SOPDueDate"];

da.Fill(ds, "SOPHeader");
}
[/Code]

 
[code]
string sql;

cn = new OleDbConnection(dataaccess.SLConString);
SQL = "SELECT * FROM SOPHeader ORDER BY SOPOrdNo";
da = new OleDbDataAdapter(SQL, cn);
cn.Open();

OleDbCommand cmd;

sql = "UPDATE [sOPHeader]";
sql += " SET SOPAccID = ?, SOPCustRef = ?, SOPEntry = ?, SOPProcess = ?, SOPDate = ?, SOPDelDate = ?, SOPDueDate = ?";
sql += " WHERE SOPOrdNo = ? AND SOPAccID = ? AND SOPCustRef = ? AND SOPEntry = ? AND SOPProcess = ? AND SOPDate = ? AND SOPDelDate = ? AND SOPDueDate = ?";

cmd = new OleDbCommand(sql, cn); //sql, cn);

OleDbParameterCollection pc = cmd.Parameters;

pc.Add("SOPAccID_new", OleDbType.Char, 0, "SOPAccID");
pc.Add("SOPCustRef_new", OleDbType.Char, 0, "SOPCustRef");
pc.Add("SOPEntry_new", OleDbType.Char, 0, "SOPEntry");
pc.Add("SOPProcess_new", OleDbType.Char, 0, "SOPProcess");
pc.Add("SOPDate_new", OleDbType.Date, 0, "SOPDate");
pc.Add("SOPDelDate_new", OleDbType.Date, 0, "SOPDelDate");
pc.Add("SOPDueDate_new", OleDbType.Date, 0, "SOPDueDate");

OleDbParameter param;
		
param = pc.Add("SOPOrdNo_Orig", OleDbType.Char, 0, "SOPOrdNo");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPAccID_new", OleDbType.Char, 0, "SOPAccID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPCustRef_new", OleDbType.Char, 0, "SOPCustRef");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPEntry_new", OleDbType.Char, 0, "SOPEntry");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPProcess_new", OleDbType.Char, 0, "SOPProcess");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDate_new", OleDbType.Date, 0, "SOPDate");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDelDate_new", OleDbType.Date, 0, "SOPDelDate");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDueDate_new", OleDbType.Date, 0, "SOPDueDate");
param.SourceVersion = DataRowVersion.Original;

this.SalesOrders[dataaccess.colSOPEntry] = "Data";
this.SalesOrders[dataaccess.colSOPProcess] = "Adapter";
this.SalesOrders[dataaccess.colSOPDueDate] = DateTime.Parse("31/08/2004");

da.UpdateCommand = cmd;

try 
{
	da.Update(ds, "SOPHeader");
}
catch (Exception ex) 
{
	MessageBox.Show(ex.ToString());
}

 

The problem is with SOPDueDate, I am importing a CSV file from the old system and this one did not have a Due Date.

 

The import program was written using VB.Net and Old style ADO, the due date is set to DBNull.Value and then saved.

 

Everything is fine in ADO.NET untill I try to save a record with a date that is null.

 

How do I get around this.

 

Thanks

laredo512 Newbie

laredo512

Posted
Posted
Well now I am back at work have managed to find out why my update command doesn't work.

 

private void TestADO_Load(object sender, System.EventArgs e)
{
ds = new DataSet();

SQL = "SELECT SOPOrdNo, SOPAccID, SOPCustRef, SOPEntry, SOPProcess, SOPDate, SOPDelDate, SOPDueDate FROM SOPHeader ORDER BY SOPOrdNo";
cn = new OleDbConnection(dataaccess.SLConString);
cn.Open();

Application.DoEvents();

da = new OleDbDataAdapter(SQL, cn);
da.TableMappings.Add("Table", "SOPHeader");

tbl = ds.Tables.Add("SOPHeader");
col = tbl.Columns.Add("SOPOrdNo", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;

col = tbl.Columns.Add("SOPAccID", typeof(string));
col.AllowDBNull = false;
col.MaxLength = 16;

col = tbl.Columns.Add("SOPCustRef", typeof(string));
col = tbl.Columns.Add("SOPEntry", typeof(string));
col = tbl.Columns.Add("SOPProcess", typeof(string));
col = tbl.Columns.Add("SOPDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDelDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDueDate", typeof(DateTime));
col.AllowDBNull = true;

tbl.PrimaryKey = new DataColumn[] {tbl.Columns["SOPOrdNo"]};

dataaccess.tblSOPHeader = ds.Tables["SOPHeader"];

dataaccess.colSOPOrdNo = dataaccess.tblSOPHeader.Columns["SOPOrdNo"];
dataaccess.colSOPAccID = dataaccess.tblSOPHeader.Columns["SOPAccID"];
dataaccess.colSOPCustRef = dataaccess.tblSOPHeader.Columns["SOPCustRef"];
dataaccess.colSOPEntry = dataaccess.tblSOPHeader.Columns["SOPEntry"];
dataaccess.colSOPProcess = dataaccess.tblSOPHeader.Columns["SOPProcess"];
dataaccess.colSOPDate = dataaccess.tblSOPHeader.Columns["SOPDate"];
dataaccess.colSOPDelDate = dataaccess.tblSOPHeader.Columns["SOPDelDate"];
dataaccess.colSOPDueDate = dataaccess.tblSOPHeader.Columns["SOPDueDate"];

da.Fill(ds, "SOPHeader");
}
[/Code]

 
[code]
string sql;

cn = new OleDbConnection(dataaccess.SLConString);
SQL = "SELECT * FROM SOPHeader ORDER BY SOPOrdNo";
da = new OleDbDataAdapter(SQL, cn);
cn.Open();

OleDbCommand cmd;

sql = "UPDATE [sOPHeader]";
sql += " SET SOPAccID = ?, SOPCustRef = ?, SOPEntry = ?, SOPProcess = ?, SOPDate = ?, SOPDelDate = ?, SOPDueDate = ?";
sql += " WHERE SOPOrdNo = ? AND SOPAccID = ? AND SOPCustRef = ? AND SOPEntry = ? AND SOPProcess = ? AND SOPDate = ? AND SOPDelDate = ? AND SOPDueDate = ?";

cmd = new OleDbCommand(sql, cn); //sql, cn);

OleDbParameterCollection pc = cmd.Parameters;

pc.Add("SOPAccID_new", OleDbType.Char, 0, "SOPAccID");
pc.Add("SOPCustRef_new", OleDbType.Char, 0, "SOPCustRef");
pc.Add("SOPEntry_new", OleDbType.Char, 0, "SOPEntry");
pc.Add("SOPProcess_new", OleDbType.Char, 0, "SOPProcess");
pc.Add("SOPDate_new", OleDbType.Date, 0, "SOPDate");
pc.Add("SOPDelDate_new", OleDbType.Date, 0, "SOPDelDate");
pc.Add("SOPDueDate_new", OleDbType.Date, 0, "SOPDueDate");

OleDbParameter param;

param = pc.Add("SOPOrdNo_Orig", OleDbType.Char, 0, "SOPOrdNo");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPAccID_new", OleDbType.Char, 0, "SOPAccID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPCustRef_new", OleDbType.Char, 0, "SOPCustRef");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPEntry_new", OleDbType.Char, 0, "SOPEntry");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPProcess_new", OleDbType.Char, 0, "SOPProcess");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDate_new", OleDbType.Date, 0, "SOPDate");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDelDate_new", OleDbType.Date, 0, "SOPDelDate");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("SOPDueDate_new", OleDbType.Date, 0, "SOPDueDate");
param.SourceVersion = DataRowVersion.Original;

this.SalesOrders[dataaccess.colSOPEntry] = "Data";
this.SalesOrders[dataaccess.colSOPProcess] = "Adapter";
this.SalesOrders[dataaccess.colSOPDueDate] = DateTime.Parse("31/08/2004");

da.UpdateCommand = cmd;

try 
{
	da.Update(ds, "SOPHeader");
}
catch (Exception ex) 
{
	MessageBox.Show(ex.ToString());
}

 

The problem is with SOPDueDate, I am importing a CSV file from the old system and this one did not have a Due Date.

 

The import program was written using VB.Net and Old style ADO, the due date is set to DBNull.Value and then saved.

 

Everything is fine in ADO.NET untill I try to save a record with a date that is null.

 

How do I get around this.

 

Thanks

 

Check this thread : http://www.xtremedotnettalk.com/showthread.php?t=86088

Laredo512

* using VS.NET Pro 2003 *

pendragon Newbie

pendragon

Posted
  • Author
Posted

Thanks for the reply laredo512

 

I think there is something somewhere that I don't understand, inserting a new record with a null date is working ok, what I can't get to work is when I try to Update a record that started with a null date I get the message 'the update command affected 0 records' :confused:

pendragon Newbie

pendragon

Posted
  • Author
Posted

I have now got a work around for this.

 

The problem lies in the WHERE clause of the SQL statement, for some reason if I have SOPDueDate = ? then, when it is checking the original values, if it is DBNull it fails the comparison and never finds a record to update.

 

As I have a procedure to make the update command from a data table that it receives as a parameter and all my table primary keys are the first fields on the table, I think I will change the WHERE clause to just be 'WHERE [tablePrimary] = ?', as there is only one record per key this will get over the problem, whether this is good programming practice I have no idea.

 

If anyone out there has a clue as to why the comparison check is failing on the DBNull check then please let me know as I would like to know if I am doing something wrong.

 

Thank you

laredo512 Newbie

laredo512

Posted
Posted (edited)
I have now got a work around for this.

 

The problem lies in the WHERE clause of the SQL statement, for some reason if I have SOPDueDate = ? then, when it is checking the original values, if it is DBNull it fails the comparison and never finds a record to update.

 

As I have a procedure to make the update command from a data table that it receives as a parameter and all my table primary keys are the first fields on the table, I think I will change the WHERE clause to just be 'WHERE [tablePrimary] = ?', as there is only one record per key this will get over the problem, whether this is good programming practice I have no idea.

 

If anyone out there has a clue as to why the comparison check is failing on the DBNull check then please let me know as I would like to know if I am doing something wrong.

 

Thank you

try:

 

Update tblPrimary SET(fldOne, fldTwo .... ) Values( <<< use parameters >>>) WHERE fldDateYouAreLookingFfor IS NULL

 

also try this for your SOPDueDate --> convert.todatetime("31/08/2004")

Edited by laredo512

Laredo512

* using VS.NET Pro 2003 *

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...