ExamGuru IT/.Net Guru : Losing characters with sql parameterized insert query on microsoft.public.dotnet.framework.aspnet

.NETGURU

Losing characters with sql parameterized insert query

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.aspnet.

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()

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

Reply to this message...

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

"anony" <Click here to reveal e-mail address> wrote in message
news:bMh1d.43696$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

"anony" <Click here to reveal e-mail address> wrote in message
news:bMh1d.43696$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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.

"Greg Burns" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

"anony" <Click here to reveal e-mail address> wrote in message
news:ayl1d.28159$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

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

"Greg Burns" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Girish Bharadwaj

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

--
Girish Bharadwaj
http://msmvps.com/gbvb
"anony" <Click here to reveal e-mail address> wrote in message
news:bMh1d.43696$Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Greg Burns

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

Greg

"Girish Bharadwaj" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

	System.Configuration.ConfigurationSettings
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlParameter
	System.Data.SqlDbType
	System.EventArgs
	System.Object
	System.Web.UI.Page

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification