Last post Dec 01, 2011 06:18 PM by march11
Nov 30, 2011 08:19 PM|march11|LINK
This sure seems like an encoding problem, but everything I have read states that Access and SQL default to UTF8 unless they are changed and I haven't changed anything.
I am trying to prevent from having to escape apostrophes and other special characters in my string variables by using a parameterized query with an
Here's the code:
Dim pConn As New OleDb.OleDbConnection pConn = New OleDbConnection(cb.ConnectionString) Dim SqlString As String = "INSERT INTO Strings (Mlt_String_ID, Lng_ID, Strg_Name, Sht_Text, Lng_Text, Alt_Text) Values (?, ?, ?, ?, ?, ?)" Using cmd As New OleDbCommand(SqlString, pConn) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@Mlt_String_ID", Mlt_String_ID) cmd.Parameters.AddWithValue("@Lng_ID", Lng_ID) cmd.Parameters.AddWithValue("@Strg_Name", Strg_Name) cmd.Parameters.AddWithValue("@Sht_Text", Sht_Text) cmd.Parameters.AddWithValue("@Lng_Text", Lng_Text) cmd.Parameters.AddWithValue("@Alt_Text", Alt_Text) pConn.Open() cmd.ExecuteNonQuery() pConn.Close() End Using
Updated example: This is directly from the access table after Insert.
String_ID String_Name Long_Text Short_Text Alternate_Text Multi_String_ID Lang 993 U_°C_fr °C °C °C 651 fr 652 U_°C_jp °C °C °C 651 jp 651 U_°C °C °C °C 651 en
jp and en records already existed, the insert of fr record pushes the html
codes for the small circle which represents the degree symbol. Happens for all
other special characters as well.
PS. I realize the redundancy here, this is a migration, the dups fields will be cleared up later.
Nov 30, 2011 08:37 PM|Mikesdotnetting|LINK
I'm not sure what the problem is. The values you are passing in to the parameters must include the html entites that you see when looking at the table. In that case, this is expected behaviour. What were you expecting to see?
Nov 30, 2011 08:39 PM|march11|LINK
The small circle as it exists for the other two records in the example is what I was expecting.
the data is imported to the Web server DB, and viewing the records in the web DB shows the correct symbols.
Exporting the data to Acces though causes the behavior reported in the post.
Dec 01, 2011 04:54 AM|Mikesdotnetting|LINK
How are you exporting to Access? I mean, I can see the code you are using to effect an INSERT, but what is the actual source for the parameter values? Are you reading them directly out of another database? If so what type? Or are you processing them in some
way in an intermediate step? Neither OleDb or Access will convert symbols to their HTML entity counterparts. Neither if those technologies are even aware of HTML, so the conversion must happen elsewhere.
Dec 01, 2011 12:49 PM|march11|LINK
The original data is imported from an Excel Spreadsheet using a strongly typed data adapter to insert into the web portal SSEE database tables. The characters appear as expected in Excel, and when they are loaded into the SQL db.
I am exploring the source of the 'fr' records, I originally believe they too were imported from Excel, but not via the same adapater. I believe it was the SQL Import and Export Wizard that connected to the Excel Spradsheet. Once the Spreadsheet was imported
to a SSEE table, a SQL Query was written to copy the matching values into the appropriate 'fr' table from the imported Spreadsheet table.
The specific Excel spreadsheet is not the same source from 'en' data as it is for 'fr' data, if that matters. Its simply a new workbook that collected the terms.
The Insert process you see in my code is extracting the data from a web SSEE dB, it is populated as explained in the text above.
I am actually exporting to Access from a gridview that is first populated as follows:
I build an itemDataSource that is assigned to the Gridview....
itemDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString()
itemDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
itemDataSource.SelectCommand = "usp_exportLangStrings"
I then run this query to return the records to the gridview....
/* test2 */
SET NOCOUNT ON;
DECLARE @sql varchar(max);
SET @sql = 'SELECT ' + @tableName + '.short_Text, tblMasterStringPTS.string_name, tblMasterStringPTS.alt_text, tblMasterStringPTS.Multi_String_ID, tblMasterStringPTS.Lang_code, tblMasterStringPTS.dbName, tblMasterStringPTS.dbKeyID_ofString, ' + @tableName + '.lang_String'
SET @sql = @sql + ' FROM tblMasterStringPTS INNER JOIN ' + @tableName + ' ON ' + @tableName + '.ID = tblMasterStringPTS.tblROOTptsMaster_ID '
SET @sql = @sql + ' WHERE(((tblMasterStringPTS.dbName) = ''' + @dbName + ''')) AND (tblMasterStringPTS.Lang_code = ''en'')'
SET @sql = @sql + ' ORDER BY tblMasterStringPTS.dbKeyID_ofString;'
SET @sql = Null;
I then run the typical ForEach Row loop to export/Insert the Gridview records into my external Access DB.
For Each row As GridViewRow In GridView1.Rows
Dim Short_Text As String = row.Cells.Item(0).Text ' Short_text
Dim f2 As String = row.Cells.Item(1).Text ' String_Name
Dim Alternate_Text As String = row.Cells.Item(2).Text ' Alt_Text
Dim Multi_String_ID As Integer = row.Cells.Item(3).Text ' Multi_String_ID
Dec 01, 2011 06:04 PM|Mikesdotnetting|LINK
I haven't tested this, but I suspect that the values are being HTMLEncoded when they are displayed (bound) to the GridView. Try using
HtmlDecode() on the values:
For Each row As GridViewRow In GridView1.Rows
Dim Short_Text As String = HtmlDecode(row.Cells.Item(0).Text) ' Short_text
Dim f2 As String = HtmlDecode(row.Cells.Item(1).Text) ' String_Name
Dim Alternate_Text As String = HtmlDecode(row.Cells.Item(2).Text) ' Alt_Text
Dim Multi_String_ID As Integer = HtmlDecode(row.Cells.Item(3).Text) ' Multi_String_ID
Dec 01, 2011 06:11 PM|march11|LINK
I am getting that squiggley blue line that says, Name 'HtmlDecode' is not declared. I also have these imports...
Disregard, found the issue, correct syntax is....
Dim Short_Text As String = HttpUtility.HtmlDecode(row.Cells.Item(0).Text) ' Short_text
Dec 01, 2011 06:18 PM|march11|LINK
Mike I now fully understand why you are the number one ASP ROCKSTAR. I have posted this issue on multiple forums with no solution over the past month. You solved it in less than 24 hours.
Thank you, Thank you, Thank you, Thank you!!!!