Such a simple task: How to store a Byte[] in Access 2010? (Searching the web all day long about this.)
I have to use a "Attachment Field" in access 2010 because as far as i can see there is no other possible (varBinary, Image,..) field available.
I tried: (ConvertImageToByte returns a Byte[])
Cmd.CommandText = "UPDATE Clubs SET Field1 = @File WHERE Name = @Name"; OleDbParameter para = new OleDbParameter("@File", OleDbType.VarBinary); para.Value = ConvertImageToByte(Logo); Cmd.ExecuteNonQuery();
Exception: "An UPDATE or DELETE query cannot contain a multi-valued field."
I tried:
DBEngine dbe = new DBEngine(); Database db = dbe.OpenDatabase("database.accdb", false, false, ""); String Command = "SELECT * FROM Clubs"; Recordset rs = db.OpenRecordset(Command, RecordsetTypeEnum.dbOpenDynaset, 0, LockTypeEnum.dbOptimistic); rs.MoveFirst(); rs.Edit(); Recordset2 rs2 = (Recordset2)rs.Fields["Field1"].Value; rs2.AddNew(); Field2 f2 = (Field2)rs2.Fields["FileData"]; f2.LoadFromFile("file.png"); rs2._30_Update(); rs2.Close(); rs._30_Update(); rs.Close();
This works but the file is in the first row of the tabel all the time and i can′t figure out how to get the right row. If i try to add a WHERE clause to the SELECT statement ill get a " Too few parameters. Expected 2." exception.
If anyone knows a way to story my Byte[] (or a image) into the database an get it out again please let me know!
Please don′t give me links to:
http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx#BM4.6
http://www.mikesdotnetting.com/Article/123/Storing-Files-and-Images-in-Access-with-ASP.NET
http://www.sitepoint.com/forums/showthread.php?t=666928
http://www.eggheadcafe.com/software/aspnet/35103540/multivalued-fields-in-access-2007-with-c-ado.aspx
Programmatically managing Microsoft Access Attachment-typed field with .NET
Thanks for your help guys.
You can use an OLE Object field, it is the best choice for varbinary(max) match up.
Some notes:
''Reference: Microsoft ActiveX Data Object x.x Library Dim strSQL As String Dim strCN As String Dim rs As dao.Recordset Dim mstream As ADODB.Stream strSQL = "SELECT Pix FROM Table1" Set rs = CurrentDb.OpenRecordset(strSQL) Set mstream = New ADODB.Stream mstream.Type = adTypeBinary mstream.Open mstream.LoadFromFile "c:docsproject.jpg" ''FileName & FullPath rs.AddNew rs.Fields("Pix").Value = mstream.Read rs.Update rs.Close
EDIT
To copy back to disk, you can again use the Stream:
Dim strSQL As String Dim cn As New ADODB.Connection Dim mstream As New ADODB.Stream strSQL = "SELECT Pix FROM Table1" Set rs = CurrentDb.OpenRecordset(strSQL) mstream.Type = adTypeBinary mstream.Open mstream.Write rs!Pix mstream.SaveToFile "c:docspixout.jpg", adSaveCreateOverWrite
1.4m articles
1.4m replys
5 comments
57.0k users