Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

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
0
BobSamonik
Asked:
BobSamonik
  • 3
  • 3
1 Solution
 
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
0
 
BobSamonikAuthor Commented:
2.0 and I did N'' .... doesn't help
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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!
0
 
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
0
 
BobSamonikAuthor Commented:
You are the Man! Thanks!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now