Dear NG,
As expected, when I went to implement TIMESTAMP, I failed. With the help of
Kevin Yu,
I got the 2 code segments at the bottom working using a WHERE clause that
checks all
columns. SQL does not like my use of TIMESTAMP. First, notice that I have
used
"string" type data to hold the TIMESTAMP column in C#. Second, the Primary
Key is
composed of loc_PartNo and loc_location. I got the following exception back
from
execution:
Error number: 8178
Error message: Prepared statement'(@loc_PartNo nvarchar(4000),@loc_location
nvarchar
(4000),@loc_qu' expects parameter @Old_loc_alter, which was not supplied.
As I am unclear on the concept, the error above may not be the only issue at
hand. My
books are very light on this subject. The John Papa article used DATETIME
rather
than TIMESTAMP.
Thanks in advance for any help.
Cheers,
Bob
Robert Schuldenfrei
bo*@s-i-inc.com
This is what is NOT working
public class ItemLoc
{
public string Loc_PartNo;
public string Loc_location;
public decimal Loc_quantity;
public string Loc_prime;
public string Loc_alter; //In SQL this is TIMESTAMP
}
//Change code goes here. Get or Add instantiates object
ItemLoc oldItemLoc = new ItemLoc();
oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
oldItemLoc.Loc_location = itemLoc.Loc_location;
oldItemLoc.Loc_alter = itemLoc.Loc_alter;
LoadItemLoc(); //take the changes out of the text boxes
if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
{
MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString()
+ " Message: "
+ ItemLocTbl.excp
+ " Could not change item location record. "
+ " Record may be in use by another user.", "Datebase error");
}
public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
{
string updateStmt = "UPDATE ItemLoc SET "
+ "loc_PartNo = @loc_PartNo, "
+ "loc_location = @loc_location, "
+ "loc_quantity = @loc_quantity, "
+ "loc_prime = @loc_prime "
+ "WHERE loc_PartNo = @Old_loc_PartNo "
+ "AND loc_location = @Old_loc_location "
+ "AND loc_alter = @Old_loc_alter "
; //notice ; on line by itself
//Change to TRANSACTION form 08/30/04. Notice order of statements.
SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here
SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
trans
updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);
updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@Old_loc_location", oldItemLoc.Loc_location);
updateCommand.Parameters.Add("@Old_loc_alter", oldItemLoc.Loc_alter);
try
{
updateCommand.Transaction = updateTrans; //Necessary? Yes
if (updateCommand.ExecuteNonQuery() > 0) //1 or more rows updated
{
//We changed a row successfully
updateTrans.Commit();
mcs3Connection.Close();
return true;
}
else
{
//Update failed concurrency test.
updateTrans.Rollback();
excpNumber = 0; //My own exception number for error msg.
return false;
}
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}
This is what IS working:
//Change code goes here. Get or Add instantiates object
ItemLoc oldItemLoc = new ItemLoc(); //Concurrency check
oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
oldItemLoc.Loc_location = itemLoc.Loc_location;
oldItemLoc.Loc_quantity = itemLoc.Loc_quantity;
oldItemLoc.Loc_prime = itemLoc.Loc_prime;
LoadItemLoc(); //take the changes out of the text boxes
if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
{
MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString()
+ " Could not change item location record. "
+ " Record may be in use by another user.", "Datebase error");
}
public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
{
string updateStmt = "UPDATE ItemLoc SET "
+ "loc_PartNo = @loc_PartNo, "
+ "loc_location = @loc_location, "
+ "loc_quantity = @loc_quantity, "
+ "loc_prime = @loc_prime "
+ "WHERE loc_PartNo = @Old_loc_PartNo "
+ "AND loc_location = @Old_loc_location "
+ "AND loc_quantity = @Old_loc_quantity "
+ "AND loc_prime = @Old_loc_prime "
; //notice ; on line by itself
//Change to TRANSACTION form 08/30/04. Notice order of statements.
SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here
SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
trans
updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);
updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@Old_loc_location", oldItemLoc.Loc_location);
updateCommand.Parameters.Add("@Old_loc_quantity", oldItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@Old_loc_prime", oldItemLoc.Loc_prime);
try
{
updateCommand.Transaction = updateTrans; //Necessary? Yes
if (updateCommand.ExecuteNonQuery() > 0) //1 or more rows updated
{
//We changed a row successfully
updateTrans.Commit();
mcs3Connection.Close();
return true;
}
else
{
//Update failed concurrency test.
updateTrans.Rollback();
excpNumber = 0; //My own exception number for error msg.
return false;
}
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}
