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.Comma ndText="UPDATE tblFoo SET Field1=@Data1, Field2=@Data2 ...
etc... Field19=@Data19 , ImageField=@Ima geData WHERE RowID=12" (e.g.)

myCommand.Param eters.AddWithVa lue("@Data1", "teststring 1 teststring1
teststring1 teststring1")
myCommand.Param eters.AddWithVa lue("@Data2", "teststring 2 teststring2
teststring2 teststring2")
..
..
..
myCommand.Param eters.AddWithVa lue("@Data19", "teststring 19 teststring19
teststring19 teststring19")
myCommand.Param eters.AddWithVa lue("@ImageData ", "This is my 5600 character
long string containing image data")

myCommand.Execu teNonQuery

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.Comma ndText="UPDATE tblFoo SET Field1=@Data1, Field2=@Data2 ...
etc... Field19=@Data19 WHERE RowID=12"

myCommand.Param eters.AddWithVa lue("@Data1", "teststring 1 teststring1
teststring1 teststring1")
myCommand.Param eters.AddWithVa lue("@Data2", "teststring 2 teststring2
teststring2 teststring2")
..
..
..
myCommand.Param eters.AddWithVa lue("@Data19", "teststring 19 teststring19
teststring19 teststring19")

myCommand.Execu teNonQuery

myCommand.Param eters.Clear

myCommand.Comma ndText="UPDATE tblFoo SET ImageField=@Ima geData WHERE
RowID=12"

myCommand.Param eters.AddWithVa lue("@ImageData ", "This is my 5600 character
long string containing image data")

myCommand.Execu teNonQuery



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]]

Re: Strange Access DB Updat Behavior :-(

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