Solved

Problem retrieving info from database into DataTable object

Posted on 2008-06-09
10
1,418 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 29

Expert Comment

by:anarki_jimbel
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:tjgquicken
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Accepted Solution

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

Expert Comment

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

Author Comment

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

Author Comment

by:tjgquicken
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now