Hi

Environment VB6 SP5

I'm attempting to dynamically build a SQL string in VB for inclusion in a Command object. Attempting being the operative term in this instance.

I am having difficulty building a SQL string longer than 260 chars. I'm somewhat bemused by this one. My understanding is that String Variables hold monstrous numbers of characters(0 to approximately 2 billion according to MS).
The code is as follows:



shows my test. I'm replacing the where clause with a string of numbers for test purposes. sSQLCmd is declared as string. Its length is 231.

The output for sSQLCmd from the watch window is as follows:

"SELECT b.Criteria, a.CriteriaId, a.CountryId, a.CategoryId, a.CountryCriteriaScore ,a.CountryCriteriaNotes FROM TblCountryCriteria as a ,TblCriteriaDefinition as b WHERE a.CategoryId = b.CategoryId AND a.CriteriaId = b.CriteriaId 1234567890123456789 "

This demonstrates that the string that I attempt to concatenate onto sSQLCmd gets truncated. Not entirely the effect I was after!:(

Unless my simple maths has flaked out and there is some curious quantum effect happening I should be able to make this concatenation without any issue. Any ideas?

The answer is probably very simple

I just tested your code and it seems to work fine.
I'm using Win2K VB6 SP5.
Here's my test code.

Private Sub Form_Load()

    Dim sSQLCmd As String
    Dim iLen As Integer
    Dim swhereclause As String
    Dim sCategoryIdSelected As String
    Dim sCountryIdSelected As String
    Dim iCategoryIdSelected As Integer
    Dim iCountryIdSelected As Integer

    iCategoryIdSelected = 99
    iCountryIdSelected = 88

    sCategoryIdSelected = CStr(iCategoryIdSelected)
    sCountryIdSelected = CStr(iCountryIdSelected)

    sSQLCmd = ""

    sSQLCmd = "SELECT b.Criteria, a.CriteriaId, a.CountryId, a.CategoryId, a.CountryCriteriaScore ,a.CountryCriteriaNotes" & _
                " FROM TblCountryCriteria as a ,TblCriteriaDefinition as b" & _
                " WHERE a.CategoryId = b.CategoryId " & _
                " AND a.CriteriaId = b.CriteriaId "

    iLen = Len(sSQLCmd)
    swhereclause = " AND a.CategoryId = " & sCategoryIdSelected
    swhereclause = swhereclause & " AND a.CountryId = " & sCountryIdSelected

    swhereclause = "1234567890123456789012345678901234567890123456789 0123456789A"

    sSQLCmd = sSQLCmd & swhereclause

End Sub

The value of sSQLCmd after that last assignment is:

SELECT b.Criteria, a.CriteriaId, a.CountryId, a.CategoryId, a.CountryCriteriaScore ,a.CountryCriteriaNotes FROM TblCountryCriteria as a ,TblCriteriaDefinition as b WHERE a.CategoryId = b.CategoryId AND a.CriteriaId = b.CriteriaId 12345678901234567890123456789012345678901234567890 123456789A

and iLen = 231

Jack

Thanks for your reply. Glad I'm not being totally irrational. I'm relieved my code worked in your environment. I thought I was losing the plot completely. I guess the issue now is to figure out why it doesn't work in my environment Win98

I have a feeling this could be one of those long and frustrating investigations!

Many thanks

Jon