By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

TIMESTAMP instead of new/old row

P: n/a
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;
}
}
Nov 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Robert,

First off, I notice a problem with your code. When you get a
connection, you do this:

SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here

// Try/catch block
try
{
}
catch
{
mcs3Connection.Close();
}

Basically, you have the call to close in one place, and that is a bad
thing. If anything, I would declare the variable mcs3Connection outside of
a using block, and then initialize the variable in the using block.

Now, as far as using timestamps is concerned, I don't see the use of it
in what is not working. For SQL Server, you can only have one timestamp
column, and you should be checking that in your update statement.
Basically, when selected from the table, the SQL Provider will convert that
to an array of bytes. If you parameterize your query, like you are doing
here, you should be able to send that array of bytes right back through the
command.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Robert Schuldenfrei" <bo*@s-i-inc.com> wrote in message
news:tLIZc.89792$9d6.70017@attbi_s54...
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;
}
}

Nov 16 '05 #2

P: n/a
Hi Nick,

Thanks for the quick response. Good eyes for spotting my failure to close
the connection. In the code below I add that line. Are you suggesting that
I move the try statement such that it covers every statement from the Open()
on down? I tried that, as the code below indicates. Since the transaction
is in the try block it is local to that block. The compiler complains that
the Rollback in the catch block can not find updateTrans.

As far as the Timestamp goes, I have but one column in the SQL table,
loc_alter. The catch block hauls in the thrown SQL exception and returns
8178 message noted below. I do not really know how to deal with errors from
the disconnected database because I can not use debug on them. Do you think
my UPDATE statement looks reasonable?

I would really like to get this working as I will have some large text
blocks attached to rows like this one and my now operational old/new row
checking will break down.

Thank you for your help,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
bo*@s-i-inc.com
781/329-4828
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OY**************@TK2MSFTNGP09.phx.gbl...
Robert,

First off, I notice a problem with your code. When you get a
connection, you do this:

SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here

// Try/catch block
try
{
}
catch
{
mcs3Connection.Close();
}

Basically, you have the call to close in one place, and that is a bad
thing. If anything, I would declare the variable mcs3Connection outside of a using block, and then initialize the variable in the using block.

Now, as far as using timestamps is concerned, I don't see the use of it in what is not working. For SQL Server, you can only have one timestamp
column, and you should be checking that in your update statement.
Basically, when selected from the table, the SQL Provider will convert that to an array of bytes. If you parameterize your query, like you are doing
here, you should be able to send that array of bytes right back through the command.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Robert Schuldenfrei" <bo*@s-i-inc.com> wrote in message
news:tLIZc.89792$9d6.70017@attbi_s54...
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();
try
{
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);
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.
mcs3Connection.Close();
return false;
}
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}

Nov 16 '05 #3

P: n/a
Hi Robert,

I agree with Nick that you have to use byte array in C# when mapping from
SQL server field types. If the type is incorrect, ADO.NET might faild to
add the parameter to the statement and thus caused the error which says
parameter not supplied.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #4

P: n/a
Hi Kevin, Nick, and the NG readers,

My lack of any real understanding of the building of SQL commands is really
on display here. I THOUGHT I knew what was going on in the code exerpt
shown below under the topic "This is what IS working." Clearly, I am
clueless about how Parameters are passed to the updateCommand, the UPDATE
statement, and then on to SQL. Please do not laugh, but where do you set up
the byte array in C# to do the mapping?

1/ Do I even need the line: public string Loc_alter; in my class ItemLoc?

2/ I am sure that my Parameter.Add is wrong here:
updateCommand.Parameters.Add("@Old_loc_alter", oldItemLoc.Loc_alter); But I
am unclear as to how the right information gets passed to the updateCommand.
Add seems to allow overloading and I do not know the structure of these
parameters. What is SQL expecting?

3/ At the time the UPDATE statement is completely formed what does it look
like?

4/ Else where in my code I have an INSERT and a DELETE. I do not think that
the INSERT needs to be changed. DELETE is still using the old/new row
technique and is therefore still working.

I have once again reproduced my buggy code below. Can you correct what I am
doing wrong and suggest a way to use Nick's implementation of a byte array.
Thanks in advance.

Your very humble (and with good cause) student,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
bo*@s-i-inc.com
781/329-4828
"Kevin Yu [MSFT]" <v-****@online.microsoft.com> wrote in message
news:8g**************@cpmsftngxa10.phx.gbl...
Hi Robert,

I agree with Nick that you have to use byte array in C# when mapping from
SQL server field types. If the type is incorrect, ADO.NET might faild to
add the parameter to the statement and thus caused the error which says
parameter not supplied.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

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.
mcs3Connection.Close();
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.
mcs3Connection.Close();
return false;
}
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}
Nov 16 '05 #5

P: n/a
Hi Robert,

1. Based on the code you have provided, if Loc_alter is the timestamp
field, we have to change the type to byte array like the following:

public class ItemLoc
{
public string Loc_PartNo;
public string Loc_location;
public decimal Loc_quantity;
public string Loc_prime;
public Byte[] Loc_alter; //TimeStamp field is mapped to byte array.
}

2. SqlParameterCollection.Add method has a lot of overloads. The overload
you're using now is

updateCommand.Parameters.Add("@Old_loc_alter", oldItemLoc.Loc_alter);

The second argument is the value you're actually passing to SQL Server.
However, the type of the parameter is not very clear here. We can use the
following to specify types.

updateCommand.Parameters.Add("@Old_loc_alter", SqlDbType.Timestamp, 8);
updateCommand.Parameters["@Old_loc_alter"].Value = oldItemLoc.Loc_alter;

3. Here is a link which contains a typical update command should look like.

http://msdn.microsoft.com/library/de...us/cpref/html/
frlrfsystemdataidbdataadapterclassupdatecommandtop ic.asp

4. Since Insert doesn't have concurrency issues, we needn't do old/new
check on it. Whether Delete command needs concurrency checking, depends on
the logic of your application. If you would like to delete the record
regardless of the changes it has made, we don't need old/new checking. If
we cannot delete it if others have made changes to it, I think we have to
add such checkings.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #6

P: n/a
Hi Kevin and NG,

As usual, Kevin had this issue dead on (with some input from Nick). An
interesting side issue was that the error message:

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.

Was trying to tell me that oldItemLoc.Loc_alter had no value when I tried to
add it into the parameter collection. When you read the SQL row, you need
to assign the byte[] array to Loc_alter. Then it can be added and the code
works as designed.

As far as point 4 in Kevin's solution goes, the logic of the code will
prevent users from deleting locations so I have no concurrency issues with
DELETE in this table. However, other tables will have DELETE concurrency
problems so I have added the timestamp processing to ItemLoc.

Thank you Kevin for your help.

Sincerely,

Bob

--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
bo*@s-i-inc.com
781/329-4828

"Kevin Yu [MSFT]" <v-****@online.microsoft.com> wrote in message
news:Ps**************@cpmsftngxa10.phx.gbl...
Hi Robert,

1. Based on the code you have provided, if Loc_alter is the timestamp
field, we have to change the type to byte array like the following:

public class ItemLoc
{
public string Loc_PartNo;
public string Loc_location;
public decimal Loc_quantity;
public string Loc_prime;
public Byte[] Loc_alter; //TimeStamp field is mapped to byte array.
}

2. SqlParameterCollection.Add method has a lot of overloads. The overload
you're using now is

updateCommand.Parameters.Add("@Old_loc_alter", oldItemLoc.Loc_alter);

The second argument is the value you're actually passing to SQL Server.
However, the type of the parameter is not very clear here. We can use the
following to specify types.

updateCommand.Parameters.Add("@Old_loc_alter", SqlDbType.Timestamp, 8);
updateCommand.Parameters["@Old_loc_alter"].Value = oldItemLoc.Loc_alter;

3. Here is a link which contains a typical update command should look like.
http://msdn.microsoft.com/library/de...us/cpref/html/ frlrfsystemdataidbdataadapterclassupdatecommandtop ic.asp

4. Since Insert doesn't have concurrency issues, we needn't do old/new
check on it. Whether Delete command needs concurrency checking, depends on
the logic of your application. If you would like to delete the record
regardless of the changes it has made, we don't need old/new checking. If
we cannot delete it if others have made changes to it, I think we have to
add such checkings.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.