473,327 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Query parameters doing something odd

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
4 1660
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
Wavey
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
Stewart Ross
2,545 Expert Mod 2GB
Moved to C# from Access for expert attention...

-Stewart
Feb 20 '09 #5

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

Similar topics

8
by: Steven Stewart | last post by:
Hi there, I have a query (QueryB) that is based on the results of another query (QueryA). When I open QueryB itself, it displays the exact records that I want. I have a report based on...
2
by: deko | last post by:
How do I limit the results of this query to return only the NextAppt and the corresponding Subject? SELECT , MIN() As NextAppt FROM tblAppointments WHERE...
15
by: Jean | last post by:
Hello, I have the following query that I set up as a test, and it runs fine: SELECT STATUSHISTORIE.* FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =...
2
by: khan | last post by:
SELECT Products.Manufacturere, Products.ProductDescription, Products.SerialNumber, Products.QtyOnHand, Products.Location FROM Products WHERE (((Products.Manufacturere)=!!) AND...
11
by: anony | last post by:
Hello, I can't figure out why my parameterized query from an ASP.NET page is dropping "special" characters such as accented quotes & apostrophes, the registered trademark symbol, etc. These...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
1
by: Mayhem05 | last post by:
I have an Access 2003 database that I need to write some VBA code for to populate a table. The table is based on a query I have built in Access queries. Right now I have 2 parameters that are...
29
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.