Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Express Edition and Compact Edition (2005)  SQL CE 3.1: storing punctuated text values

Author  Topic  SFalken Starting Member 1 Post Posted - 2008-08-05 : 14:58:37 Using VB.Net 2005 (Framework 2) and SQL CE 3.1. We have 3 textboxes with a max length of 3000. These textboxes are meant to hold descriptions of work sites. However, we're running into problems when trying to store punctuation when the user enters it. We currently have a function that prevents the entry of 'illegal' characters, but since it only allows letters and numerals, it's too restrictive. It gets called from the Validating event of the textboxes in question. Here it is: 'BEGIN CODE Function IsValidDescr(ByVal strAlphaNum As String, ByVal intLen As Integer) As Boolean ' Allows one or more alphabetical and/or numeric characters up to intLen in length. Dim sb As New StringBuilder sb.Append("^[a-zA-Z0-9-_\s]{1,") sb.Append(intLen) sb.Append("}$") Dim regExPattern As String = sb.ToString Return MatchString(strAlphaNum, regExPattern) End Function 'END CODE We wanted to relax the restrictions, and the best way we knew to do so was to store the incoming string value in a SQL CE parameter, and add that to the UPDATE query. This way, we would not have to escape each and every weird punctuation mark in the text. However, we discovered that there's a bug in the SQLCE implementation that does not allow explicit setting of an NVarChar length of greater than 255 when assigning a parameter value. So, following the advice found on the Net as a workaround, we removed the length property and declared the SQLCEParameter without it. But, we began receiving a seemingly unrelated error stating the query exceeded the SQL page size limit of 8k. We received this error whenever puntuation characters we allowed to be in the value assigned to the parameter. So, we decided to do away with the parameters altogether, and restrict the textbox characters to the point that we could simply add the text value to the UPDATE query string. We did this as an expedient, and figured we'd table the issue until one of our clients complained. They did so today, only 3 days after release... so here we are - asking how to fix this issue. What would be ideal would be to use the parameterized query with the correct regular expresssion to allow as many puntuation marks as possible. But we have yet to find a bug-free way to do so. Thanks for any help we can get.