I'm trying to store the German ß in SQL Server, but it results in ÃŸ. For example "Gleimstraße" is stored as "GleimstraÃŸe"
But this also happens for other characters:
- "König-Karl-Straße" stored as "KÃ¶nig-Karl-StraÃŸe"
- "Quai André-Citroën" stored as "Quai AndrÃ© CitroÃ«n"
- "Carrer dels Adreçadors stored as "Carrer dels AdreÃ§adors"
I looked at the database collation and it shows
SQL_Latin1_General_CP1_CI_AS. I then Googled and found this.
But I have no idea if my existing collation is causing the issue, or what I need to do to be able to store the special characters like ö, ß, é, ë, ç, ñ, í.
Indeed, I'm storing the strings in an
nvarchar column. I send the data to the database via my .NET application. This is what I have in my web.config as the connection string:
<add name="conn" connectionString="data source=(local)\sql;Initial Catalog=mydb;User Id=myuser;Password=mypassword;" providerName="System.Data.SqlClient"/>
I read values from a JSON file which I've passed into a
Newtonsoft.Json.Linq.JArray and then pass it to a function, like below:
InsertStreetId(item.SelectToken("location.street").Value(Of String)) Public Shared Function InsertStreetId(ByVal street As String) As Integer Dim streetId As Integer Dim myConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("INSERT INTO geo_streets(streetname) VALUES (@streetname)" + ";SELECT CAST(scope_identity() as int);", myConnection) cmd.Parameters.Add(New SqlParameter("@streetname", street)) Try myConnection.Open() streetId = CInt(cmd.ExecuteScalar) Catch ex As Exception Finally myConnection.Close() End Try Return streetId End Function
Ok, so here's what I did:
Dim myConnection As SqlClient.SqlConnection = GetConnection() Dim cmd As New SqlClient.SqlCommand("UPDATE cities SET name=@name,updatedate=getdate() WHERE geonameid=@geonameid", myConnection) cmd.Parameters.Add(New SqlClient.SqlParameter("@name", "Kąty Wrocławskie")) cmd.Parameters.Add(New SqlClient.SqlParameter("@geonameid", 6474)) myConnection.Open() cmd.ExecuteNonQuery myConnection.Close()
This correctly inserts the string "Kąty Wrocławskie" into my database. So I guess the database collation is working properly to support special characters?
If so, I now may have another issue, but please let me know if it's best to create a new issue on that:
As stated, newly inserted records work fine. However, in my database I still have values like: "SÄpĂłlno KrajeĹskie" (it should be "Sępólno Krajeńskie").
Should I now do a find/replace on all columns? And if so, where can I find a mapping table to know what to look for and what te replace with, because I have no idea where to start.