Text field padding when using ADO.Net

[Sheila Jones]

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 M.]

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]

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.

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.

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]

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

 

[Sheila Jones]

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.

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

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 M.]

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

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.

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

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.