Language problems in SQL string (VB6) 1

[Raffles666]

I have a cunning little problem with my SQL string.

There are a couple of people using my program in Germany - with the German language etc set on their PC's (as you'd expect).

Trouble is - when they run my program they encounter a little error that took me a while to find.

If I run the exact same program on my UK (English) PC - I don't see the error.

The error turned out to be the way that VB6 translated my variables.

It keps translating my True/False keywords in to Wahr/Falsch (I'm using using CBool btw). This makes my SQL string fail. As a temporary fix I've done a 'Replace' (which works for them).

Is there a way to make VB6 and the Data Designer ignore locales and just use English - or will I have to write translators for every country that wants to use my program?

Thanks in advance...



Old school Alchemy engineer - trying to keep up with the times

 

[SkipVought]

Hi,

You should NEVER use TRUE and FALSE in an expression, like,

if bCompleted = True Then...   'VERBOTEN!!!

What sense to be saying if True = True or if False = True

Rather

if bCompleted Then...

Skip,
[sub]
[red][/red]
[/sub]

 

[Raffles666]

Errr - I'm not. I am using "tmpVariable = CBool(chkButton.Value)" to make tmpVariable either True or False.

The problem is when I convert tmpVariable to a string eg.

tmpSQL = "SELECT * FROM tmpTable WHERE [Section] = " & tmpVariable & ";"

This gives 'SELECT * FROM tmpTable WHERE [Section] = True;" in English (which works fine)
but gives 'SELECT * FROM tmpTable WHERE [Section] = Wahr;" in German (which fails when passed to the database).

Hope that explains it better.

Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[SkipVought]

What values does [Section] contain?

Skip,
[sub]
[red][/red]
[/sub]

 

[Raffles666]

[Section] is a database field with type set to Yes/No (Access' version of True/False).

So, it's either True or False.

Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[SkipVought]

Perhaps you need to write an IIF statement to accomodate both languages.

Skip,
[sub]
[red][/red]
[/sub]

 

[SBerthold]

Use

= " & CInt(tmpVariable) & ";"

or

= " & Abs(CInt(tmpVariable)) & ";

 

[SBerthold]

Oh, if you are using a CheckBox, and it is either vbChecked (1) or vbUnchecked (0), then just pass that value to it.

 

[Raffles666]

Now I'm confused. I tried using the 'vbChecked/vbUnchecked' value - but that returned either a 1 or a 0.

The mdb kicked that out as it demanded True or False (yes - I know they equate to the same - but Access is stubborn).

This was the reason I used CBool (which converts 1 or 0 to True or False). That isn't the problem. The problem is VB6 deciding to change the True/False keywords to German and srewing up a perfectly good SQL statement.

Looks like I'm stuck with translators for the time being.

Oh well...

Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[SBerthold]

I didn't see where you were using an Mdb. Therefore the two examples.
Jet mdb uses a -1 as TRUE.
So, try:

chkButton.Value * -1

Or, use Jet's own VBA CBool function:

WHERE [Section] = CBool(" & chkButton.Value & ");"
or
WHERE [Section] = CBool(" & CInt(tmpVariable) & ");"

where CBool() is embedded in the sql statement
All three should work.

 

[Raffles666]

Sorry - I meant -1 = True.

I'll try using CBool within the string (good idea - thanks).



Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[Raffles666]

Oops - I can't use CBool within the SQL. It turns out as "...WHERE [Section] = CBool(0);"

Back to my draeing board .

Thanks anyway.

Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[Raffles666]

Ignore that - it works .

Maybe I should try things properly next time.



Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[gmmastros]

What is the data type for the section column? In query analyzer, run this and post the results.

Select Data_Type
From   Information_Schema.columns
Where  Table_Name = '[!]YourTableName[/!]'
       and Column_Name = 'section'

If the data type is BIT, then SQL Server uses 0 for false and 1 for true.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Raffles666]

Thanks for that - but it now works (cheers SBerthold) as it should.



Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?

 

[jarellxede]

Do you have all of your VB6 service packs installed? You may want to check out this article...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;216379

 

[SBerthold]

I think you have missed the point of that SP correction, and what was actually corrected, or have missed the relation between the problem in this thread (there IS no real error, only a mis-understanding) and the correction actually made in that Kb thread. Probably the latter.

 

[SBerthold]

I guess the Kb article was a little misleading in their suggested resoulution:

sTemp = "-" & CStr(True) ' CStr keeps the English word True.

This isn't, and wasn't quite correct. It doesn't keep the English word.


bTemp = CBool(sTemp)

didn't work, because sTemp was still in the local. If that local was German, then sTemp = "Wahr"

After the service Pack, the correction was made to boolean conversions.

For two examples, on a german system:
sTemp= "True" and sTemp = "Wahr"

After applying the SP, bTemp = CBool(sTemp) works, for both of the examples.

But on that same german system, even though CBool(sTemp) no longer causes an error, it still returns "Wahr".
This literal is being passed to the Sql Statement, which Jet doesn't understand.
And that is the problem.

 

[Raffles666]

Exactly .

I corrected all my statements to use either CBool(0) or CBool(1) [Jet versions!] in the SQL statement itself - and not rely on VB - now all works fine.

Thanks again

Oh, and yes - I have all the SP's installed (up to SP6).

Old school Alchemy engineer - trying to keep up with the times

http://www.DataVox.co.uk

"What do you mean - it wasn't broke until I fixed it?