How to get the max length of text field in a DataSet?
I have a DataSet bound to an Access database. Is it possible to get the maximum length of a text field of a table in the DataSet?
Many fields in the database tables have maximum length values set in the design view in Access. When I examine the loaded DataSet in the VS 2005 debugger, I can't find any maximum length information anywhere in the DataSet or the "DataTable.Columns" objects. I wrote the schema of the DataTable to a file (using WriteXmlSchema) and I see that the XML schema does not contain maximum length information for any fields.
I want the maximum length to validate user input on a form. Currently, if a user enters too much text for a field and trys to update the database, an exception is thrown stating: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.". The exception does not contain any information identifying which field has the problem. The database is updated using method Update of class OleDbDataAdapter.
I would prefer to get the maximum length information from the database rather than having to duplicate it in the application, to avoid any potential problems if a maximum length is changed in the database.
I am working in C# with Visual Studio 2005 Professional.
[1313 byte] By [
jsstone] at [2007-12-24]
There is a MaxLength property on the DataColumn object. In order to make sure it's populated, set your DataAdapter's MissingSchemaAction to MissingSchemaAction.AddWithKey, or call FillSchema in addition to the regular Fill call.
Thanks,
Sarah
sorry to bring this one up again.
This technique certainly does populate the maxlength field of the datacolumns, BUT I get a (non-fatal) error because the query I am using has no primary key defined - I'm only going to read thge results.
The problem is that the MissingSchemaAction.AddKey has the affect of attempting to retrieve schema info, including primary keys and it complains if non is found.
Maybe the approach is to trap the error and tell the code to continue somehow? How?
In general, using AddWithKey should not fail if there is no key. Can you provide a code snippet that shows what you are doing, the error you get, and the callstack of that error?
As an example, I am using the following code:
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\northwind2000.mdb");
conn.Open();
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * from maxLengthTest", conn);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(dt);
Console.WriteLine("MaxLength for dt.Columns[0]: {0}", dt.Columns[0].MaxLength);
conn.Close();
maxLengthTest is a table with a text column named col1, which is of size 15. The above gives me the following correct output:
MaxLength for dt.Columns[0]: 15
There is no primary key defined on the table.
If you can provide more information about the error, we can figure out what's causing it and how to prevent it.
Thanks,
Sarah
Unfortunately I'm writing a little program to dump user entered SQL statements to an XML file along with schema info (eg MaxLength, datatype) and the data itself. I'm doing this to compare the query running against to different servers.
the query is pretty horrible as its generated by a reporting engine, and includes a call to a UDF, and it certainly returns duplicated fields.
here is the exception detail (note that I can continue in non-debug and it all seems OK):
System.Data.ConstraintException was unhandled
Message="Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
Source="System.Data"
StackTrace:
at System.Data.DataTable.EnableConstraints()
at System.Data.DataTable.set_EnforceConstraints(Boolean value)
at System.Data.DataTable.EndLoadData()
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SQLQueryCompare.QueryResultset.Populate() in F:\USERS\David\Visual Studio 2005\Projects\SQLQueryCompare\SQLQueryCompare\QueryResultset.cs:line 63
at SQLQueryCompare.Form1.cmdPopulate_Click(Object sender, EventArgs e) in F:\USERS\David\Visual Studio 2005\Projects\SQLQueryCompare\SQLQueryCompare\Form1.cs:line 48
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at SQLQueryCompare.Program.Main() in F:\USERS\David\Visual Studio 2005\Projects\SQLQueryCompare\SQLQueryCompare\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Code:
// run the querySqlDataAdapter dbadapter = new SqlDataAdapter(m_sql, m_dbconn);// this might cause MaxLength to be populateddbadapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;dbadapter.FillError +=
new FillErrorEventHandler(dbadapter_FillError);m_dbtable =
new DataTable("query - " + m_Name);// TODO: gonna have to use dataset.enforcecontraints property, which isnt available in a datatable!!dbadapter.Fill(m_dbtable);
as you can see I attempted to catch the fillerror, but nothing was fired!
also, see my TODO, I was gonna convert from datatable to use a dataset and set EnforceConstrints to off as my next attempt!
Ah, I see. So the actual data doesn't fit with the constraints for some reason. That's a bit of a concern in itself, but is not necessarily a big deal if you don't really want to use the constraints anyway. Perhaps the complexity of the query is causing the provider to not be able to accurately determine all of the metadata in this case.
If you are not concerned about actually enforcing the constraints, one thing you could do would be to use DataTable.FillSchema instead of using MissingSchemaAction.AddWithKey. FillSchema will fill in the schema information, including the key, but it doesn't go ahead and fill in the data as well. This would allow you to modify the constraints before actually filling the data. One way to do this is to call DataTable.Constraints.Clear() after FillSchema. This should prevent any constraint violation errors during Fill. This is different from EnforceConstraints in that EnforceConstraints doesn't actually remove the constraints from the DataTable collection, but it just doesn't apply those constraints to the data. If you don't want to apply them, and don't want to use them at all, I think Clear should be okay.
I think it is preferable to prevent the error in the first place, rather than just catching it and ignoring it. However, if you want to catch it and ignore, you can do that as well -- just add a try/catch around the call to Fill, and don't do anything with the error.
Thanks,
Sarah
I assumed (correctly) that FillSchema would do much the same as MissingSchemaAction.AddWithKey, but I didnt think that I could then clear the constraints BEFORE filling with actual data. Nice(ish) one!
The query is quite complex (157 columns & calls a table-valued inline UDF).
use of try/catch: I was worried that the error was happening in teh middle of the Fil, such that if I just catch it and ignore it then some important bits of code inside Fill wont be executed, wheras when running in non-debug mode I assumed that "continue" on the exception dialogue would continue from where it left off (a bit like a "resume next" in Vb6).
You are correct that the failure in Fill will effectively short circuit the logic so that some things may not get executed that would get executed in the non-error case. I think all of your data should get loaded by the point that the error occurs, but I don't know offhand if there is additional setup that would happen after the constraint enforcement. In general, preventing the error is definitely always the better way to go.
In the debugger, continue is different from resume next. When an exception occurs, the next line of code being executed will be the same as it would be if you weren't under the debugger, i.e. the first catch block that can handle the exception. You might want to use this if you want to trap the place where the exception is thrown from, but after you do something in the debugger, you want to continue running normally. For example, I might have a call stack with 5 methods on it, and the current method throws an exception, but only the outermost function has a catch block. I might want to examine some state inside the throwing method, before the control gets transferred to the catch block.
Thanks,
Sarah
