Strange Access DB Updat Behavior :-(

[Johnny Jörgensen]

Somebody please explain this to me:

I've got an Access database with a table containing some 20 fields. A few
are boolean and integer, but most of them are string(50). And then I've got
one field that is a PM (Memo) field. That one is supposed to contain image
data in string format and the length of my "testimage" string was around
5600 characters.

To update the table I had an OleDbCommand:

myCommand.CommandText="UPDATE tblFoo SET Field1=@Data1, Field2=@Data2 ...
etc... Field19=@Data19, ImageField=@ImageData WHERE RowID=12" (e.g.)

myCommand.Parameters.AddWithValue("@Data1", "teststring1 teststring1
teststring1 teststring1")
myCommand.Parameters.AddWithValue("@Data2", "teststring2 teststring2
teststring2 teststring2")
..
..
..
myCommand.Parameters.AddWithValue("@Data19", "teststring19 teststring19
teststring19 teststring19")
myCommand.Parameters.AddWithValue("@ImageData", "This is my 5600 character
long string containing image data")

myCommand.ExecuteNonQuery

This should work. I've tested it by JUST updating the image string, and
there's no problem. But when I add all the other parameters, suddenly the
ImageData field doesn't contain "This is my 5600 character long string
containing image data" but "0" or "1".

I don't get any error message, just a weird behavior. If I split it up into:

myCommand.CommandText="UPDATE tblFoo SET Field1=@Data1, Field2=@Data2 ...
etc... Field19=@Data19 WHERE RowID=12"

myCommand.Parameters.AddWithValue("@Data1", "teststring1 teststring1
teststring1 teststring1")
myCommand.Parameters.AddWithValue("@Data2", "teststring2 teststring2
teststring2 teststring2")
..
..
..
myCommand.Parameters.AddWithValue("@Data19", "teststring19 teststring19
teststring19 teststring19")

myCommand.ExecuteNonQuery

myCommand.Parameters.Clear

myCommand.CommandText="UPDATE tblFoo SET ImageField=@ImageData WHERE
RowID=12"

myCommand.Parameters.AddWithValue("@ImageData", "This is my 5600 character
long string containing image data")

myCommand.ExecuteNonQuery

THEN everything works! Can anybody explain this to me? No error message...

Is there a maximum size for the OleDbCommand Param collection and if there
is, why don't I get an error message?

I've wasted two days on this now, and would really like to know why.

Bewildered,
Johnny

[Cor Ligthert[MVP]]

Johnny,
Be aware that parameters in OleDB have names, however those do nothing,
therefore you see often an ? in meant for OleDB SQL scripts.

You have to add the parameters in the same sequence as they appear in the
SQL script.

Cor