Solved

VB error when writing a large amount of text from VB6 ADO to Access memo field

Posted on 2004-09-24
8
399 Views
Last Modified: 2013-12-25
I am getting some kind of strange error when I write a large (but not really that large) amount of text to an access memo field  from a VB6 app using ADO. Here are the details...

Below is the proc where the problem ocurs. The large amount of text is coming in to the funciton in the strQuestionText parameter. Here is the text I am trying to write that is causing me a problem:

Next you will see a movie preview for one of these movies, FRIDAY NIGHT LIGHTS, and then answer a few questions about it. | Some of the scenes are in an unfinished state, and may not look sharp to you.  When it is finished it will be in full color and as technically perfect as any other preview you might see. So do not let the unfinished state of the preview interfere with your overall reaction.                                                
I am running this code in Debug mode when the error occurs. It is not a run-time error, it seems to be a VB environment error, because VB crashes when it happens.

To be more specific about this error - when I call the blnCreateNewQuestionExpress function, the function completes and does not report an error. but then I call  the blnGetNewlyCreatedQuestionID function, and VB crashes when I try to execute the query.      

Any help would be appreciated. I do seem to be able to run these queries inside access without problems, it is running them from VB that causes the problems (hence the error from the VB environment.)
                                    



Public Function blnCreateNewQuestionExpress( _
ByRef lngSurveyID As Long, _
ByRef strQuestionName As String, _
ByRef lngSectionID As Long, _
ByRef lngQuestionTypeID As Long, _
ByRef strQuestionText As String, _
ByRef strQuestionTextRight As String, _
ByRef blnIsMemberOfAGroup As Boolean, _
ByRef lngQuestionGroupID As Long, _
ByRef lngDataStorageMethodID As Long, _
ByRef blnIsRotatingResponses As Boolean, _
ByRef lngRotationTypeID As Long _
) As Boolean
'creates a new question record in the database. This is the express method, which does not populate every field of the question record in the database.

    On Error GoTo errortrap
   
    Dim strsql As String
   
    blnCreateNewQuestionExpress = False
   
    strsql = "CreateNewQuestionExpress " & lngSurveyID & ",'" & strQuestionName & "'," & lngSectionID & "," & lngQuestionTypeID & ",'" & strQuestionText & "','" & strQuestionTextRight & "'," & blnIsMemberOfAGroup & "," & lngQuestionGroupID & "," & lngDataStorageMethodID & "," & blnIsRotatingResponses & "," & lngRotationTypeID
   
    On Error Resume Next
   
    CommonCode.mConnSurvey.Execute strsql, , adCmdStoredProc
   
    If err Then
        CommonCode.DisplayError err, "Error attempting to create the new question in the database.", False
        err.Clear
        GoTo dropout
    End If
   
    On Error GoTo errortrap
   
    'if we got to this point with no errors, then the insert was a success
    blnCreateNewQuestionExpress = True
dropout:
    Exit Function
errortrap:
    CommonCode.DisplayError err, "Unexpected error in modfunctions.blnCreateNewQuestionExpress.", False
    err.Clear
   
End Function


Public Function lngGetNewlyCreatedQuestionID(ByRef lngSurveyID As Long, ByRef strQuestionText As String) As Long
'retrieves the question id for the survey id and question text passed in
    On Error GoTo errortrap
   
    Dim strsql As String
    Dim objrst As ADODB.Recordset
   
    lngGetNewlyCreatedQuestionID = 0
   
    Set objrst = New ADODB.Recordset
   
    strsql = "GetNewlyCreatedQuestionID '" & strQuestionText & "'," & lngSurveyID
   
    On Error Resume Next
   
    objrst.Open strsql, CommonCode.mConnSurvey, , , adCmdStoredProc
   
    If err Then
        CommonCode.DisplayError err, "Unable to retrieve Question ID for survey id '" & CStr(lngSurveyID) & "' Question Text '" & strQuestionText & "'.", False
        err.Clear
        GoTo dropout
    End If
   
    On Error GoTo errortrap
   
    If objrst.EOF Then
        GoTo dropout
    End If
   
    lngGetNewlyCreatedQuestionID = objrst.Fields("questionid").Value
   
   
dropout:
    CommonCode.KillObject objrst, "adodb.recordset"
    Exit Function
errortrap:
    CommonCode.DisplayError err, "Unexpected error in modfunctions.lngGetNewlyCreatedQuestionID", False
    err.Clear
    CommonCode.KillObject objrst, "adodb.recordset"
   
End Function
0
Comment
Question by:jkwasson
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:Sethi
ID: 12149346
You are not taking care of single quotes in the text and that may be one of the cause of problem. Before netering text or memo data in database always take care of single quotes like this:

strQuestionName = Replace(strQuestionName,"'","''")

Now use this in insert statement.
0
 
LVL 5

Author Comment

by:jkwasson
ID: 12149354
good observation....I should have noted that I was cleaning those before the value goes into the function blnCreateNewQuestionExpress. So, basically what I am saying is that I know apostrophe's is not the issue because I am already cleaning those, just not in the code I included in the message.

I do have some more info though, from further testing. I have found a work around. I have done the following and it seems to be working:

instead of calling a saved query (stored proc) in access, I am just building the sql statement and assigning it to strsql. I also changed the option paramter when executing the query to adcmdtext. I have also moved that QuestionText database field (it is type Memo) to the right most column of the database. All of these things appear to allow me to save and then re-query the data with no errors. I am going to leave the question open though, because I would really like to know what it is about using the stored procedure and passing a large text field to Access that is causing the problem.  I'll keep checking it to see if you guys come up with anything.

Thanks.
0
 
LVL 5

Author Comment

by:jkwasson
ID: 12149356
and one more thing...I have also changed the query in the lngGetNewlyCreatedQuestionID to a text query (adcmdtext).
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 18

Expert Comment

by:Sethi
ID: 12149413
Options parameter can be left empty if you want. It is by default Unknown in value and hardly effects the speed of the execution, although if given is a good practice. But one needs to be very sure about this parameter.

Placing Memo as last column is definately not the reason as I have never placed it in the end and have never had any type of problem while working with large data.
0
 
LVL 5

Author Comment

by:jkwasson
ID: 12149424
I don't really know why moving memo to the last field made a difference. I ran into some Microsoft KB articles (wish I had saved the numbers...) that mentioned this as being a possible issue for SQL Server, and it seemed to imply it could be an issue for other Microsoft products as well. So the first thing I did was to move that field to the end of the table, and as soon as I made that change, I was able to save the value in that memo field...so it seemed to make a difference, although I am really not sure why.

The main thing I am wondering about is why querying that value in the second procedure caused the VB environment to crash...

0
 
LVL 18

Expert Comment

by:Sethi
ID: 12149444
:-) Can be a possible Microsoft bug. I have never encountered it
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 12149872
Hi jkwasson

Ive found when appending memo data using a stored query that expects input parameters, I have much more success if I use an ADODB.Command to correctly define the input parameters.

The defined size for a memo parameter in ADOis 1073741823 and the ADO datatype is adLongVarWChar (203).

You dont need to assign the full size reservation, if you know the length of the memo data you are appending, you set the size to len of your memo string variable; len(varMemo).

Never had any problems working with Memo fields when using a command object.

partial example:
I haven't defined all the variables etc...

Dim sMemo as String
sMemo = "Some long text"

  set cmd = CreateObject("ADODB.Command")
  with cmd
    .CommandText = "spJetPackBlob"     '<= name of stored query that expects 7 input parameters
    .CommandType = adCmdStoredProc        
    .Parameters.Append .CreateParameter("pFileName", adVarChar, adParamInput, 255, sFileName)
    .Parameters.Append .CreateParameter("pFileType", adVarChar, adParamInput, 255, sFileType)
    .Parameters.Append .CreateParameter("pDateCreated", adDBTimeStamp, adParamInput, 8, dDateCreated)
    .Parameters.Append .CreateParameter("pDateLastAccessed", adDBTimeStamp, adParamInput, 8, dDateLastAccessed)
    .Parameters.Append .CreateParameter("pFileSize", adInteger, adParamInput, 4, lFileSize)
    .Parameters.Append .CreateParameter("pFileExtension", adVarChar, adParamInput, 255, sFileExtension)
    .Parameters.Append .CreateParameter("pMemo", adLongVarWChar, adParamInput, len(sMemo), sMemo )
    Set .ActiveConnection = cn
   .Execute , , adExecuteNoRecords
  end with






Alan



0
 
LVL 5

Author Comment

by:jkwasson
ID: 12176810
tried the command object and that worked without giving me the errors, so I am going to go with that. Thanks for all of your help :)

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

821 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