Capture and insert Unicode text (Cyrillic) into MS access database
I inherited an old web application, that is writing data collected in a form into an Access 2007 database using classic ASP.
Now they need it to be able to collect input in the Cyrillic alphabet.
I'm completely unfamiliar with code page/ char sets, and working with non latin alphabets.
I've tried altering the charset on the entry form page to ISO-8859-1 , which does seem to store the ascii value of the characters (eg: #1076;). So that is interpreted and read by the browser fine, but is pretty much useless in terms of then exporting that data into excel to pass around to the departments that need it.
So my question is:
Is there an easy way to capture Cyrillic characters from the web form and insert them as Cyrillic characters into my access table?
or alternately
Is there a tool or setting within the access database that can convert the decimal values (#1076;) into Cyrillic characters within access itself.
Answer
If you stick with UTF-8 for your pages they should work (but see the Important Note below). While it is true that Access does not store Unicode characters internally as UTF-8 the Access OLEDB driver will take care of the conversions for you.
Consider the following sample script (where 65001 is the "code page" for UTF-8):
<%@ CODEPAGE = 65001 %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<title>Classic ASP Unicode Test</title>
</head>
<body bgcolor="white" text="black">
<%
Dim con, cmd, rst
Const adVarWChar = 202
Const adParamInput = 1
Set con = CreateObject("ADODB.Connection")
con.Mode = 3 ' adModeReadWrite
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\_wwwdata\unicodeTest.mdb;"
If Len(Trim(Request.Form("word"))) > 0 Then
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO vocabulary (word, language, english_equiv) VALUES (?,?,?)"
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("word"))
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("language"))
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("english_equiv"))
cmd.Execute
Set cmd = Nothing
End If
%>
<h2>Word list:</h2>
<table border=1>
<tr>
<th>word</th><th>language</th><th>english_equiv</th>
</tr>
<%
Set rst = CreateObject("ADODB.Recordset")
rst.Open _
"SELECT * FROM vocabulary ORDER BY ID", _
con, 3, 3
Do Until rst.EOF
Response.Write "<tr>"
Response.Write "<td>" & rst("word").Value & "</td>"
Response.Write "<td>" & rst("language").Value & "</td>"
Response.Write "<td>" & rst("english_equiv").Value & "</td>"
Response.Write "</tr>"
rst.MoveNext
Loop
Response.Write "</table>"
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
%>
<h2>Add a new entry:</h2>
<form action="<% Response.Write Request.ServerVariables("SCRIPT_NAME") %>" method="POST">
<table>
<tr>
<td align="right">word:</td>
<td><input type="text" name="word"></td>
</tr>
<tr>
<td align="right">language:</td>
<td><input type="text" name="language"></td>
</tr>
<tr>
<td align="right">english_equiv:</td>
<td><input type="text" name="english_equiv"></td>
</tr>
<tr>
<td></td>
<td align="center"><input type="submit" value="Submit"></td>
</tr>
</table>
</body>
</html>
Starting with a table named [vocabulary] in the Access database
when we load the ASP page we see
If we add a new entry for a Russian word
and click "Submit" the page will refresh with
and if we check the table in Access we see
Important Note
Be aware that you should NOT be using an Access database as a back-end data store for a web application; Microsoft strongly recommends against doing so (ref: here).