Problem retrieving info from database into DataTable object

I'm getting this error message when I try to call the SqlDataAdapter.Update(DataTable table) method:

Missing the DataColumn 'expiration_date' in the DataTable 'Users' for the SourceColumn 'expiration_date'.

I think I understand the message, but I don't know how to fix it. I believe it means that there's a column named 'expiration_date' in my table in the SQL database but no corresponding column in the DataTable object I'm trying to copy the data into. (This makes sense because I took a working copy of my application and added an 'expiration_date' column to the table in the DB.) My problem is how do I add that column to the DataTable object.

I have this .xsd file in my project solution that seems to mirror the table structure in the database, so I added an 'expiration_date' field to the appropriate table in that file, but that hasn't helped. Does anyone know what I have to do? Thanks.
Who is Participating?
philipjonathanConnect With a Mentor Commented:
Insert this before line 4
myDA.MissingMappingAction = MissingMappingAction.Ignore.
Not very clear what's going on. E.g., not clear how the table is created. Do you run some sql statement?
Or the table is constructed manually, filled with data and saved to DB?

Also check if datatype in xsd for the column is same as for a column in database.
tjgquickenAuthor Commented:
I'm using someone else's legacy code and I'm not completely sure how this works. I have a class called IFData that's autogenerated by one of those .NET tools. IFData has a member DataTable called Users. The DataTable is populated with info from the SQL database. There's a table in IFData.xsd called Users, so I'm assuming they're the same, but adding an 'expiration_date' column (datatype System.DateTime) in .xsd doesn't seem to affect the DataTable.
string sql = "SELECT u.*, u.first_name+' '+u.last_name as wholename, q.question, GetDate() as Now FROM Users u, SecurityQuestions q WHERE = u.security_question AND = @userid AND u.status <> 2";
SqlDataAdapter myDA = new SqlDataAdapter( sql, myConnection );
IF.IFData ds = new IF.IFData();
myDA.Fill( ds, "Users" );

Open in new window

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

it sounds like you may have a DataSet configured for your solution that's generating TableAdapters - a visual designer like thing?
what can happen sometimes is that when the user 1st creates the dataset it;s modelled from the databases table structure. this is fine until that structure is changed... then when one of the adapters tries to fire a Add or an Update and the adapter/dataset's schema is different from the databases schema, you get errors like the one you described.
if this is the case, you need to update the dataset schema but BE CAREFUL because they are easy to screw up.. particularly if the Adapter has a lot of custom functions attached to it that DIDNT come from the DB.
[BE SURE to back up ur solution before trying these changes]
> Open up the DataSet designer and right-click on the DataTable object you need to update
> select "Configure..."
> click the "Query Builder" button from the dialog that follows
> you can see here that the table represented may or may not have all the fields included (look at the check boxes from the table)
> if needed, select remaining fields from the table to include in query
> click "Execute Query" to test the new SQL
> if all ok, click OK
> click Finish to apply changes.
at this point the DataTable should be fixed, but to ensure the TableAdapter is also up to date you may need to:
> right-click on the TableAdapter and select "Configure..."
> click "Query Builder..."
> like before make sure all needed fields are included in the SQL query bu selecting/de-selecting from the table shown at the top of the form
> click "Execute Query" to test and OK to apply
> click Finish to apply again
once completed, save the DataSet and try the code again
tjgquickenAuthor Commented:
This all makes sense to me, except that I can't find the Query Builder anywhere in VS2005. I might not be looking in the right place. When I open up the IFData.xsd file in VS2005, I get this graphical representation of all of my tables and their columns. (I attached a screenshot in case that helps.) If I right-click on the table in question, the Users table, the menu says Add, Configure (inactive), Cut, Copy, Paste, Delete, Rename, Autosize, View Code, and Properties. But none of those bring up a dialog or anything where I can construct a query.
tjgquickenAuthor Commented:
One thing I tried is adding an 'expiration_date' column to the IFData.xsd Users table via the view above and setting all of that column's properties. Then I went to the IFData.cs file and did a find and replace for 'expiration_date'. The search came up empty. Since the IFData.cs file is autogenerated, how do I re-generated it with this new column? Thanks.
right-click on the table in the designer and select "Configure..."
tjgquickenAuthor Commented:
My version of VS2005 must be missing something, because there's no "Configure..." in the right-click menu either.
tjgquickenAuthor Commented:
myDA.MissingMappingAction = MissingMappingAction.Ignore doesn't make a difference. Neither does MissingMappingAction.Passthrough, which is supposedly the default. The SqlDataAdapter is behaving like MissingMappingAction.Error. Here's the full exception that I'm getting:
Missing the DataColumn 'expiration_date' in the DataTable 'Users' for the SourceColumn 'expiration_date'.
Stack trace:   at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
  at KJI.Components.UsersDB.UpdateUserRecord(DataTable userTable, DataTable rolesTable, DataTable permsTable)
Exception type: System.InvalidOperationException
tjgquickenAuthor Commented:
Never mind that last post... made the change to the wrong part of the code. Still haven't gotten it working though...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.