Solved

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

Posted on 2004-09-24
8
391 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
Comment Utility
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
Comment Utility
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
Comment Utility
and one more thing...I have also changed the query in the lngGetNewlyCreatedQuestionID to a text query (adcmdtext).
0
 
LVL 18

Expert Comment

by:Sethi
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

Author Comment

by:jkwasson
Comment Utility
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
Comment Utility
:-) Can be a possible Microsoft bug. I have never encountered it
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now