String Truncation Problem

[b19620719]

I already posted this as a response to another thread, but thought I should post it as a new one in case people don't follow threads...

I need to construct a SQL statement to insert a row. The VB code used to construct the insert statement is very long (the actual insert statement will be much shorter). For some reason that I do not understand, it is truncated after 255 bytes.

My VB code is:

Dim InsertStatement As String

InsertStatement = "INSERT INTO tCoverage (CompanyCd, ProvinceCd, " & _
"LOBCd, ProductID, CoverageCd, NBSStartDt, WIPVersion, " & _
"RWLStartDt, ScreenID, WithoutCreditChargeInd, " & _
"GrandfatheredInd, OverrideModeInd, EndorsementTypeCd, " & _
"NoteText, AvailabiltyOfOPCFsTxt, LimitDedTypeCd, LimitDedGroupCd) " & _
"VALUES ('" & Forms!fCoverageDetail!CompanyCd & "', " & _
Forms!fCoverageDetail!ProvinceCd & "', " & _
Forms!fCoverageDetail!LOBCd & "', " & _
Forms!fCoverageDetail!ProductID & "', " & _
Forms!fNewVersionDates!newNBSStartDt & ", 0, " & _
Forms!fNewVersionDates!newRWLStartDt & ", " & _
Forms!fCoverageDetail!ScreenID & ", " & _
Forms!fCoverageDetail!WithoutCreditChargeInd & ", " & _
Forms!fCoverageDetail!GrandfatheredInd & ", " & _
Forms!fCoverageDetail!OverrideModeInd & ", " & _
Forms!fCoverageDetail!EndorsementTypeCd & ", " & _
Forms!fCoverageDetail!NoteText & ", " & _
Forms!fCoverageDetail!AvailabiltyOfOPCFsTxt & ", " & _
Forms!fCoverageDetail!LimitDedTypeCd & ", " & _
Forms!fCoverageDetail!LimitDedGroupCd & ")"

When I look at the InsertStatement string in debug, it looks like the following:

"INSERT INTO tCoverage (CompanyCd, ProvinceCd, LOBCd, ProductID, CoverageCd, NBSStartDt, WIPVersion, RWLStartDt, ScreenID, WithoutCreditChargeInd, GrandfatheredInd, OverrideModeInd, EndorsementTypeCd, NoteText, AvailabiltyOfOPCFsTxt, LimitDedTypeCd, Limi"

It is truncated after byte 255. Does anybody know why this is occurring?

 

[KalelGmoon]

Don't quote me on this as I am a bit of a novice at SQL, but I believe the compiler? can only handle up to 255 characters at a time in one command. I dont know if you can break it up into smaller bits for it to work or not.

 

[jeremypaule]

jw if it has to be a string? have you tried DoCmd, that may make a difference. Or just use and object query.

 

[boblarson]

I believe you can do it this way (I could be wrong, but ...)

InsertStatement = "INSERT INTO tCoverage (CompanyCd, ProvinceCd, " & _
"LOBCd, ProductID, CoverageCd, NBSStartDt, WIPVersion, " & _
"RWLStartDt, ScreenID, WithoutCreditChargeInd, " & _

InsertStatement = InsertStatement & "GrandfatheredInd, OverrideModeInd, EndorsementTypeCd, " & _
"NoteText, AvailabiltyOfOPCFsTxt, LimitDedTypeCd, LimitDedGroupCd) " & _
"VALUES ('" & Forms!fCoverageDetail!CompanyCd & "', " & _
Forms!fCoverageDetail!ProvinceCd & "', " & _

InsertStatement = InsertStatement & Forms!fCoverageDetail!LOBCd & "', " & _
Forms!fCoverageDetail!ProductID & "', " & _
Forms!fNewVersionDates!newNBSStartDt & ", 0, " & _

InsertStatement = InsertStatement & Forms!fNewVersionDates!newRWLStartDt & ", " & _
Forms!fCoverageDetail!ScreenID & ", " & _
Forms!fCoverageDetail!WithoutCreditChargeInd & ", " & _

InsertStatement = InsertStatement & Forms!fCoverageDetail!GrandfatheredInd & ", " & _
Forms!fCoverageDetail!OverrideModeInd & ", " & _
Forms!fCoverageDetail!EndorsementTypeCd & ", " & _

InsertStatement = InsertStatement & Forms!fCoverageDetail!NoteText & ", " & _
Forms!fCoverageDetail!AvailabiltyOfOPCFsTxt & ", " & _
Forms!fCoverageDetail!LimitDedTypeCd & ", " & _

InsertStatement = InsertStatement & Forms!fCoverageDetail!LimitDedGroupCd & ")"

I just broke it up kind of arbitrarily, and you may need to do it for each line, but it should just need to be done to keep each assignment to 255 characters or less but concatenating the previous string with the next. I hope that makes sense. I haven't done this in practice, but I remember seeing this on the forum here at some other time.