From a standard web form I'm inserting a record using a stored procedure. (I'm writing this to a SQL 2000 db - where the column types and variables are all consistant) No matter what I do, the columns are padded with extra characters maxing out the field length (if it's nchar or nvarchar or char) after insert or update. I've tried Trim - ing the field.text values that I'm feeding to the @.variables used in my stored procedure. I've even RTRIM() - ed the @.variables within the stored procedure. No matter what I do I get extra spaces padding the end of the intended column input. Ideas anyone?
Thanks in advance.
- AbeDepending on your extra characters, trimmimg won't solve your problem if they are not whitespaces.
Could you supply more info and post your ASP and T-Sql code?
|||
Thanks. I'm pretty sure it's spaces because if I run a query "SELECT Education FROM tblCV WHERE (Education LIKE N'% %')" I get all records returned except the ones that I manually stripped out the trailing spaces.
privatevoid InsertCV()
{
SqlConnection conn =new SqlConnection(connectString);
SqlCommand cmd =new SqlCommand("sp_InsertCV",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.AAASS_ID",SqlDbType.NVarChar,5);
cmd.Parameters.Add("@.Education",SqlDbType.NChar,700);
cmd.Parameters.Add("@.Experience",SqlDbType.NChar,700);
cmd.Parameters.Add("@.Publication",SqlDbType.NChar,700);
cmd.Parameters.Add("@.ConferencePapers",SqlDbType.NChar,700);
cmd.Parameters.Add("@.OrganizationalMembership",SqlDbType.NChar,700);
cmd.Parameters.Add("@.EnteredBy",SqlDbType.NVarChar,10);
cmd.Parameters.Add("@.UpdatedBy",SqlDbType.NVarChar,10);
cmd.Parameters["@.AAASS_ID"].Value = _AAASSID;
cmd.Parameters["@.Education"].Value =this.education.Text;
cmd.Parameters["@.Experience"].Value =this.experience.Text;
cmd.Parameters["@.Publication"].Value =this.publications.Text;
cmd.Parameters["@.ConferencePapers"].Value =this.conferencePapers.Text;
cmd.Parameters["@.OrganizationalMembership"].Value =this.organizationalMembership.Text;
cmd.Parameters["@.EnteredBy"].Value = _AAASSID;
cmd.Parameters["@.UpdatedBy"].Value = _AAASSID;
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
CREATE PROCEDURE sp_InsertCV
(
@.AAASS_ID AS nvarchar(5),
@.Education AS nchar(700),
@.Experience AS nchar(700),
@.Publication AS nchar(700),
@.ConferencePapers AS nchar(700),
@.OrganizationalMembership AS nchar(700),
@.EnteredBy AS char(10),
@.UpdatedBy AS char(10)
)
AS
INSERT INTO [AAASSSQLData].[dbo].[tblCV]
(
[AAASS_ID], [Education], [Experience], [Publication], [ConferencePapers], [OrganizationalMembership], [DateEntered], [EnteredBy],
[DateUpdated], [UpdatedBy]
)
VALUES(
@.AAASS_ID,RTRIM(@.Education),RTRIM(@.Experience),
RTRIM(@.Publication),
RTRIM(@.ConferencePapers),
RTRIM(@.OrganizationalMembership),
GetDate(),
RTRIM(@.EnteredBy),
GetDate(),
RTRIM(@.UpdatedBy)
)
GO
tblCV
3 AAASS_ID nvarchar 5 0
0 Education nchar 700 1
0 Experience nchar 700 1
0 Publication nchar 700 1
0 ConferencePapers nchar 700 1
0 OrganizationalMembership nchar 700 1
0 DateEntered datetime 8 1
0 EnteredBy char 10 1
0 DateUpdated datetime 8 1
0 UpdatedBy char 10 1
Column contents: "SUNY Binghamton, B.A., Psychology, 1982
Pace U., MBA, Management Information Systems, 1986 "
Thank you for taking a look at this.
- Abe
Best,
- AbeR
