Solved

Problem retrieving info from database into DataTable object

Posted on 2008-06-09
10
1,447 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:tjgquicken
10 Comments
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 21747187
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.
0
 

Author Comment

by:tjgquicken
ID: 21747819
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 q.id = u.security_question AND u.id = @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

0
 
LVL 6

Expert Comment

by:cottsak
ID: 21748328
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tjgquicken
ID: 21751239
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.
vs2005.GIF
0
 

Author Comment

by:tjgquicken
ID: 21751361
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.
0
 
LVL 18

Accepted Solution

by:
philipjonathan earned 500 total points
ID: 21756751
Insert this before line 4
myDA.MissingMappingAction = MissingMappingAction.Ignore.
0
 
LVL 6

Expert Comment

by:cottsak
ID: 21756927
right-click on the table in the designer and select "Configure..."
0
 

Author Comment

by:tjgquicken
ID: 21761151
My version of VS2005 must be missing something, because there's no "Configure..." in the right-click menu either.
0
 

Author Comment

by:tjgquicken
ID: 21761325
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
0
 

Author Comment

by:tjgquicken
ID: 21761637
Never mind that last post... made the change to the wrong part of the code. Still haven't gotten it working though...
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question