Solved

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

Posted on 2014-03-25
4
1,105 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
  • 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 34

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

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 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

23 Experts available now in Live!

Get 1:1 Help Now