Solved

Update BLOB field on SQL Server

Posted on 2000-03-17
4
216 Views
Last Modified: 2008-03-04
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

0
Comment
Question by:peteodonnell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2630777
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.
0
 
LVL 2

Author Comment

by:peteodonnell
ID: 2636416
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.
0
 
LVL 6

Accepted Solution

by:
Jon_Raymond earned 50 total points
ID: 2638650
The only thing that comes to mind is that you might need a newer version of DAO from the MDAC pack www.microsoft.com/data
0
 
LVL 2

Author Comment

by:peteodonnell
ID: 2640494
..maybe I'll try that.  Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question