complex databind question

[bprego]

I am getting this error and I dont know why.

An unhandled exception of type 'System.InvalidOperationException' occurred in system.windows.forms.dll
Additional information: Cannot create a child list for field ?????.
--------------------------------------------------------------------------------

Thanks in advance for any help.
this is my code:

varAccountID = lblAccountID.Text
        'fills demos dataset        
        Dim selectCMD As SqlCommand = New SqlCommand("SELECT q.question_id, q.short_form, '' FROM question AS q INNER JOIN question_set AS qs ON q.question_id=qs.question_id WHERE qs.affinity_id=1 AND q.question_id NOT IN (SELECT v.question_id FROM answer_heading AS h INNER JOIN answer_value AS v ON h.answer_id=v.answer_id WHERE h.account_id = '" & varAccountID & "')UNION SELECT q.question_id, q.short_form, v.response FROM answer_heading AS h INNER JOIN answer_value AS v ON h.answer_id=v.answer_id INNER JOIN question AS q ON v.question_id=q.question_id WHERE(h.account_id = '" & varAccountID & "')ORDER BY q.question_id", SqlConnection1)

        Dim daAnswers As SqlDataAdapter = New SqlDataAdapter
        daAnswers.SelectCommand = selectCMD

        SqlConnection1.Open()

        Dim DsAnswers1 As DataSet = New DataSet
        DataGrid1.DataSource = DsAnswers1

        DsAnswers1.Clear()
        daAnswers.Fill(DsAnswers1)

        SqlConnection1.Close()

[Grimfort]

The help file has an example like this:

Private Sub BindToDataSet(myGrid As DataGrid)
    ' Create a DataSet.
    Dim myDataSet As New DataSet("myDataSet")
    ' Insert code to populate DataSet with several tables.
    myGrid.DataSource = myDataSet
    ' Use the DataMember property to specify the DataTable.
    myGrid.DataMember = "Suppliers"
End Sub 'BindToDataSet

[bprego]

how will having two datasets allow me to pull data from both datasets into the grid when you can only have one DataMemeber

myGrid.DataMember = "Suppliers"

if you can show me how to pull the questionID and the shortform from A question dataset and the response from A Answer dataset into the datagrid than I can use what you are suggesting.

or can I create two datasets, then put those datasets together in a third? How would I do that?

[Grimfort]

You can do a join query on data to merge two tables (using your question ID). Its quite a common feature of databases.

In fact, looking at your command code, you have already linked them together, so there will only be one datatable in the dataset.

[bprego]

DataGrid1.DataMember = "?????"

I have tried to replace ????? with

question
answer_value
answer_heading
question_set

and i get the same error

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll
Additional information: Cannot create a child list for field ?????.

So what do I replace the ????? with?

The query as it stands above is the way it has to be. I have to use the union.

Thanks

[Grimfort]

The data returned IS the table name, but you may not have it named (ive not tried it tbh). The ????? is the name of the table for the grid to load, you have been trying a field. Look at giving the return table/data a name.

[bprego]

the advice I have been given so far is not working. thanks for your help grim but I think your missing the whole picture.

[Grimfort]

Your possibly right .