£ signs etc not being stored in database

Hi,
I'm having a few problems with my forms saving things like ￡ signs.
I'm trying to store all my data in plain text with-in a MS SQL 2000 database, but for some reason certain characters are being automatically removed. I don't really want to use the HTML code for these characters so was wondering if anyone know how I could stop these characters automatically being removed.
Many thanks in advance.
Below is the code I'm using to insert the text to the database:
----
Dim MyConnection9 As New SQLConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim MySQL9 as string
MySQL9 = "INSERT INTO NEWS_ARTICLES " & _
"(NewsTitle, NewsText, NewsDate, NewsShow) " & _
"VALUEs (@.title, @.text, @.date, @.show)"
Dim Cmd9 as New SqlCommand(MySQL9, MyConnection9)

Dim strtitle, strtext, strimage, strdate, strshow
strtitle = txttitle.Text
strtext = txtText.Text
strdate = DateTime.Now
strshow = ddlShow.SelectedItem.Value

cmd9.Parameters.Add(New SqlParameter("@.title", strtitle))
cmd9.Parameters.Add(New SqlParameter("@.text", strtext))
cmd9.Parameters.Add(New SqlParameter("@.date", strdate))
cmd9.Parameters.Add(New SqlParameter("@.show", strshow))

MyConnection9.Open()
cmd9.ExecuteNonQuery
MyConnection9.Close()
----
Regards,
Rich

What data type are you using? See this article from BOL on Unicode characters.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6voh.asp
When I run this, it works.
declare @.test nvarchar(50)

set @.test = '￡'

select @.test

Nick

|||

Hi Nick,

Thanks for your reply. I'm using nvarchar and also ntext.

Do you think it's my code that's wrong? Or something on the SQL setup?

If I edit it via SQL enterprise manager, it works ok.

Regards,

Rich

|||I would recommend you set a break point in your code and run it in debug mode. This sounds like its an issue of your web code not holding the value correctly. How are you getting the value including the￡into your string variable? Does it come from a textbox? If so, how does it get added to your textbox (key pattern, copy and paste, etc)?
Nick|||

Hi Nick,
I'm getting the ￡ signs from within a textbox, either by pasting one in there or by using the ￡ on the keyboard.
I've tried changing the cost and instead of using the textbox text, I replaced it with a ￡ which worked. So I think it's somewhere in the below code I'm using, but I just can't seem to figure it out :(
Dim strtitle, strtext, strimage, strdate, strshow
strtitle = txttitle.Text
strtext = txtText.Text
strdate = DateTime.Now
strshow = ddlShow.SelectedItem.Value
'strimage = ddlImage.SelectedItem.Value

cmd9.Parameters.Add(New SqlParameter("@.title", strtitle))
cmd9.Parameters.Add(New SqlParameter("@.text", strtext))
cmd9.Parameters.Add(New SqlParameter("@.date", strdate))
cmd9.Parameters.Add(New SqlParameter("@.show", strshow))
Thanks again for your help so far.
Regards,
Rich

|||

Hi Nick,

When you say insert a break point? What does this mean? It's unfortunately not a term I'm familiar with :(

Regards,

Rich

|||

I wonder if the issue is the parm construction. I think it might be seeing the ￡ sign, and the datatype is being converted to money (That is the English symbol for money right?) Maybe try:
dim parm as new sqlParameter("@.title", sqldbType.varchar, 50)
parm.Value = strTitle
cmd9.Parameters.add(parm)
Does cmd9.Parameters.Add(New SqlParameter("@.title", "￡")) work?

Nick
Edit: forgot the parm.add

|||

Hi Nick,

Thanks again for your reply.

I tried your suggested code, but unfortunately it didn't make any difference. Although, cmd9.Parameters.Add(New SqlParameter("@.title", "￡")) did work.
I also tried:
dim parm as new sqlParameter("@.title", sqldbType.varchar, 50)
parm.Value = txtTitle.Text
cmd9.Parameters.add(parm)
My textbox code is:
<asp:TextBox ID="txtTitle" runat="server" Width="350" />
Does that look ok?

Cheers,

Rich

|||

Try outputting the value

response.write (me.txtTitle.text) : response.end

If it doesnt have the value, the encoding isnt happening correctly. Try:

response.write (request.form("txtTitle")) : response.end
See if that helps any. Im kind of lost on this one as I tried it myself and everything seemed okay.
Nick

|||

Hi Nick,
Thanks again for your reply. Same thing happens when I do this also :(
$ works, but not ￡, very strange...
Thanks again for all your help.
Cheers,
Rich

|||

Could the problem be with the below bit of code which is also used on the page?
<%@. Page Language="VB" ContentType="text/html" validateRequest=false ResponseEncoding="iso-8859-1" debug="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQLClient" %>
<%@. Import Namespace="System.Web.Mail" %>
<%@. Import Namespace="System.DateTime" %>
Cheers,
Rich