Re: Losing characters with sql parameterized insert query

From: Kevin Spencer (kspencer_at_takempis.com)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 10:50:41 -0400

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" <nosp@m.com> wrote in message
news:bMh1d.43696$w_4.6339662@twister.tampabay.rr.com...
> 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()
>
> -------------
>
>

Relevant Pages

  • Return Results of sp_helptext via ADO
    ... I am trying to write code to document stored procedures for an Access ... Dim daorsStoredProcs As DAO.Recordset ... Dim strSQLStoredProcBase As String ... Set adoconnStoredProcText = New ADODB.Connection ...
    (microsoft.public.data.ado)
  • Re: Restrictions accessing MDB (through OLEDB) within .NET applications?
    ... that you will need a parameterized query such as ... > Dim insertCmd As OleDbCommand ... > Dim conn As OleDbConnection ...
    (microsoft.public.dotnet.general)
  • Re: ADP - ADO beginner Search Records with a Sp ~ help please
    ... need additional stored procedures for updating/adding/deleting. ... > Dim MyMsgBox As VbMsgBoxStyle ... >Dim cnn As ADODB.Connection ... >Set rst = New ADODB.Recordset ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Moving User Defined Functions between Databases
    ... Stefan Berglund gave a great answer. ... You can include all your functions, triggers, and stored ... user defined functions and stored procedures, ... Dim oServer As SQLDMO.SQLServer2 ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL query... is there a better way?
    ... Your code works well, but when I am usin parameterized query, it gives me ... followin error: "No value given for one or more required parameters." ... Dim paramList As New StringBuilder ... > Dim paramList As New StringBuilder ...
    (microsoft.public.dotnet.framework.adonet)