Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-09-24
8
Medium Priority
?
429 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

688 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