inserting unicode with RDO (VB)

can I insert unicode text with something like this
Cn.Execute "insert into tabel1(text1, ...) values('" & strText & "',...)
text1 is nvarchar field and strText contains UNICODE text string
when I do this, texts get converted to ANSI, I dont even get characters that exist in my code page but character from ascii 32-127 range that best resembles the one in the string
so is it possible to insert/update unicode fields with RDO( in VB app) without louseing real content in conversion to plain ASCII
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig YellickDatabase ArchitectCommented:
Which version of RDO? RDO did not support unicode encoding until version 2.0 (I think).

Also, try sticking an N out infront of the string, which tells SQL Server that what follows is unicode.

  select 'abc' as 'This is presumed ASCII'
  select N'xyz' as 'This is explicity Unicode'

In your case it would be:

  insert into tabel1(text1, ...) values(N'" & strText & "',...)

-- Craig
BobSamonikAuthor Commented:
2.0 and I did N'' .... doesn't help
Craig YellickDatabase ArchitectCommented:
The N is by itself out in front of the string constant. I mention this because your comment shows it with two apostrophe characters.

It is possible to work with Unicode in VB6 + RDO 2.0. Prior versions of VB and prior versions of RDO could not handle Unicode.

I'm going to guess that you're testing in the full, complex context of your application. If so, you will need to create a too-simple-to-fail test application. It should have the fewest possible lines of VB6 and RDO code that demostrates the problem. There's a good chance that the too-simple version works just fine, and you know the problem is in your larger application code context. If it fails even in the most simple case, THAT is perfect for posting here on EE.

Also, before doing anything, see if you can prove that you can get the update to work using something that requires no programming, like Query Analyzer.  Take a statement comprised of nothing but constants and test it in QA, then use that exact string in VB6+RDO.  That way you know you're not up against a database configuration problem or something outside of your VB6 context.

-- Craig
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BobSamonikAuthor Commented:

SQL = "select unicode_field from table where id_field=153"
Set rs = Cn2.OpenResultset(SQL, rdOpenStatic)
tb = rs("unicode_field") ' displays fine! tb is TextBox control that displays unicode (Forms 2.0)
SQL = "select id_field from table where unicode_field='" & rs("unicode_field") & "'"
tb=sql ' complete sql string displays correctly! But ...
Set rs = Cn2.OpenResultset(SQL, rdOpenStatic)
' ... rs.rowcount is 0!

So, I get Unicode value from database, when I concat it to sql string it still is in unicode but query returns nothing!
It doesn't make any difference whether or not I prefix it with N
I used Profiler to see what statement SQL gets and I see my rs("unicode_field") part in plain ASCII!
Craig YellickDatabase ArchitectCommented:
The problem lies in the way RDO is messing with the string. It's Unicode inside VB6 but not after exiting RDO's OpenResultsets. You need to use a parameter to get the encoding proper. Skip to the final paragraph for the solution. What follows is intended for other readers of this thread who want a quick way to test Unicode access from client apps.

To test, I added a Unicode character (that funky thing that looks like a little pagoda or something) out in front of the Chai product in Northwind.

  update Products set ProductName =  nchar(1769) +' ' + ProductName where ProductID=1

Query Analyzer cannot display the character. You can see the character is stored correctly in SQL Enterprise Manager. Nonetheless, the following returns ID=1 as expected in QA.

  select ProductID from Products where left(ProductName,1) = nchar(1769)

In VB6, so long as TextBox1 is a Forms 2.0 text box, the character is displayed.

    Set rs = con.OpenResultset("select ProductName from Products where ProductID=1")
    TextBox1.Text = rs(0)

And if the query is constructed manually, avoiding the character, it works as expected.

   Set rs = con.OpenResultset( _
       "select ProductID from Products where left(ProductName,1) = nchar(1769)" )

This pretty much narrows it down to the OpenResultset method. Since I rarely concatenate strings together for SQL statements I've never noticed this. Concatenating strings is fine for quick little apps but for long-term production-type apps you are way better off using parameterized queries. Parameters handle embedded quote marks and all of that nonsense, along with Unicode encoding.

  Dim qry As RDO.rdoQuery
  Set qry = con.CreateQuery("test", "select ProductID from Products where ProductName=?")
  qry.rdoParameters(0).Value = s
  Set rs = qry.OpenResultset
  msgbox rs(0) '// Joy! This works.

Sorry about steering you wrong with the N'abc' business. I had no idea that the OpenResultset method hammered on Unicode the way it does, and assumed it was SQL Server not accepting the data as Unicode.

-- Craig

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobSamonikAuthor Commented:
You are the Man! Thanks!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.