Solved

Problem retrieving info from database into DataTable object

Posted on 2008-06-09
10
1,450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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 short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 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