MS Forms 2 Textbox and Unicode data 1

markros · Dec 26, 2011

In the closed thread184-1624353 I found a reference to MS Forms 2 Textbox control. By specifying the ControlSource property of this control, I can display data (which are varbinary) just fine. My question is how to save the input data?

Thanks.

PluralSight Learning Library

Mike Lewis · Dec 26, 2011

There's no automatic way of saving the data, as there is with native VFP controls.

At the point at which you do the save (typically, in the Save button of your form), you need to do a REPLACE or UPDATE into the underlying table.

Let us know if you need any more details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

markros · Dec 27, 2011

I clearly need more details as how to save the data with this particular control. As I tried the whole day yesterday and nothing I tried worked so far. This control perfectly displays unicode data, but refuses to save them properly.

I am about to abandon this control, but what are the alternatives then (and if possible, with some code as I don't want to spend few more days in vain).

PluralSight Learning Library

IlyaRabyy · Dec 27, 2011

Then, colleague MarkRos (is it how it should be interpreted?

), it would be easier to help you if you provided some more details. For instance: are you saving your text into a field in a table or into a text file on disk? If it's the former - of what data type this field is? If it's the latter - what text format you are trying to have in this file?

The above notwitrhstanding, have you tried to save it as XML and/or in a Memo type field?

Regards,

Ilya

markros · Dec 27, 2011

Sorry, looks like I forgot to provide more details here. I am saving the data in the nvarchar(100) field in SQL Server database. I can retrieve this field in VFP by SQL pass-thru using this code

select cast(MyNVarcharField as varbinary(100)) as MyField from mySQLTable

I then can do either

MSForms2TextBox.Text = MyField

or

MSForms2TextBox.ControlSource = 'myAlias.myField'

and both correctly display the data. I can then copy text from, say, Word, into this textbox and it is displayed correctly again.

However, I can not figure out how to save this text back to SQL Server nvarchar field.

Attempts to do

replace myField with MSForm2TextBox.Text

or apply strconv(MSForms2TextBox.Text,11) don't help.

---------------------
I will tremendously appreciate if someone can repeat the exact same steps I am trying and provide a solution, of, if there is no solution using this particular control, suggest a different ActiveX with some proven code I can plug in my form easily.

A bit more info - I found this thread at foxite

http://www.foxite.com/archives/how-to-implement-unicode-in-vfp-0000209431.htm

but unfortunately again it only talks about displaying the data, not saving. May be there is some way to contact this guy and find out if he had success with saving the data?

PluralSight Learning Library

Mike Lewis · Dec 27, 2011

Markros,

Well, it would have helped if you had mentioned at the outset that you are using a SQL Server back end.

You said you are using SQL pass-through. In that case, you need to do something like this:

Code:

lcCmd = "UDATE TheTable SET TheField = " + ;
  ALLTRIM(THISFORM.MyTextBox.Text) + ; 
  " WHERE ID = " + TRANSFORM(IDValue)
SQLEXEC(lnConnection, lcCmd)
[code]

You would do the above at whatever point you want to save the data.

(I hope it's obvious that you have to supply the values for TheTable, etc. yourself. I'm just guessing at your table name, field name, and so on.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

[url=http://www.ml-consult.co.uk]Visual FoxPro articles, tips, training, consultancy[/url]

markros · Dec 27, 2011

Mike,

Before making this suggestion, have you at least tried it with this particular control?

The ALLTRIM function is not going to work properly with the UNICODE data not to mention that in your code sample you're missing the single quotes around the text.

In any case, the MS Forms2 Textbox control is not the same as the regular textbox control where I would not have trouble saving the data.

As I said, I need to save UNICODE data that I can type into this particular ActiveX. If you can offer help for the question I am asking, then please offer it.

Thanks again.

PluralSight Learning Library

Mike Lewis · Dec 27, 2011

Markros,

The code I posted was just to give you the general idea. You're right that I missed the delimiters, but my aim wasn't to give you code that you could copy exactly. I only wanted to get across the point about using SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

JRB-Bldr · Dec 27, 2011

Before making this suggestion, have you at least tried it with this particular control?

I hope that you are not suggesting that any time any of us offers you suggestions or advice we would need to set up a complete duplicate of your environment and test each suggestion before posting.

We are here to Help you, not to write your code for you.

Often we are not given enough detail about the issue/problem to get the EXACT issue resolved (like not knowing about a SQL Server backend).
When details are missing, our guesses and assumptions might not be correct.

And, admittedly, yes we sometimes make mistakes.

But we are offering you an opportunity to learn more by trying our suggestions yourself.
Then after doing so, you can tell us if we have resolved your issue or not.
And, if not, you supply the specific details so that we can adjust our advice.

Good Luck,
JRB-Bldr

markros · Dec 27, 2011

I DO know how to update fields using sql pass thru. This is not the point of my post. I asked Help for the particular ActiveX control with a complex problem of saving UNICODE data. I don't need guesses which will not lead me to solution. Of course I already tried all possible things I can think of. The main problem here that I can not use text property and assign it to VFP variable. As soon as I try this, the data are lost.

So, once again, if you know the answer to the exact question I am asking, then please speak. Otherwise don't send me to the goose chase.

PluralSight Learning Library

markros · Dec 27, 2011

I thought I will be able to create a simple reproducible test, but it turned out to be not simple as well. In other words, I can get nvarchar converted to varbinary field from SQL Server. However, if I try to copy the value I see in that field directly into a new cursor defined as create cursor csrTest (cVal varbinary(100), nVal varbinary(100))
insert into csrTest (cVal) values ('20044304410441043A0438043904')

I don't see the same word in MS Forms 2 Textbox.

PluralSight Learning Library

Dan Freeman · Dec 27, 2011

makros said:
So, once again, if you know the answer to the exact question I am asking, then please speak. Otherwise don't send me to the goose chase.

With this attitude on display I doubt you'll be troubled with people trying to help you.

Your initial question was unclear, and it wasn't until your third post that you even mentioned Unicode. Visual Foxpro does not officially natively support Unicode.

There are workarounds limited only by the creativity of the practitioner, but I doubt anyone will be willing to put their oar in given your telling everyone to stuff it.

markros · Dec 27, 2011

The thread looked like it was deleted and now it re-appeared.

I understand I appeared as I didn't appreciate the advices given, I can only say in my defense that I was tired and that I spent now two days without any progress so far.

I found thread on foxite

http://www.foxite.com/archives/how-to-implement-unicode-in-vfp-0000209431.htm

and what Mike is telling
is not working for me

I downloaded sample from

http://www.west-wind.com/presentations/foxunicode/foxunicode.asp

and tried running as is (with the change of the DB) and even this didn't exactly worked (e.g. my result was not the same as the article demonstrated).

So, I do really appreciate if someone can walk me through from point A of having data in SQL Server as nvarchar to point C of saving this data from VFP form.

------------
I tried sql pass thru as this is what we're using in our application. I think tomorrow I will be trying ADO recordset if I will not get better answers...

PluralSight Learning Library

Olaf Doschke · Dec 29, 2011

Markros,

I just downloaded the sample code and ran it without success, as I am missing the WestwindAdmin database. This is not meant as a standalone sample code. Read more on the whitepaper itself, you'll find all the essential information in it:

1. explaination why you get back ??? from the Text property:

Rick Strahl said:
It’s very likely that if you convert Unicode characters from a non-default code page, you will not be able to convert these strings to a useful Visual FoxPro string in the VFP environment. If your machine is configured for code page 1252, and you read a Korean Unicode string and convert it to a FoxPro string with STRCONV() you will find that you get a string full of ???

2. explaination why you get back ??? from the Text property:

COM Objects
COM objects always accept and represent strings in Unicode format. So every time VFP calls a COM object a conversion happens behind the scenes converting VFP strings to and from Unicode. By default it uses the current locale settings for the conversion, so if data from an extended locale comes back VFP will have problems presenting that locale’s data in a VFP string.

To work around this issue Visual FoxPro 9.0 introduces a new SYS(3101) function that tells VFP to return the COM data to you in UTF-8 format (or any specific locale you specify):

SYS(3101,65001)

This tells VFP to convert all data to and from UTF-8. If you are retrieving extended character strings it makes sense to pull the data out in UTF-8 format and then perform any additional conversions to Unicode when you need to store the data to the database. As you’ll see a little later this option is very useful when dealing with data passed through ADO, because ADO is an all-COM interface to the data.

So if you would have spent your days in reading the article as is instead of simply trying and retrying without any success, you already would know the essential Sys(3101) as first part of the solution.

3. Why SQL does not provide unicode to Foxpro

Additional to the COM<->VFP Unicode <-> ANSI conversion there is a SQL Server <-> VFP Unicode <-> ANSI conversion, Rick also mentions:

Rick Strahl said:
Because VFP doesn’t provide a native Unicode type, SPT is also somewhat limited in what you can do with Unicode data retrieved from SQL Server. The problem is that while you can query data from SQL Server in Unicode fields, ODBC will try to convert that data into the default code page running on the machine. For example if I run the following code:

lnHandle =SQLStringCONNECT(;
"driver={sql Server};server=(local);"+;
"database=WestWindAdmin")
? SQLEXEC(lnHandle,"select Id,descript as Note," + ;
"CAST(lDescript as nVarChar(120)) as Description"+;
"from foreignData")
BROWSE

it retrieves the data that is displayed in Web page from Figure 2. The unacceptable Browse result is shown in Figure 4.

See Rick Strahl's whitepaper for Figure 2 and 4 mentioning the problem.

Rick also posts a solution you already know and use right beneath Figure 4:

Rick Strahl said:
So retrieving the data as string values into VFP is not possible from SQL Server any more than it was with VFP earlier. But as with the VFP data we can also return the data as binary:

SqlExec(lnHandle,[select ID,] +;
[CAST(CAST(Descript as nVarChar(4000)) as VarBinary(8000)) as Note,] +;
[CAST(CAST(lDescript as nVarChar(4000)) as VarBinary(8000)) as Description] +;
[from ForeignData],"TFData")

When you run this SQL Statement you’ll get back a cursor with two binary memo fields and these memo fields will contain binary Unicode strings.

This is one part of the solution, but you also need SYS(3101) and writing back to SQL Server is solved by him with...

Rick Strahl said:
To write the data back we need to take our UTF-8 input, convert it to binary and then cast it in SQL Server back to nVarChar. Note, that you can’t cast to nText – SQL Server doesn’t allow casting to nText or Image data. The abbreviated process looks like this:

Code:

*** UTF-8 to Unicode conversion pcSavedDescription = STRCONV(pcSavedDescription,12) pcSavedTitle = STRCONV(pcSavedTitle,12) *** Must explicitly force to binary – can also use CAST in 9.0 pcSavedTitle = CREATEBINARY(pcSavedTitle) pcSavedDescription = CREATEBINARY(pcSavedDescription) *** SQL Server requires CASTS on the server! SqlExec([insert into ForeignData (ID,Descript,lDescript) values ] +; [(?pcID, CAST(?pcSavedTitle as nVarChar(4000)),] +; [CAST(?pcSavedDescription as nVarChar(4000) ) ) ] )

This is a fairly complicated mechanism as you really have to understand the underlying mechanics of this process to make this work.

So admitted, as Rick Strahl says this is complex, the solution has not just one but at least 5 essential parts now:

SQL Server to VFP: CAST as varbinary
COM to VFP: SYS(3101)
UTF-8 to Unicode: Strconv()
convert back to binary: CREATEBINARY()
binary to unicode within SQL Server: Cast again

Well, still thoroughly reading this whitepaper gives you the solution, doesn't it?

So Again: Did you really read this article?

There's one more thing, that makes some parts of this forth and back conversions unneccessary: SYS(987). Rick mentions it here:

http://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=608

Rick said:
This is a huge improvement as it makes Unicode data at least reasonably approachable with FoxPro code. I’ve struggled through STRCONV() madness before with several apps

So this stops the autoconversion from SQL Server to Foxpro and back like SYS(3101) prevents the autoconverion from unicode to ANSI on the way from Foxpro to ActiveX and back.

Now you do have only 2 essential parts:
1. SYS(3101,65001) will turn anything you read from ActiveX to Foxpro to UTF-8
2. SYS(987,.F.) prevents Unicode to Ansi conversion via SQLExec().

Besides all this: What do you think your code does?

Code:

create cursor csrTest (cVal varbinary(100), nVal varbinary(100))
insert into csrTest (cVal) values ('20044304410441043A0438043904')

If you want to write a varbinary value for the insert into, why do you write a normal string instead? The prefix for it is 0h, it's not delimited with string delimiters:

0h20044304410441043A0438043904

This stored into the .text property yields "Русский"

otherwise, well, you most probably get a display of 20044304410441043A0438043904, what else do you expect?

Bye, Olaf.

JRB-Bldr · Dec 29, 2011

Olaf - as you told me not too long ago in another forum...

Wow - That's Service!!

JRB-Bldr

Mike Lewis · Dec 30, 2011

Wow - That's Service!!

I second the motion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

markros · Jan 1, 2012

I've tried at that time lots of things from Rick's article, but it was not exactly working. Rick's sample (downloaded and changed to use my test database) didn't work as well.

However, I was finally able to get the working solution with Gregory Adam's help and documented it in the blog post.

PluralSight Learning Library

markros · Jan 1, 2012

I did read this article, but this part from Rick's article

Code:

*** UTF-8 to Unicode conversion
pcSavedDescription =  STRCONV(pcSavedDescription,12)
pcSavedTitle = STRCONV(pcSavedTitle,12)
 
*** Must explicitly force to binary – can also use CAST in 9.0
pcSavedTitle = CREATEBINARY(pcSavedTitle)
pcSavedDescription = CREATEBINARY(pcSavedDescription)
 
*** SQL Server requires CASTS on the server!
SqlExec([insert into ForeignData (ID,Descript,lDescript) values ] +;
       [(?pcID, CAST(?pcSavedTitle as nVarChar(4000)),] +;
  [CAST(?pcSavedDescription as nVarChar(4000) )  ) ] )

Was not working for me. I did also try sys(3101) in addition to that code.

PluralSight Learning Library

Olaf Doschke · Jan 1, 2012

Did you specifically try SYS(3101,65001)? Because that is getting UTF-8 from the ActiveX textbox.Text property.

Bye, Olaf.

Olaf Doschke · Jan 1, 2012

Also: Do you set COMPROP()? This overrides SYS(3101) behaviour.

For example COMPROP(this,'UTF8',1) in the init of an activeX control is not good for handling unicode, it will convert UTF-8 to the active codepage and that is NOT what you want, this is only working for one certain codepage, so it could support one language, but not many.

Bye, Olaf.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

MS Forms 2 Textbox and Unicode data 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor