[RESOLVED] MySql or Vb6 issue

[S0LARIS]

Hello Dear VbForums Team!
Please help me to solve this issue.

I am using Mysql and ADO recordset to connect to MySql.

I am creating customizable search engine in vb6 and during my query modification now as result I receive "???????" values in some columns.
However when I tried same query in php everything worked correct and I was able to see correct field values.

Please advice,why I see ?????? values instead of real values ?

Here is my query:

set @match = '^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$'

After next part of query:

Select if(tblclientmaster.ClientId regexp @match,if(Count(Distinct tblclientmaster.ClientId)=1,tblclientmaster.ClientId,SUM(tblclientmaster.ClientId)),if(Count(tblclie ntmaster.ClientId)=1,tblclientmaster.ClientId,if(Count(Distinct tblclientmaster.ClientId)=1,tblclientmaster.ClientId,Concat(Count(tblclientmaster.ClientId),"" (count)"")))),if(tblclientmaster.ClientName regexp @match,if(Count(Distinct tblclientmaster.ClientName)=1,tblclientmaster.ClientName,SUM(tblclientmaster.ClientName)),if(Count(t blclientmaster.ClientName)=1,tblclientmaster.ClientName,if(Count(Distinct tblclientmaster.ClientName)=1,tblclientmaster.ClientName,Concat(Count(tblclientmaster.ClientName),"" (count)"")))) from tblclientmaster Group by tblclientmaster.ClientType


In Php everything is ok in vb6 I get "?????" in all ClientId fields and blank field values in ClientName field,however on php everything is showing normally.

Kind Regards,
S0LARIS.

[S0LARIS]

Dear Moderators I think thread must be moved into Database Development subforum, sorry for posting here,just first time here

[si_the_geek]

Welcome to VBForums

The Database Development forum might be better, but we can't tell yet - because there is a good chance that the database side of things isn't really relevant to the issue.

To be able to tell what the actual issue is, it would help to see your code for loading/displaying the data, and tell us what control(s) you are using to display it, and also what data types the database fields are (especially any that might contain unicode text).

[Mehmood Iqbal]

I think, you are using some Unicode characters to save into the Database that can't be supported. Show your database file version & part of the code to be understand that what is going on.

[S0LARIS]

Hello,thanks for reply!

I am using MSFLexGrid and String type array.

Here I am attaching Loading part of my code:

Private ADOConnection As New adodb.Connection
Private rs As Recordset
Private SearchedRows() As String 'here will keep all searched rows


'In my example MegaQuery is
MegaQuery = "Select if(tblclientmaster.ClientId regexp @match,if(Count(Distinct tblclientmaster.ClientId)=1,tblclientmaster.ClientId,SUM(tblclientmaster.ClientId)),if(Count(tblclie ntmaster.ClientId)=1,tblclientmaster.ClientId,if(Count(Distinct tblclientmaster.ClientId)=1,tblclientmaster.ClientId,Concat(Count(tblclientmaster.ClientId),"" (count)"")))),if(tblclientmaster.ClientName regexp @match,if(Count(Distinct tblclientmaster.ClientName)=1,tblclientmaster.ClientName,SUM(tblclientmaster.ClientName)),if(Count(t blclientmaster.ClientName)=1,tblclientmaster.ClientName,if(Count(Distinct tblclientmaster.ClientName)=1,tblclientmaster.ClientName,Concat(Count(tblclientmaster.ClientName),"" (count)"")))) from tblclientmaster Group by tblclientmaster.ClientType"
    
rs.Open MegaQuery, ADOConnection, adOpenKeyset, adLockPessimistic

 If rs.BOF = False Then
   Do While Not rs.eof
             Dim AddLine As String
             AddLine = ""
             For i = 0 To rs.Fields.COunt - 1
                
                AddLine = AddLine & rs.Fields(i) & Chr(9)
                
             Next i
                
                 k = k + 1
                ReDim Preserve SearchedRows(k)
                SearchedRows(k) = AddLine
                
                rs.MoveNext
    Loop
 End if

So I am actually populating array with searched values and only after it populating MSFlexGrid.
I also noticed that during population of array it is already '???????' so problem not in MsFlexGrid.

ClientId field is numeric type and ClientName is varchar type .

Some explanation of query:
With "tblclientmaster.ClientId regexp @match" line, I am 'knowing' if field is numeric type or is varchar type and performing SUM or count operation,depending on field type.

Just the fields are always changing and I can't exactly know what field has what type,so that's why I am using that query.

But how explain it that it is working normally on php ?

Also no Unicode charecters in Clientnames.

Another thing I wolud like to mention:
Before adding Concat function to my query everything was working correctly.

So after adding

Concat(Count(tblclientmaster.ClientId),"" (count)"")

to my Query,it started showing "???????" and blank values.

Thanks,
S0LARIS.

[si_the_geek]

I suspect Concat is converting to unicode, but don't use MySQL so can't be sure.

Try displaying the text in controls that support unicode, such as the ones here:
http://www.vbforums.com/showthread.php?t=500026

[Mehmood Iqbal]

Concat(Count(tblclientmaster.ClientId),"" (count)"")

What you are doing with " Count " ? I think, 'count' must be as 'Constant' but you are going to make it variable (more values assigned). Unicode characters may also an issue.

[S0LARIS]

2 si_the_geek
Is there any way to get values not in unicode format ?
Also I not found any Unicode supporting Grid control.
(but I am still not sure that concat is converting to unicode,read tons of documentation about Concat,yet no line about it that it is converting into unicode)

2 Mehmood Iqbal
I just want to show returned rows this way:

2 (count)
3 (count)
...
and so on...

So "(count)" is just a string that must be added to returned rows for later showing in FlexGrid.
It is not a constant.

Too Strange on my database I haven't any Unicode characters....

[si_the_geek]

Don't try to solve everything immediately - start by finding out what the issue is.

The suggestions I have made are to simply check whether the values are unicode or not (by showing one value in a unicode compatible control). If they aren't, there is no point trying to get hold of a unicode grid control, or find a way of converting from unicode, etc.

[Ram2Curious]

May be its a bug, but i am not sure. It could be caused due to a combination of COUNT, DISTINCT and CONCAT. The MySql site page here describes a case where it did seem to lock the server (Windows platform ) .

[S0LARIS]

2 si_the_geek
I tried UniText control ,instead of '??????',it is now showing 2 Chinese characters.

Also here more simple query,which again shows '????' values....

Query = Select concat(count(tblclientmaster.ClientId),"" (count)""),tblclientmaster.ClientName from tblclientmaster where tblclientmaster.ClientOrigin='Event' group by tblclientmaster.ClientName

ClientName is varchar type.
ClientId is numeric

2 Ram2Curious
Under php query work's normally, so I suppose that it is not a bug.

[si_the_geek]

It seems you are getting unicode then... and it does seem that the mixture of Concat and Count is the issue.

What I would recommend is converting the Count to non-unicode text, which might be like this:

concat(  cast( count(tblclientmaster.ClientId) as varchar(50) ),""

[S0LARIS]

Oh I think I found a solution
Concat can concentrate varchar types only,so it was required firstly to convert

count(tblclientmaster.ClientId)

into char and then use in Concat.

So right query is:

Query = Select concat(Cast(count(tblclientmaster.ClientId) AS CHAR),"" (count)""),tblclientmaster.ClientName from tblclientmaster where tblclientmaster.ClientOrigin='Event' group by tblclientmaster.ClientName

Thanks everybody for help

[Ram2Curious]

Additionally, this information will guide you with what si has explained (about type cast).