Not able to escape apostrophe/special characters in parameterized query RSS

• • Reply
  

  march11

  Contributor

  3017 Points

  1365 Posts

  Not able to escape apostrophe/special characters in parameterized query

  Nov 30, 2011 08:19 PM|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

  OleDbConnection

  . I have all of the parameters in the same order, the insert is working, it just does not maintain the characters. I keep getting those pesky html codes. Any help would be appreciated. I am trying to insert into an Access db.

  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.

  • Edited by march11 on Nov 30, 2011 08:21 PM
  • Edited by march11 on Nov 30, 2011 08:21 PM
  • Moved by limno on Nov 30, 2011 08:32 PM
  • Edited by march11 on Nov 30, 2011 08:37 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Not able to escape apostrophe/special characters in parameterized query

  Nov 30, 2011 08:37 PM|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?

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  march11

  Contributor

  3017 Points

  1365 Posts

  Re: Not able to escape apostrophe/special characters in parameterized query

  Nov 30, 2011 08:39 PM|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.

   

  • Edited by march11 on Nov 30, 2011 08:39 PM
  • Edited by march11 on Nov 30, 2011 08:47 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Not able to escape apostrophe/special characters in parameterized query

  Dec 01, 2011 04:54 AM|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.

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
• • Reply
  

  march11

  Contributor

  3017 Points

  1365 Posts

  Re: Not able to escape apostrophe/special characters in parameterized query

  Dec 01, 2011 12:49 PM|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.SelectParameters.Clear()
  
  itemDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString()
  
  itemDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
  itemDataSource.SelectCommand = "usp_exportLangStrings"
  
  itemDataSource.SelectParameters.Add("dbName", dbName)
  itemDataSource.SelectParameters.Add("tableName", tableName)

   

  I then run this query to return the records to the gridview....

  @tableName varchar(50),
  @dbName varchar(50)
  	
  /* test2	*/ 
  AS  
  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;'
  		 
  EXECUTE (@sql);   
  SET @sql = Null;   
  RETURN

   

   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
  
  etc, etc
  

   

   

  • Edited by march11 on Dec 01, 2011 04:16 PM
  • Edited by march11 on Dec 01, 2011 04:28 PM
  • Edited by march11 on Dec 01, 2011 04:28 PM
  • Edited by march11 on Dec 01, 2011 04:29 PM
  • Edited by march11 on Dec 01, 2011 04:31 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  154927 Points

  19867 Posts

  Moderator

  MVP

  Re: Not able to escape apostrophe/special characters in parameterized query

  Dec 01, 2011 06:04 PM|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
  
  etc, etc

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Marked as answer by march11 on Dec 01, 2011 06:18 PM
• • Reply
  

  march11

  Contributor

  3017 Points

  1365 Posts

  Re: Not able to escape apostrophe/special characters in parameterized query

  Dec 01, 2011 06:11 PM|LINK

  I am getting that squiggley blue line that says, Name 'HtmlDecode' is not declared.  I also have these imports...

  Imports System

  Imports System.Web

  Imports System.IO

  
  Disregard, found the issue, correct syntax is....

  Dim Short_Text As String = HttpUtility.HtmlDecode(row.Cells.Item(0).Text)      ' Short_text

  • Edited by march11 on Dec 01, 2011 06:11 PM
  • Edited by march11 on Dec 01, 2011 06:12 PM
  • Marked as answer by march11 on Dec 01, 2011 06:18 PM
• • Reply
  

  march11

  Contributor

  3017 Points

  1365 Posts

  Re: Not able to escape apostrophe/special characters in parameterized query

  Dec 01, 2011 06:18 PM|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!!!!

  • Edited by march11 on Dec 01, 2011 06:21 PM