Query parameters doing something odd

[Wavey]

Hi All,

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

 
DataRow editRow1 = ds.Tables["TraderNames"].Rows.Find("1");
DataRow editRow2 = ds.Tables["TraderNames"].Rows.Find("2");
editRow2["TraderName"] = "Bob";
editRow1["TraderName"] = "AAAAAAA";

update.Parameters.Clear();
update.Parameters.Add("@TName", OleDbType.Char, 20, "TraderName");
update.Parameters.Add("@TNum", OleDbType.Char, 20, "TraderNum");
update.Parameters.Add("@ID", OleDbType.Char, 20, "TraderID");

int numRows = adapter.Update(ds.Tables["TraderNames"]);

The Update Query I use is

UPDATE Traders SET TraderName = [@TName], TraderNum = [@TNum]
WHERE TraderID=[@ID];

Before I do the update the database holds this information

TraderID	TraderName	TraderNum
1	David	20
2	Hamish	11

And after I do the update I see this in the Database

TraderID	TraderName	TraderNum
1	AAAAAAA	20
2	BobAAAA	11

Where I would expect it to hold

TraderID	TraderName	TraderNum
1	AAAAAAA	20
2	Bob	11

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

[Stewart Ross]

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:

AAAAAAAA
Bob
Bobby
Ted

As the fixed-length parameter would be set to each in turn the names would result in:

AAAAAAAA
BobAAAAA
BobbyAAA
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

[NeoPa]

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).

[Wavey]

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.

[Stewart Ross]

Moved to C# from Access for expert attention...

-Stewart