asked on

Update BLOB field on SQL Server

I am trying to update a row in SQL Server 7. I login fine and have datareader / writer permissions on the database, but I get a permissions error. Here's the code and error:

With rs
.LockEdits = False
.Edit
ReDim bytCur(0 To LOF(nFile) - 1) As Byte
Get #nFile, 1, bytCur

!ObjectName = dlgUpdate.txtName.Text

!ObjectVersion = dlgUpdate.mskVersion.Text

!ObjectType = dlgUpdate.txtType.Text

!ObjectDate = dlgUpdate.Label1.Caption

!ObjectFile.AppendChunk bytCur

.Update
.Close
End With

This fails on Update. The error I get is:

ODBC — update on a linked table <table> (or '???') failed. (Error 3157 (or 3155))

and the reasons help gives (none of which are applicable) are:

Using an ODBC connection, you tried to update data in an ODBC database; that update couldn't be completed.
Possible causes:

·      The update would have caused a rule violation.
·      The ODBC database is read-only, or you don't have permission to update data in that database. Resolve the read-only condition, or see your system administrator or the person who created the database to obtain the necessary permissions.
·      The ODBC database is on a network drive and the network isn't connected. Make sure the network is available, and then try the operation again.

The table is empty so no rules are violated, I administer the database so the permissions are fine and it is on my local machine so there should be no network problems. That takes care of Microsoft's help files, How about yours?

-Pete

Jon_Raymond

It depends on the rs object. Is rs just a table, a query string, or a call to a stored procedure? If it's a table, the problem could be that you need a dynamic recordset connection:

Public Sub AppendChunkX()
Dim cn As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As Variant
Const conChunkSize = 100
' Open a connection.
Set cn = New ADODB.Connection
strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"
cn.Provider = "sqloledb"
cn.Open strCn
'Open the pub_info_x table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenDynamic
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info_x", cn, , , adCmdTable
'Prompt for a logo to copy.
strMsg = "Available logos are : " & vbCr & vbCr
Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info,
InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
rstPubInfo.MoveNext
Loop
strMsg = strMsg & "Enter the ID of a logo to copy:"
strPubID = InputBox(strMsg)
' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop
' Get data from the user.
strPubID = Trim(InputBox("Enter a new pub ID:"))
strPRInfo = Trim(InputBox("Enter descriptive text:"))
' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfo
lngOffset = 0 ' Reset offset.
Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - _
lngOffset),conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
rstPubInfo.Update
' Show the newly added data.
MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
"Description: " & rstPubInfo!pr_info & vbCr & _
"Logo size: " & rstPubInfo!logo.ActualSize
rstPubInfo.Close
cn.Close
End Sub

Otherwise, if rs is a query there may be a problem linking to a text field. Text fields cannot always be used in queries as freely as other datatypes. There are restrictions.

peteodonnell

ASKER

Thanks for your help, but I was trying to use DAO. When I made the switch (as the code above has) to ADO, I had no problems. Do you have an idea why DAO would have such trouble accessing SQL server, while ADO doesn't? Just curious, but the original problem is solved.

ASKER CERTIFIED SOLUTION

Jon_Raymond

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peteodonnell

ASKER

..maybe I'll try that. Thanks.