?
Solved

Problem retrieving info from database into DataTable object

Posted on 2008-06-09
10
Medium Priority
?
1,461 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

862 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