473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DataTable: Corrupted SQL Table Properties with generalized SqlDataAdapter?

Hello,

I've encountered a rather puzzling problem with SqlDataAdapter. I've set up a number of elaborate tables in the Microsoft SQL Server Management Studio Express (including several table key relationships), and I've been successfully accessing all of that information through the classes generated by the "visual" DataSet file in Web Developer Express (Solution > Add New Item > Data Set).

Prior to my last code modification I've successfully read the SQL table relationships from DataSet.Relations, or, in my case, ProjectsDataSet.Relations (my main table is called "Projects", ProjectsDataSet was generated by Web Developer Express).

For the next step of my project I had to abandon the classes from the "visual" DataSet files, such as ProjectsDataSet and ProjectsDataAdapter, and start making the program work with generalized DataSets, SqlDataAdapters and SqlConnections.

So instead of relying on the classes from the DataSet file, I began to feed my application the following manually created instances:

Expand|Select|Wrap|Line Numbers
  1. string tableName = "Projects";
  2.  
  3. SqlConnection connection = new SqlConnection( connectionString );
  4.  
  5. DataSet dataSet = new DataSet();
  6. SqlDataAdapter dataAdapter = new SqlDataAdapter( "select * from " + tableName, connection );
  7. SqlCommandBuilder b = new SqlCommandBuilder( dataAdapter );
  8. dataAdapter.Fill( dataSet, tableName );
  9.  
Suddenly my program no longer detects any relations in the DataSet, even though it comes from the exact same source as the one that I allocated "by hand" instead of using the DataSet file. The DataSet.Relations list has no elements at all.

What's worse, the AllowDBNull properties of my DataColumns are all "true" even though most of my columns don't allow null values. I've explicitly set the column properties in MS Server Management Studio!

It seems that unlike the ProjectsDataSet class that was created by Visual Web Developer, the manually-allocated DataSet object isn't pulling all information out of the database definitions.

Any ideas?
Apr 17 '07 #1
3 1630
iburyak
1,017 Expert 512MB
Microsoft is famous to call this type of programming as disconnected datasets.
It makes intelligent assumption on data structure but not actual like allow null values where they are not allowed in underlying database. I am sure you will not be able to save this dataset to a database with null value in a column.
First time - you linked to the database and this time - you imported data and created your own disconnected database that is independent from the server.
This way it is easy to import XML and SQL data into the same DataAdapter and join together seamlessly.

For more info check this:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx

Sorry not sure how to help you here.

Irina.
Apr 17 '07 #2
Thanks. I had already read through that page and actually based parts of my program on it.

Is there another elegant way to retrieve table/column information, like AllowDBNull or Relations, other than looking at the DataColumn object of the DataTable?
Apr 17 '07 #3
Just in case anyone is having the same problem, after a long and annoying search I've finally found a solution:

You need to generate an XSD database schema file using Web Developer Express (Or by any other means) and then,prior to Fill(), load it into the DataSet using ReadXmlSchema().
Apr 18 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Luis Esteban Valencia | last post by:
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source...
10
by: dauphian | last post by:
Hello, I am new to .net and am trying to build a report application that queries 4 different tables based on a id, and I need to return them in the same table for easy viewing. Basically, I...
1
by: andrewcw | last post by:
I have just 1 table that I am updating, the SQL I use to generate the DataTable is complex using multiple tables, however when I view the fieldnames of the DataTable from the DataSet - everything...
9
by: =?Utf-8?B?anAybXNmdA==?= | last post by:
I've got a routine that builds a table using different queries, different SQL Tables, and adding custom fields. It takes a while to run (20 - 45 seconds) so I wrote a thread to handle the table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.