Text field padding when using ADO.Net

Hello,

Can somebody tell me what I'm doing wrong here, please? I'm using ADO.Net
from an ASP.Net page to insert records into an Access table. It works OK,
except that the value written to the text field ('Name') is always
right-padded with spaces to the width of the field. Here is the relevant bit
of code:

string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
cmd = new OleDbCommand(sql);
cmd.Connection = con;
cmd.Parameters.Add("@id", OleDbType.Integer);
cmd.Parameters.Add("@name", OleDbType.VarWChar);
cmd.Parameters["@id"].Value = id;
cmd.Parameters["@name"].Value = name;
cmd.ExecuteNonQuery();

Why does it pad, even though the parameter type is VarWChar? Thanks.

Scott
Thu Feb 05 07:55:49 CST 2004

Can't say why it is padding without seeing the rest of the code, but you can
just use the trim method on the text value (name) to strip off the spaces.


"Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
news:ux74i8%236DHA.360@TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Can somebody tell me what I'm doing wrong here, please? I'm using ADO.Net
> from an ASP.Net page to insert records into an Access table. It works OK,
> except that the value written to the text field ('Name') is always
> right-padded with spaces to the width of the field. Here is the relevant
bit
> of code:
>
> string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
> cmd = new OleDbCommand(sql);
> cmd.Connection = con;
> cmd.Parameters.Add("@id", OleDbType.Integer);
> cmd.Parameters.Add("@name", OleDbType.VarWChar);
> cmd.Parameters["@id"].Value = id;
> cmd.Parameters["@name"].Value = name;
> cmd.ExecuteNonQuery();
>
> Why does it pad, even though the parameter type is VarWChar? Thanks.
>
>
>

tperovic
Thu Feb 05 08:16:50 CST 2004

What is your backend database and what is the column type for Name?

"Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
news:ux74i8#6DHA.360@TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Can somebody tell me what I'm doing wrong here, please? I'm using ADO.Net
> from an ASP.Net page to insert records into an Access table. It works OK,
> except that the value written to the text field ('Name') is always
> right-padded with spaces to the width of the field. Here is the relevant
bit
> of code:
>
> string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
> cmd = new OleDbCommand(sql);
> cmd.Connection = con;
> cmd.Parameters.Add("@id", OleDbType.Integer);
> cmd.Parameters.Add("@name", OleDbType.VarWChar);
> cmd.Parameters["@id"].Value = id;
> cmd.Parameters["@name"].Value = name;
> cmd.ExecuteNonQuery();
>
> Why does it pad, even though the parameter type is VarWChar? Thanks.
>
>
>

Sheila
Thu Feb 05 08:16:27 CST 2004

You mean like this?
cmd.Parameters["@name"].Value = name.Trim();

Unfortunately that doesn't help, as the name string is already trimmed. The
padding is being added when the query is run.

Thanks for the reply.


"Scott M." <s-mar@BADSPAMsnet.net> wrote in message
news:%23mpcU$%236DHA.2496@TK2MSFTNGP09.phx.gbl...
> Can't say why it is padding without seeing the rest of the code, but you
can
> just use the trim method on the text value (name) to strip off the spaces.
>
>
> "Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
> news:ux74i8%236DHA.360@TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > Can somebody tell me what I'm doing wrong here, please? I'm using
ADO.Net
> > from an ASP.Net page to insert records into an Access table. It works
OK,
> > except that the value written to the text field ('Name') is always
> > right-padded with spaces to the width of the field. Here is the relevant
> bit
> > of code:
> >
> > string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
> > cmd = new OleDbCommand(sql);
> > cmd.Connection = con;
> > cmd.Parameters.Add("@id", OleDbType.Integer);
> > cmd.Parameters.Add("@name", OleDbType.VarWChar);
> > cmd.Parameters["@id"].Value = id;
> > cmd.Parameters["@name"].Value = name;
> > cmd.ExecuteNonQuery();
> >
> > Why does it pad, even though the parameter type is VarWChar? Thanks.
> >
> >
> >
>
>

Sheila
Thu Feb 05 08:48:17 CST 2004

Actually, I've just sussed what the problem was. I'd created the table using
CREATE TABLE PRODUCTS (ID INTEGER CONSTRAINT PrimaryKey PRIMARY KEY,
[Name] CHAR(255))

I should have defined [Name] as TEXT(255) instead.

It confused me, because in both cases, Access shows the field as being
'Text' if you open the table in Design view.

Thanks to both of you for replies.


"tperovic" <tperovic@compumation.com> wrote in message
news:mfsUb.181168$5V2.886314@attbi_s53...
> What is your backend database and what is the column type for Name?
>
> "Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
> news:ux74i8#6DHA.360@TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > Can somebody tell me what I'm doing wrong here, please? I'm using
ADO.Net
> > from an ASP.Net page to insert records into an Access table. It works
OK,
> > except that the value written to the text field ('Name') is always
> > right-padded with spaces to the width of the field. Here is the relevant
> bit
> > of code:
> >
> > string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
> > cmd = new OleDbCommand(sql);
> > cmd.Connection = con;
> > cmd.Parameters.Add("@id", OleDbType.Integer);
> > cmd.Parameters.Add("@name", OleDbType.VarWChar);
> > cmd.Parameters["@id"].Value = id;
> > cmd.Parameters["@name"].Value = name;
> > cmd.ExecuteNonQuery();
> >
> > Why does it pad, even though the parameter type is VarWChar? Thanks.
> >
> >
> >
>
>

Scott
Thu Feb 05 09:15:56 CST 2004

That's because Access doesn't support the CHAR type, so it shows you the
closest thing it does have.


"Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
news:eqfNUc$6DHA.632@TK2MSFTNGP12.phx.gbl...
> Actually, I've just sussed what the problem was. I'd created the table
using
> CREATE TABLE PRODUCTS (ID INTEGER CONSTRAINT PrimaryKey PRIMARY KEY,
> [Name] CHAR(255))
>
> I should have defined [Name] as TEXT(255) instead.
>
> It confused me, because in both cases, Access shows the field as being
> 'Text' if you open the table in Design view.
>
> Thanks to both of you for replies.
>
>
> "tperovic" <tperovic@compumation.com> wrote in message
> news:mfsUb.181168$5V2.886314@attbi_s53...
> > What is your backend database and what is the column type for Name?
> >
> > "Sheila Jones" <sheilavjones@btopenworld.com> wrote in message
> > news:ux74i8#6DHA.360@TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > Can somebody tell me what I'm doing wrong here, please? I'm using
> ADO.Net
> > > from an ASP.Net page to insert records into an Access table. It works
> OK,
> > > except that the value written to the text field ('Name') is always
> > > right-padded with spaces to the width of the field. Here is the
relevant
> > bit
> > > of code:
> > >
> > > string sql = @"INSERT INTO PRODUCTS (ID, Name) VALUES (?, ?)";
> > > cmd = new OleDbCommand(sql);
> > > cmd.Connection = con;
> > > cmd.Parameters.Add("@id", OleDbType.Integer);
> > > cmd.Parameters.Add("@name", OleDbType.VarWChar);
> > > cmd.Parameters["@id"].Value = id;
> > > cmd.Parameters["@name"].Value = name;
> > > cmd.ExecuteNonQuery();
> > >
> > > Why does it pad, even though the parameter type is VarWChar? Thanks.
> > >
> > >
> > >
> >
> >
>
>