Solved

Syntax Error In Query Expression, Max Length of Sql Statement?

Posted on 2014-03-25
4
1,146 Views
Last Modified: 2014-03-25
This is a routine in my application that writes information to a table in the database:

Public Sub writePropertyComment(passedPropertyID As Long, _
                                passedComment As String, _
                                Optional passedUserName As String = "", _
                                Optional passedCommentTypeID As Long = 1, _
                                Optional passedBRT As Long = 0)
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'                             
                               If IsDeveloper Then
                               Else
                                 On Error GoTo writePropertyComment_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'
Dim wkUserName As String
Dim wkDateTimeAdded As Date
wkDateTimeAdded = Now

'
If Len(Trim(passedUserName)) > 0 Then
    wkUserName = passedUserName
Else
    wkUserName = GimmeUserName
End If

' add the comment
'
DoCmd.SetWarnings False
'
CurrentDb.Execute " insert into tblProperty_Comments " & _
              "( [BRT], [PropertyID], [CommentTypeID], [Comment], [DateAdded], [UserAdded]   )  " & _
  "   values(" & passedBRT & _
          ", " & passedPropertyID & _
          ", " & passedCommentTypeID & _
          ", " & Chr(34) & passedComment & Chr(34) & _
          ", " & Chr(35) & wkDateTimeAdded & Chr(35) & _
          ", " & Chr(34) & wkUserName & Chr(34) & _
          ")"


'
' Replace all of the below with an insert command for better speed
'
'
'Dim rsOut As ADODB.Recordset
'Set rsOut = New ADODB.Recordset
'rsOut.Open "tblProperty_Comments", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
''
'With rsOut
'    .AddNew
'        !PropertyID = passedPropertyID
'        !BRT = passedBRT
'        !CommentTypeID = passedCommentTypeID
'        !Comment = passedComment
'        !DateAdded = Now()
'        If Len(Trim(passedUserName)) > 0 Then
'            !UserAdded = passedUserName
'        Else
'            !UserAdded = GimmeUserName
'        End If
'    .Update
'End With
''
'rsOut.Close
'Set rsOut = Nothing
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
writePropertyComment_Error:
                               sysErrorHandler Err.Number, Err.Description, "writePropertyComment", "modEvent_Import_Export_Comments", "Module"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------



End Sub

Open in new window


Initially I used the commented ADO recordset code to add a comment record.  I later revised it to the "CurrentDB.Execute...." to improve the performance.

It did improve the performance but now I get 3075 (syntax error is query expression...) error when the users enter a particularly long, actually small book of a comment,

The recieving field [Comment] of the insert is type 'Memo' so a small or even large book should be OK.  The statement has been in production for several months and written thousands of records without issues, so it works when the comment is not very, very long.

The error message shows the query expression that got the error and it is truncated somewhere in the middle of the comment.

My quess is that the insert string exceeded the maximum length permissible for a query expression, causing it to truncate.

Can anyone verify this.  What is the maximum permissible length?

Another alternative I've considered is that some character in the comment is causing the SQL to truncate.  Should I replace certain characters in the comment that might cause issues in the generated SQL ststement?  What characters could cause issues?

Truncating the comment is not permissible so if the lenght of the comment is causing the max lenght of the SQL to be exceeded my only alternative appears to be going back to the slower, ADO recordset method of adding the comment to the table.

Are their any alternatives that I'm missing?
0
Comment
Question by:mlcktmguy
[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
4 Comments
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 200 total points
ID: 39954101
I think the maximum length of a SQL string is around 64K - certainly not a limit I have encountered!

Are you sure you don't have any double-quotes in your comment string?  If so, then the first quote it encounters will be interpreted as the closing delimiter for the string, and everything after it will be invalid SQL.

You must "escape" the quotes within the string by doubling them.  Try this:

CurrentDb.Execute " insert into tblProperty_Comments " & _
              "( [BRT], [PropertyID], [CommentTypeID], [Comment], [DateAdded], [UserAdded]   )  " & _
  "   values(" & passedBRT & _
          ", " & passedPropertyID & _
          ", " & passedCommentTypeID & _
          ", " & Chr(34) & Replace( passedComment, Chr(34), Chr(34) & Chr(34)) & Chr(34) & _
          ", " & "Now()" & _
          ", " & Chr(34) & wkUserName & Chr(34) & _
          ")" _
  , dbFailOnError

Open in new window


Note that I have put the Now() function in the string, as SQL understands it.  I have also added dbFailOnError, so that a trappable error will occur if the INSERT fails.

Also, you don't need SetWarnings False with Database.Execute.

Best wishes,
Graham Mandeno [Access MVP 1996-2014]
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 39954103
What is the maximum permissible length?

Open in new window

Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically

Access Specifications
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 200 total points
ID: 39954140
By the way, enquoting text for SQL statements and escaping any internal quotes is such a common operation that I use the following library function:
Public Function SQLText(varText As Variant) As String
Const SQ = "'", SQ2 = "''"
Dim strResult As String
  strResult = "" & varText
  If InStr(strResult, SQ) <> 0 Then
    strResult = Replace(strResult, SQ, SQ2)
  End If
  SQLText = SQ & strResult & SQ
End Function

Open in new window

You can then use it thus:
CurrentDb.Execute " insert into tblProperty_Comments " & _
              "( [BRT], [PropertyID], [CommentTypeID], [Comment], [DateAdded], [UserAdded]   )  " & _
  "   values(" & passedBRT & _
          ", " & passedPropertyID & _
          ", " & passedCommentTypeID & _
          ", " & SQLText(passedComment) & _
          ", " & "Now()" & _
          ", " & SQLText(wkUserName) & _
          ")" _
  , dbFailOnError

Open in new window


I use a similar function for formatting and delimiting dates and times for SQL.

Another tip is to assign the whole SQL string to a local variable and then execute the variable.  That makes it easier to debug because you can examine the complete string an you step through the code, and even copy it and paste it into a query to test it.

-- Graham
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39955088
Excellent information, thank you.
0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

738 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