Set TextBox MaxLength to match SQL varchar(nnn) length?

[Edward Mitchell]

I have a database with text string fields defined by varchar(nnn). When I
request from the user the text from a textbox, I'd like to set the maximum
number of characters in the textbox to the "nnn" that was used in the
varchar(...) statement in the field definition. i.e.

Field1 varchar(25) NOT NULL

and in the .aspx file:

<asp.textbox id=Field1 runat="server" />

I load a dataset and put data in the text box via the following:

DataSet dsInfo = new DataSet();
SqlDataAdapter daRecord
= new SqlDataAdapter("SELECT * FROM Records WHERE RecordID ='"
+ RecordID + "'", SqlConnect);
daRecord.Fill(dsInfo, "Table");

DataTable dtRecord = dsInfo.Tables["Table"];
DataRow drRecord = dtRecord.Rows[0];

textBox1.Text = drRecord["Field1"]
textBox1.MaxLength = ???

My question is how can I set the MaxLength property of the text box?
Somewhere in the depths of the DataSet must be some description of the
schema defining the field in the table. Could someone offer guidance as to
extracting this? Presumably, I should also be able to extract other details
of the schema such as type or nullability.

I could have an array of sizes for each one of the text boxes but it seems
bad programming practice to have the field sizes defined in two completely
different places.

Ed

 

[Kevin Yu [MSFT]]

Hi Edward,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the MaxLength of a
certain column from SQL database. If there is any misunderstanding, please
feel free to let me know.

I think we can do the following to achieve this.

1. Before filling the DataSet, we first fill the schema.

daRecord.FillSchema(ds, SchemaType.Source);
daRecord.Fill(dsInfo, "Table");

2. Then we use the DataColumn.MaxLength to get the maximum length. If the
column has no maximum length, the value is -1.

textBox1.Text = drRecord["Field1"]
int len = dsInfo.Tables["Table"].Columns["Field1"].MaxLength
if(len != -1)
textBox1.MaxLength = len;

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Edward Mitchell]

It's the FillSchema(...) that was the routine that I was looking for so that
I could interrogate the table definition.

However, I seem to get only the -1 that says that it has no maximum length!

I define the test table with:

CREATE TABLE Records
(
RecordID int NOT NULL IDENTITY PRIMARY KEY,
Field1 varchar(15) NOT NULL
)
INSERT Records (Field1) VALUES ("abc")
GO

and in the page load event do the following:

private void Page_Load(object sender, System.EventArgs e)
{
// database connection string
String SqlConnect = "workstation id=DELL340;packet size=4096;"
+ "integrated security=SSPI;data source=DELL340;"
+ "persist security info=False;initial catalog=Test";

DataSet dsInfo = new DataSet();

// read all the stuff from the table
SqlDataAdapter daRecord
= new SqlDataAdapter("SELECT * FROM Records", SqlConnect);
daRecord.FillSchema(dsInfo, SchemaType.Source);
daRecord.Fill(dsInfo, "Records");
DataTable dtRecord = dsInfo.Tables["Records"];
DataColumn dc1 = dtRecord.Columns["Field1"];
int iLen1 = dc1.MaxLength;
}

When I single step through this code, the iLen1 shows as -1 rather than the
15 that I had put into the varchar(...) field definition that was used when
the table was created.

I looked (with the Debugger) into the DataSet dsInfo and found that there
was a list of {System.Data.DataColumns} of length 2 and the second column
had the right name Field1 and a length of 15.

When I look into the DataTable dtRecord (the QuickWatch window expression
is:
((System.Collections.ArrayList)(((System.Data.DataColumnCollection)(dtRecord.Columns)).List))._items[1])
then the field name is still "Field1" but the MaxLength has been changed
to -1. I tried both SchemaType of Source or Mapped with the same results.

Why doesn't the 15 length carry over into the DataColumn?

Ed

 

[Kevin Yu [MSFT]]

Hi Edward,

You are getting -1 as the MaxLength of the DataColumn because when you
fillschema, the DataAdapter created a table named "Table". But when you
call Fill, the data was filled to another table named Records. So actually
the schema information was not put to the Record table. Thus, we have to
add a table name for FillSchema as the third argument. Please replace
FillSchema with the following line, which will resolved this issue.

daRecord.FillSchema(dsInfo, SchemaType.Source, "Records");

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Edward Mitchell]

Kevin,

That fix worked. I appreciate the help.

The documentation (MSDN Oct2004) for the .NET Framework Class Library
doesn't mention the availability of a third argument to FillSchema(...)! It
does say that FillSchema adds a DataTable named "Table" to the specified
DataSet so I could have extracted the lengths from this.

Ed

 

[Kevin Yu [MSFT]]

Hi Ed,

It was nice to hear that the problem is resolved.

Actually, you can find this overload for FillSchema from the following link.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondbdataadapterclassfillschematopic3.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."