Losing characters with sql parameterized insert query

[anony]

Hello,

I can't figure out why my parameterized query from an ASP.NET page is
dropping "special" characters such as accented quotes & apostrophes, the
registered trademark symbol, etc. These symbols insert without problem from
query analyzer, so that suggests it's something within ASP.NET. I've tried
using .NET textbox web controls as well as html textareas. I have a test
database set up with 4 fields: varchar, nvarchar, text, and ntext - they
all do the same thing. Regular text inserts fine, but it will simply remove
the special characters. I've searched usenet and the web for info, but to
no avail. Someone must have expereinced this issue! I'm running v1.1 with
the latest SP. Thanks for any help! Here's my code, doing my parameters in
different ways:

------------

Dim conCS As SqlConnection
Dim cmdSqlCommand As SqlCommand
Dim strSQL As String
Dim myParam As New SqlParameter

conCS = New SqlConnection(
ConfigurationSettings.AppSettings("ConnectionString") )
conCS.Open()

strSQL = " INSERT INTO TEST VALUES (@TEST_VARCHAR, @TEST_NVARCHAR,
@TEST_TEXT, @TEST_NTEXT) "

cmdSqlCommand = New SqlCommand( strSQL, conCS )

myParam.ParameterName = "@TEST_NVARCHAR"
myParam.SqlDbType = SqlDbType.NVarchar
myParam.Value = txtTest.Text
cmdSqlCommand.Parameters.Add(myParam)

cmdSqlCommand.Parameters.Add( "@TEST_VARCHAR", txtTest.Text )
cmdSqlCommand.Parameters.Add( "@TEST_TEXT", txtTest.Text )
cmdSqlCommand.Parameters.Add( "@TEST_NTEXT", txtTest.Text )

cmdSqlCommand.ExecuteNonQuery()
conCS.Close()

-------------

 

[Kevin Spencer]

I haven't used parameterized queries. We use Stored Procedures for all of
our database ops. I couldn't find a specific reference to this in the SDK
documentation, but I suspect that the difference is in how the query is
executed. My guess would be that when you create a parameterized query, .Net
puts a SQL statement together by doing the concatenation for you. If so,
single quotes and other special characters may be being dropped somehow,
although I would suspect that they would throw an exception instead. In any
case, why not use a Stored Procedure instead?

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[Girish Bharadwaj]

A dump question, are you sure that txtTest.Text contains all those special
chars?

 

[Greg Burns]

Try doing

myParam.ParameterName = "@TEST_NVARCHAR"
myParam.SqlDbType = SqlDbType.NVarchar
myParam.Value = "hard code charcters taht don't pass here as a test"
'txtTest.Text
cmdSqlCommand.Parameters.Add(myParam)

In other words, what is the value of your txtTest.Text in the debugger when
you get to that point in code. I can't imagine this is a SQL problem.

Maybe when you read txtTest.Text back in ASP.NET it is escaping your
characters or something.

Greg

 

[Greg Burns]

That's a good question, and the one he should be asking. :^)

Greg

A dump question, are you sure that txtTest.Text contains all those special
chars?

 

[anony]

Kevin - No reason why I can't use a SP, but I'm more so on a mission to
figure out what is going wrong. Greg, Girish - Yes, I'm really providing
input with special characters into the textbox! I tried your suggestion
below Greg, hardcoding the registered trademark symbol to my parameter, and
it did insert without problem. So something is lost when adding my
textbox.text as a parameter. Thanks for your replies.

Try doing

myParam.ParameterName = "@TEST_NVARCHAR"
myParam.SqlDbType = SqlDbType.NVarchar
myParam.Value = "hard code charcters taht don't pass here as a test"
'txtTest.Text
cmdSqlCommand.Parameters.Add(myParam)

In other words, what is the value of your txtTest.Text in the debugger
when you get to that point in code. I can't imagine this is a SQL
problem.

Maybe when you read txtTest.Text back in ASP.NET it is escaping your
characters or something.

Greg

 

[Greg Burns]

I seem to be able to read the registered trademark character back into a
variable from the textbox after postback.

How exactly are you getting these special symbols into your textbox?
ALT-numpad????

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
TextBox1.Text = Chr(174) ' registered trademark
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim x As String = TextBox1.Text
End Sub

Greg

Kevin - No reason why I can't use a SP, but I'm more so on a mission to
figure out what is going wrong. Greg, Girish - Yes, I'm really providing
input with special characters into the textbox! I tried your suggestion
below Greg, hardcoding the registered trademark symbol to my parameter,
and it did insert without problem. So something is lost when adding my
textbox.text as a parameter. Thanks for your replies.

 

[Greg Burns]

Should have been:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
TextBox1.Text = Chr(174)
End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim x As String = TextBox1.Text
End Sub

but the outcome is the same.

Greg

 

[anony]

Either by copy & pasting from Word or doing Alt-0174 directly in the
textbox.

I'd be curious as to what your string reads if you capture it from the
browser instead of assigning it like you're doing below. Even if I simply
Response.Write( textbox.Text ) on postback it drops the characters.

Thanks again.

 

[Greg Burns]

My first attempt was to put a ® in the html. When the page loaded I
copied it into the textbox. Seemed to work fine.

Aren't those characters coming from Word font specific? You actually see
them in your textbox before you submit the page?

As far as using ATL-0147 that works too. I just removed the Page_Load
stuff, and on postback x contains the reg character in the debug window and
displays on screen with a Response.Write(x).

Try my example on a new page, it should work. Must be something else on
your page that is messing things up.

Greg

 

[anony]

The culprit has been found: ResponseEncoding="iso-8859-1"

Dreamweaver adds this page directive automatically for asp.net pages. Thank
you Macromedia.

Thanks for your help!

 

[Greg Burns]

Glad you figured it out ('cause I never would have!)

Greg