String length limits hit in SQL statement

String length limits hit in SQL statement

Post by Dave Emmit » Sat, 17 Nov 2001 00:00:08



I think this is what is happening. I am executing or trying to execute
very long SQL statements in my VB app but now I am running into the
physical limits of VB's String type. Does anyone know a work around?

Here are some of my code segments:

sReturn = "SELECT BillPeriod, BilledNumber, CallDate, ConnectTime, " & _

        "RateClass, FromNumber, FromPlace, CalledNumber, " & _
        "CalledPlace, CallDuration, Minutes, " & _
        "CallCount, Amount, DataFileName as 'Source' " & _
        "FROM Data "
...
sReturn = sReturn + "WHERE BillPeriod = '" & sBillingPeriod & "'"
...
sReturn = sReturn + " AND (CallDate BETWEEN #" & Format(tbFromDate.Text,
"mm/dd/yy") & "# AND #" & Format(tbToDate.Text, "mm/dd/yy") & "#)"

If I use a SELECT * in place of the long SELECT statement shown above
everything works fine but when I try to use the SELECT statement shown
above my SQL statement gets cut off.

Thanks,

Dave

 
 
 

String length limits hit in SQL statement

Post by Dave Emmit » Sat, 17 Nov 2001 01:57:15


After more searching I found the answer to my problem.

I'm using DAO Recordset and a Data Control -- the Name property of a DAO
Recordset object is limited to 256 characters. I should use a string
variable to set the RecordSource property  before refreshing the data
control like:

Data1.RecordSource = SQLStr
Data1.Refresh

I had been using something like this:

Data1.RecordSource = rsData.Name
Data1.Refresh

I guess I had been right around the 256 character limit.

(See Q168194 - PRB: DAO Recordset Name Property Is Limited to 256
Characters)

Dave


> I think this is what is happening. I am executing or trying to execute
> very long SQL statements in my VB app but now I am running into the
> physical limits of VB's String type. Does anyone know a work around?

> Here are some of my code segments:

> sReturn = "SELECT BillPeriod, BilledNumber, CallDate, ConnectTime, " & _

>         "RateClass, FromNumber, FromPlace, CalledNumber, " & _
>         "CalledPlace, CallDuration, Minutes, " & _
>         "CallCount, Amount, DataFileName as 'Source' " & _
>         "FROM Data "
> ...
> sReturn = sReturn + "WHERE BillPeriod = '" & sBillingPeriod & "'"
> ...
> sReturn = sReturn + " AND (CallDate BETWEEN #" & Format(tbFromDate.Text,
> "mm/dd/yy") & "# AND #" & Format(tbToDate.Text, "mm/dd/yy") & "#)"

> If I use a SELECT * in place of the long SELECT statement shown above
> everything works fine but when I try to use the SELECT statement shown
> above my SQL statement gets cut off.

> Thanks,

> Dave


 
 
 

1. Limit to length of string displayed in SELECT statement

Hi.

I am concatonating a number of different fields together
to create an email message for use with xp_sendmail.  

When the length of a column string reaches 255 (256?)
characters, the remainder of the string is lost.  Is
there any way to get string values longer than this to
display?  I have worked around this problem by creating a
new SELECT statement every few lines, but this is hardly
an elegant solution...

I am just using a normal SELECT, with default datatypes.  
Putting in a DECLARE and setting a different or longer
datatype has not seemed to work, but I may just be using
the wrong type.  

Any suggestions on this?

Thanks in advance,

Dan

2. Three-Tier without D3 C/S ? Your suggestions and comments please...

3. Hit Max limit for SQL String?

4. Binding Nightmares

5. SQL string length limit in VB3.0????

6. Any possibility of restricting schema view?

7. SQL string length limit in VB 4?

8. Pick programmer wants a job

9. 128 character SQL String length limit??

10. limits of SQL statement length

11. SQL statement length limit?

12. ODBC SQL Statement length limit of 64k???

13. SQL statement length limits