VBA / SQL ADODB - update statement with percent % symbol

[PinkeyNBrain]

Having trouble with a % symbol in a SQL update statement. The following is largely striped down, but illustrates the issue

  set rs = New ADODB.Recordset
  With rs
    .ActiveConnection = con_ref          ' assume con_ref set up in preceding code
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    sql_cmd = "UPDATE tblCalc SET buffer_name='CUT ID TOL' WHERE Order = '100254-1-1'"
    .Open sql_cmd
  End With

Problem is that the actual command I want to send is:

  sql_cmd = "UPDATE tblCalc SET buffer_name='CUT % ID TOL' WHERE Order = '100254-1-1'"

This generates the error, "String or binary data would be truncated". The % symbol is coming from another source and I don't have the option to remove or substitute the symbol. I've tried these:

  UPDATE tblCalc SET buffer_name='CUT \% ID TOL' WHERE MillOrder = '100254-1-1'
' The above generates the same error message
UPDATE tblCalc SET buffer_name='CUT ~% ID TOL' WHERE MillOrder = '100254-1-1' ESCAPE '~'
' This one generates a syntax error near ESCAPE
UPDATE tblCalc SET buffer_name='CUT ~% ID TOL' WHERE MillOrder LIKE '100254-1-1' ESCAPE '~'
' This goes back to generating the original error message

Working with SQL Server 2005 and MS Excel 2010.

 

[PinkeyNBrain]

Forgot to include that I've tried this as well

UPDATE tblCalc SET buffer_name='CUT [%] ID TOL' WHERE MillOrder = '100254-1-1'

 

[strongm]

Firstly, the ESCAPE clause can only be used with LIKE and NOT LIKE, hence the syntax error.

Secondly, I don't believe that the problem is the %. Check the underlying datatype for the buffer_name column; can I suggest that you make it text?

 

[Andrzejek]

You don't need a recordset to Update.

Have you tried:

Dim strSQL As String

strSQL = "Update tblCalc SET buffer_name ..."[blue]
con_ref.Execute strSQL[/blue]

Have fun.

---- Andy

 

[PinkeyNBrain]

Kinda figured that about the LIKE/ESCAPE combo. Any of the reference examples I found always paired ESCAPE w/ LIKE them but didn't notice an 'only if' rule between them.

strongm - thanks for making me feel like such a novice While the column is text, it's defined as nchar(10) - The intended string is length 12. Hence the error message was trying to tell me exactly what I needed to know. Set to nchar(20) and whatdaya know - it works.

Andrzejek - thanks as well for the tip on con_ref.Execute. The strSQL command is built in another procedure which passes it into a canned routine to execute it. I suspect I can upgrade the canned routine to seperate off these types of commands and likely run a little faster if it isn't building the Recordset contruct.

 

[Andrzejek]

PinkeyNBrain,
I use recordsets for SELECT statements only.
For UPDATE, INSERT, or DELETE, I use:
[tt]
strSQL = "UPDATE...." (Insert, Delete)
MyConnection.Execute strSQL[/tt]

Have fun.

---- Andy