inserting unicode with RDO (VB)

Posted on 2006-04-08
Last Modified: 2008-02-01
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
Question by:BobSamonik
    LVL 11

    Expert Comment

    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

    Author Comment

    2.0 and I did N'' .... doesn't help
    LVL 11

    Expert Comment

    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

    Author Comment


    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!
    LVL 11

    Accepted Solution

    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

    Author Comment

    You are the Man! Thanks!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now