RE: ADO.NET 2.0 saving single space to SQL?
- From: Donald Joppa <djoppa@xxxxxxxxxxxxx>
- Date: Tue, 7 Aug 2007 15:54:02 -0700
It turns out that changing the parameter to a type of varchar or using the
method signature that only specifies the colunm name and the value avoids
this problem. It seems likely that it is a bug in in the implicit conversion
from string to char to varchar. I think it should either work like 1.1 or it
should throw an exception.
"Donald Joppa" wrote:
I've created a much simpler test that demonstrates this on my machine when.
compiled with the .NET 2.0 framework.
Here's the C# code:
public static void InsertRecord()
{
SqlConnection conn = new
SqlConnection("Server=.;Database=SqlDataAdapterTest;Trusted_Connection=Yes;");
SqlCommand cmd = new SqlCommand("AddRecord", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@TextField", SqlDbType.Char);
param.Value = string.Empty;
cmd.Parameters.Add(param);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (conn != null)
conn.Close();
}
}
Here's the simple stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.AddRecord
@TextField varchar(50) = null
as
Insert into table1
(TextField)
values (@TextField)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Here's the table definitiion:
CREATE TABLE [Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TextField] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Here's the query that shows that the length of the column is 1 and the space:
select datalength(textfield), '[' + textfield + ']' from table1
"Donald Joppa" wrote:
We're converting from framework 1.1 to 2.0 and something interesting has
popped up. Fields that are being inserted into the database as a zero length
string in 1.1 contain a single space when inserted with 2.0 compiled code.
The code specifies a stored proc to run and set up the parameter collection.
I've verified in the debugger that in the parameter collection's item array
entries that I care about contain a zero length string (this is true for both
1.1 and 2.0).
SQL is running on a W2K3 box and the instance is SQL2K. I've checked the
database compatibility level and it is set to 80.
When I capture the insert command via SQL profiler from 1.1 the parameters
show up as a zero length string (''), but when it comes from 2.0 code the
same parameters contain a single space (' ').
Anyone have any idea what I'm missing?
- References:
- RE: ADO.NET 2.0 saving single space to SQL?
- From: Donald Joppa
- RE: ADO.NET 2.0 saving single space to SQL?
- Prev by Date: RE: ADO.NET 2.0 saving single space to SQL?
- Next by Date: Re: Which Method to Create a Database Do I Use?
- Previous by thread: RE: ADO.NET 2.0 saving single space to SQL?
- Next by thread: bind variables and sql
- Index(es):
Relevant Pages
|
