SQL String being cut off

[Jordan C. Schroeder]

This has been a mystery to me for some time now... What I am doing is
assigning a query to a Com.commandText object by saying:

com.CommandText = "INSERT INTO table (field, field1, field2)" & _
"VALUES ("Value", "Value1", "Value2");
com.execute

The actual query is much longer, but what is happening is the string is
being cut off about half way through. So a debug.print looks like:

INSERT INTO table (field, field1, field2)
VALUES ("Va

I have a text editor to count spaces, and it is only holding 255. This is
not the first time I have seen this, is there any explanation?

Thanks!

 

[Steve Sanford]

It looks like there might be a carrige return embedded in the string.

I like creating strings like this

Dim sSTR ad String

sSTR = "INSERT INTO table (field, field1, field2)"
sSTR = sSTR & " VALUES ('Value', 'Value1', 'Value2');"

DeBug.Pring sSTR

I can check to make sure spaces are in the right place.

In your example, you would get an error because the delimiters are not
correct. Notice I changed the delimiters. Numbers don't need delimiterd and
dates are delimited with # (#1/1/2008#)


HTH

 

[Dale Fye]

In addition to the comments Steve made, I would note that you need a space
between the ) and the word "Values"

The technique I generally use, as opposed to the way Steve mentions is:

strSQL = "INSERT INTO table (field, field1, field2)" _
& " Values ('Value', 'Value1', 'Value2')"

Note the space at the beginning of line two, right after the ". I like to
make sure that I have the spaces at the beginning of the line, so that I can
see that it is actually there.


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Albert D. Kallal]

Two things I would check:

I would first make sure that you've not accidentally bound that text box to
a field that has a limit of 255 characters. (non memo field).

I believe there is also an issue that if the text box as any type of
formatting on it, or input masks, then again your text will get messed up
and truncated to 255 chars.

I would ensure there's no formatting or input masks on that text box control
at all.