SQL query > 255 chars in length for VB6

[JP]

I am using VB6 with the following code. If my string "SQL" is > then
255 characters in length, it fails on the execute. Less than that and
it works fine. Is there any work around?
Thanks,
-Jim

Public Sub InsertUpdate(SQL As String)


Dim lngErrorIndex As Long
Dim objCmd As ADODB.Command
Dim SQLarray(10) As String
Dim i As Integer


On Error GoTo Error_Handler

lngErrorIndex = 1037
Set objCmd = New ADODB.Command



If Not mConnection Is Nothing Then
With objCmd
.ActiveConnection = mConnection
.CommandText = SQL
.CommandType = adCmdText
.Execute
End With
End If

Exit Sub

 

[Douglas J. Steele]

I've never heard of any such limitation. Are you sure the sub is getting the
value correctly?

 

[JP]

I've never heard of any such limitation. Are you sure the sub is getting the
value correctly?

Definately. I went over it character by character after it had reached
the sub. It is correct. Also, this particular insert it fails on has
13 fields with 13 fairly lengthy values (about 280 chars total). Just
before this one however, I make a call for a table with only two fields
(less than 100 chars) and that works fine.

-Jim

 

[Brendan Reynolds]

The following test produces a SQL string of 368 characters. It executes
without error and entirely as expected. I'm not sure what is causing your
problem, Jim, but it doesn't seem to be the length of the SQL string.

Public Sub LongSQL()

Dim strSQL As String

strSQL = "INSERT INTO tblTest(TestOne, TestTwo, TestThree) VALUES ('" &
String$(100, "A") & "', '" & _
String$(100, "B") & "', '" & String$(100, "C") & "')"
Debug.Print Len(strSQL)
CurrentProject.Connection.Execute strSQL

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[JP]

The following test produces a SQL string of 368 characters. It executes
without error and entirely as expected. I'm not sure what is causing your
problem, Jim, but it doesn't seem to be the length of the SQL string.

Brendan,

Thanks for your help. It turns out the last field I was updating was
called "Year" which must be a SQL reserved word. When I deleted the
last two fields, I got 251 one characters, which I thought was the
significant fact. There were a few KB articles describing other 255
char limits, so I jumped to the wrong conclusion. Once I saw that your
sample was working ok I pursued it further.

-Jim

 

[???]

I am using VB6 with the following code. If my string "SQL" is > then
255 characters in length, it fails on the execute. Less than that and
it works fine. Is there any work around?
Thanks,
-Jim

Public Sub InsertUpdate(SQL As String)


Dim lngErrorIndex As Long
Dim objCmd As ADODB.Command
Dim SQLarray(10) As String
Dim i As Integer


On Error GoTo Error_Handler

lngErrorIndex = 1037
Set objCmd = New ADODB.Command



If Not mConnection Is Nothing Then
With objCmd
.ActiveConnection = mConnection
.CommandText = SQL
.CommandType = adCmdText
.Execute
End With
End If

Exit Sub