Text field padding when using ADO.Net

  • Thread starter Thread starter Sheila Jones
  • Start date Start date
S

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.
 
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.
 
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. said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
What is your backend database and what is the column type for Name?

Sheila Jones said:
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.
Click to expand...
Click to expand...
 
That's because Access doesn't support the CHAR type, so it shows you the
closest thing it does have.


Sheila Jones said:
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 said:
What is your backend database and what is the column type for Name?

Sheila Jones said:
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top