DataRelation Problem

adshocker

Well-known member
Joined
Jun 30, 2007
Messages
180
Programming Experience
Beginner
Hi all,

With the help of this forum, I learned how to use the basics of .xsd Type DataSets. I was also able to use simple DataRelations, Foreign Key Constraints.

However, I encountered another problem and need some assistance.

Using the Wizard, I created a DataSet. Then I manually created the DataRelations and Foreign Key Constraints. Everything works fine as long as I don't changed anything else on my TableAdapters and DataTable.

But I need to have a Dynamic SQL for filling some of my DataTables. This is where the problem begins. My TableAdapters default SelectCommand is "SELECT * FROM video" and it works fine. But then on my screen I have an option where the use can perform a search so my SelectCommand will contain a Dynamic SQL from here. So I programmatically create a dataadapter like this.

VB.NET:
[SIZE="2"]
Private Sub ExecuteSearch()
Dim orclConnection As New OracleConnection(Me.VIDEOTableAdapter.Connection.ConnectionString)
Dim orclCommand As New OracleCommand("SELECT * FROM video WHERE video_ID LIKE '" & txtVideoId.Text & "'", orclConnection)
Dim orclAdapter As OracleDataAdapter
orclAdapter = New OracleDataAdapter(orclCommand)
orclAdapter.Fill(Me.OrclDataset.VIDEO) 'Fill parent datatable.
Me.RENTAL_PAYMENTSTableAdapter.Fill(Me.OrclDataset.RENTAL_PAYMENTS) 'Fill parent datatable
[I]Me.RENTED_VIDEOTableAdapter.Fill(Me.OrclDataset.RENTED_VIDEO)[/I] 'Fill child datatable
End Sub[/SIZE]

I've made sure that all my parent datatables are being filled first, (whether or not they're being used on the screen) before I fill the child table.

Also, I clear all the datatables when I click the EnterSearch button I created. Then the codes above I put on a ExecuteSearch button.

But still it gives me the "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." error again on the italized line above.

I've also noticed that when you changed the SelectCommand of the TableAdapter at design time, (by going to the TableAdapters SelectCommand property) and setting it to a Select Statement with WHERE condtion something like "SELECT * FROM video WHERE video_id = 1". Then when I run the form I get an error immediately at the line where my TableAdapter Fills the Datatable.

Do DataTables with DataRelation not support SQL with WHERE conditions?

Please help... Thanks.
 
Last edited:
unless I'm missing something, why don't you create queries on the actual tableAdapter itself, instead of in code, then all you do is call the query?

I.e instead of calling

VB.NET:
Me.RENTAL_PAYMENTSTableAdapter.Fill(Me.OrclDataset.VIDEO)

you call something along the lines of

VB.NET:
Me.RENTAL_PAYMENTSTableAdapter.FillByVideoID(Me.OrclDataset.VIDEO, me.txtVideoID.text)

Where the Query you have set up is called FillByVideoID (SELECT * FROM Video WHERE VideoID = @VideoID)

and you use whatever is in the textbox txtVideoID as your parameter (@VideoID) ?
 
But I need to have a Dynamic SQL for filling some of my DataTables. This is where the problem begins. My TableAdapters default SelectCommand is "SELECT * FROM video" and it works fine. But then on my screen I have an option where the use can perform a search so my SelectCommand will contain a Dynamic SQL from here. So I programmatically create a dataadapter like this.
That's not dynamic SQL I'm afraid. Dynamic SQL is one where the structure of the rows returned each time, changes.. That's simply a parameterised SQL and the IDE can handle that one very easily.. Read the DW2 link in my signature to find out how, the section you want is "Creating a Form to Search Data"



I've made sure that all my parent datatables are being filled first, (whether or not they're being used on the screen) before I fill the child table.
I've mentioned before, that youre better off making seaprate datasets, or selectively disabling constraints, than downloading huge amounts of data you will never use.. Get into good habits from the start ;)

But still it gives me the "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." error again on the italized line above.
I advised you to use Nitpick.dll - what was the result of your using it?


Do DataTables with DataRelation not support SQL with WHERE conditions?
Yes, they do, and the error you'll be making will be something very simple that you'll kick yourself for later.. Something like "there is no row with id 1", or "row id 1 has a name of length 250, and the column is only 100 long"
All I can say, is use Nitpick.. it will tell you all the errors on every row of every table in a dataset.. I cant help you much more if youre not going to follow advice?!
 
To save you looking. Use would be something like this:

VB.NET:
Try
  adapter.Fill(broken method)
Catch Exception
  StringBuilder errMsgs = New StringBuilder
  Nitpick.WhatsWrong(myDataSet, errMsgs)

  MessageBox.Show(errMsgs)
End Try
 

Attachments

  • Nitpick.zip
    2.7 KB · Views: 12
hi all,

thanks for the replies... sorry it took me a while to get back here.

also, pardon my being stubborn..

i tried to do the disabling of contraints instead of loading data not needed... this works fine.

also, i haven't use nitpick yet... sorry about that... but i'm gonna do try using it just to figure out whats wrong with my dataset. thanks again for the tip.
 
Back
Top