Sql statement too long???

[panadoll]

Hi,

I am developing an application using vb with access 2000.

Currently, I encountered a problem when i am executing a INSERT INTO ...VALUE statement from vb. It keeps prompting me that the statement is wrong. I copied the statement and pasted it into the access 2000 query and it works fine.

This is the sql statement generated from executing of codes in vb. if u look at [SM-007-007] in the statement below, u will see that the line is spilted into [SM-0 and 07-007].

INSERT INTO [IFA Login] ([UserName], [Password], [Opt_Admin_Normal], [FL-001],[AC-001],[CR-001],[PR-001],[PR-002],[CR-002],[AC-002],[FL-002],[CR-003],[AC-003],[PR-003],[FL-003],[CR-004],[SM-006-001],[IV-002-001],[SM-003-001],[IV-003-001],[AR-003-001],[AP-001-001],[AR-002-001],[AR-005-001],[SM-005-001],[SM-004-001],[IV-001-001],[AP-002-001],[SM-016-001],[SM-015-001],[SM-014-001],[SM-007-001],[SM-012-001],[SM-011-001],[SM-008-001],[AR-001-001],[SM-013-001],[IV-003-002],[AR-003-002],[SM-016-002],[SM-013-002],[AP-002-002],[SM-012-002],[SM-011-002],[SM-008-002],[AR-001-002],[AR-002-002],[SM-003-002],[SM-007-002],[AR-005-002],[SM-005-002],[IV-002-002],[SM-004-002],[SM-006-002],[AP-001-002],[IV-001-002],[IV-003-003],[AR-002-003],[AR-001-003],[IV-002-003],[AP-001-003],[SM-016-003],[SM-006-003],[SM-003-003],[SM-007-003],[SM-012-003],[SM-005-003],[SM-005-004],[AR-001-004],[SM-006-004],[SM-007-004],[IV-003-004],[AP-001-004],[IV-002-004],[SM-003-004],[SM-003-005],[SM-006-005],[AR-001-005],[SM-007-005],[SM-007-006],[SM-0
07-007]) VALUES ('PEPE', '', 1, 'V','','V, A, E, D','','','V, E','','V','V, A, E, D','','','V','V, E','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','')

If I copy this junk of codes and paste into Access 2000 query and correct the [SM-007-007], it will run properly like the example below.
INSERT INTO [IFA Login] ([UserName], [Password], [Opt_Admin_Normal], [FL-001],[AC-001],[CR-001],[PR-001],[PR-002],[CR-002],[AC-002],[FL-002],[CR-003],[AC-003],[PR-003],[FL-003],[CR-004],[SM-006-001],[IV-002-001],[SM-003-001],[IV-003-001],[AR-003-001],[AP-001-001],[AR-002-001],[AR-005-001],[SM-005-001],[SM-004-001],[IV-001-001],[AP-002-001],[SM-016-001],[SM-015-001],[SM-014-001],[SM-007-001],[SM-012-001],[SM-011-001],[SM-008-001],[AR-001-001],[SM-013-001],[IV-003-002],[AR-003-002],[SM-016-002],[SM-013-002],[AP-002-002],[SM-012-002],[SM-011-002],[SM-008-002],[AR-001-002],[AR-002-002],[SM-003-002],[SM-007-002],[AR-005-002],[SM-005-002],[IV-002-002],[SM-004-002],[SM-006-002],[AP-001-002],[IV-001-002],[IV-003-003],[AR-002-003],[AR-001-003],[IV-002-003],[AP-001-003],[SM-016-003],[SM-006-003],[SM-003-003],[SM-007-003],[SM-012-003],[SM-005-003],[SM-005-004],[AR-001-004],[SM-006-004],[SM-007-004],[IV-003-004],[AP-001-004],[IV-002-004],[SM-003-004],[SM-003-005],[SM-006-005],[AR-001-005],[SM-007-005],[SM-007-006],[SM-007-007]) VALUES ('PEPE', '', 1, 'V','','V, A, E, D','','','V, E','','V','V, A, E, D','','','V','V, E','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','')

Is there a limit to the number of characters for a string literal?

What can i do to solve this prob?

Sorry for the big junk of codes....
Thanks in advance for any advice given.

 

[CassandraR]

Hi Panadoll:

I don't know about limits on SQL statements in VB, but there is a suspicion with your statement. It breaks at the 1024th character. Maybe this means something in your code, like defining the string variable as 1024-characters. long

HTH,
Cassie

 

[jopaumier]

I have very long SQL queries as well. I build the string

dim strSQL as string

strSQL = ""
strSQL = strSQL & "INSERT INTO ..."
strSQl = strSQL & ...

until I am done. I try to make each line break at logical points so I can read it or when I have to make changes. I then execute the SQL as follows:

cn.Execute strSQL

where cn is a connection to my database.

I have not counted the number of characters in any one SQL query, so maybe there is a limit even with my method.

Jim

 

[CCLINT]

What is the error number and exact error descrition?