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

Query parameters doing something odd

P: 4
Hi All,

I have a C# test app that writes to an access database with the following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. DataRow editRow1 = ds.Tables["TraderNames"].Rows.Find("1");
  3. DataRow editRow2 = ds.Tables["TraderNames"].Rows.Find("2");
  4. editRow2["TraderName"] = "Bob";
  5. editRow1["TraderName"] = "AAAAAAA";
  6.  
  7. update.Parameters.Clear();
  8. update.Parameters.Add("@TName", OleDbType.Char, 20, "TraderName");
  9. update.Parameters.Add("@TNum", OleDbType.Char, 20, "TraderNum");
  10. update.Parameters.Add("@ID", OleDbType.Char, 20, "TraderID");
  11.  
  12. int numRows = adapter.Update(ds.Tables["TraderNames"]);
  13.  
The Update Query I use is
Expand|Select|Wrap|Line Numbers
  1. UPDATE Traders SET TraderName = [@TName], TraderNum = [@TNum]
  2. WHERE TraderID=[@ID];

Before I do the update the database holds this information

Expand|Select|Wrap|Line Numbers
  1. TraderID    TraderName    TraderNum
  2. 1    David    20
  3. 2    Hamish    11
  4.  
And after I do the update I see this in the Database
Expand|Select|Wrap|Line Numbers
  1. TraderID    TraderName    TraderNum
  2. 1    AAAAAAA    20
  3. 2    BobAAAA    11
  4.  
Where I would expect it to hold
Expand|Select|Wrap|Line Numbers
  1. TraderID    TraderName    TraderNum
  2. 1    AAAAAAA    20
  3. 2    Bob    11
  4.  
As I can see that the data held in the C# DataSet is what I think it should be I assume that it's something to do with access parameters not cleaning up properly in between updates.

Anyone seen anything like this before?

Thanks
Feb 19 '09 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Although I'm not a C# person myself, I suspect that your parameters are being declared as fixed-length strings. @TName is shown as a Char type of length 20, not a VarChar. If I'm right about this you are then carrying over part of the previous settings of your parameter from one row to the next. Bear in mind that although you set editrow2 before editrow1 the evidence suggests that editrow1 is updated first, and therefore @TName is being passed the longer value first.

If you had more than two rows being updated you'd probably see this more clearly. With a fixed-length string parameter not cleared between rows consider the following values:

Expand|Select|Wrap|Line Numbers
  1. AAAAAAAA
  2. Bob
  3. Bobby
  4. Ted
As the fixed-length parameter would be set to each in turn the names would result in:

Expand|Select|Wrap|Line Numbers
  1. AAAAAAAA
  2. BobAAAAA
  3. BobbyAAA
  4. TedbyAAA
I could be wrong, but I am sure that the update itself is not the problem - it's the fixed-length definition of the parameter.

-Stewart
Feb 19 '09 #2

NeoPa
Expert Mod 15k+
P: 31,494
I'm with Stewart on this one (without any c# specific experience I'm almost positive).

If you'd like though, we can move the thread to the c# forum for you. This is certainly not an Access issue (although I appreciate it appeared that way to you when you posted).
Feb 20 '09 #3

P: 4
Hi,

Thanks for the suggestion, however it didn't fix it when I changed my variable to a VarChar. i still get the same problem.
Moving this to the C# forum is fine with me.
Feb 20 '09 #4

Expert Mod 2.5K+
P: 2,545
Moved to C# from Access for expert attention...

-Stewart
Feb 20 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.