?
Solved

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

Posted on 2014-03-25
4
Medium Priority
?
1,179 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 800 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 38

Assisted Solution

by:PatHartman
PatHartman earned 200 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 800 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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