ADO.NET 2.0 saving single space to SQL?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Let's see the code used to invoke the SP--especially the code that populates
the Parameters collection. It would be helpful to know what values are
passed to the problem parameter.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Here's the method that is called to create the parameter object

public SqlParameter AddParam(string paramName, SqlDbType paramType, object
paramValue)
{
SqlParameter param = mCmd.Parameters.Add(paramName, paramType);
param.Value = paramValue;
return param;
}

Here's an edited sample of how it is called:

db.AddParam("@EmailAddress", SqlDbType.Char, EmailInfo.EmailAddr.Value);

The value in the problem case is a string with a zero length.

The mCmd.CommandType is CommandType.StoredProcedure and the code is calling
mCmd.ExecuteNonQuery().

Note that I've looked at the mCmd.Parameters in the debugger and burrowed
down to the itemarray with 1.1 code and 2.0 code. In both cases I'm seeing
values that are empty strings, but when picked up by sql profiler the 2.0
call has a single space in the parameter...
 
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
 
William see my post to my original question. I've been able to reproduce the
issue in a much simpler scenario. Including c# code, table definition, and
stored procedure definition.
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
Ah, why are you using Char? There are very few (good) reasons to do so. I
try to avoid it since space is usually not a consideration. I use it for
FIXED-length text values like the StateCode which has to be two characters
(and only two characters) and the value must exist in the ValidStates table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
It is code someone else wrote quite some time ago and all of the string
parameters in the code were identified as char (that's been fixed now). In
the stored procedure and in the database columns those fields are varchar...
 
Back
Top