Deleting rows in from a UNION query

[pachjo]

Hi,

if I have a tableadapter that has a single table in it populated from two seperate database tables by use of a union query would I be able to call the follow?

tableadapter.table.rows.item.delete

tableadapter.update(table)

and have the rows from each pyhsical table deleted?

Thansk

 

[pachjo]

I found I got an error by not having a deletecommand setup, so I created a commandbuilder and run the following code but get an error:

'Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information'

I guess this has something to do with the union and it not know what table is which?

I don't understand really what I need to do here? Any help please?

Thanks

taTableAdapter = New SqlClient.SqlDataAdapter("SELECT id, tran_date, tran_id, tran_amount tran_balance, tran_save, tran_withdrawal, void FROM transactions WHERE void = 'True' AND tran_date BETWEEN @DateFrom AND @DateTo UNION SELECT id, tran_date, tran_id, tran_amount, tran_balance, tran_save, tran_withdrawal, void FROM transactions_weekly WHERE void = 'True' AND tran_date BETWEEN @DateFrom AND @DateTo", glb_cnMM2007)

taTableAdapter.SelectCommand.Parameters.Add("@DateFrom", SqlDbType.DateTime)

taTableAdapter.SelectCommand.Parameters.Add("@DateTo", SqlDbType.DateTime)

taTableAdapter.SelectCommand.Parameters("@DateFrom").Value = Me.dtmDeleteVoidFrom.Value.Date

taTableAdapter.SelectCommand.Parameters("@DateTo").Value = Me.dtmDeleteVoidTo.Value.Date

cbCommandBuilder = New SqlClient.SqlCommandBuilder(taTableAdapter)

taTableAdapter.Fill(dtTable)

If dtTable.Rows.Count > 0 Then

   For intX = 0 To dtTable.Rows.Count - 1

        dtTable.Rows.Item(intX).Delete()

   Next

   taTableAdapter.Update(dtTable)

End If

 

[craigv]

I had a similar situation with a JOIN in VS 2005 Express. I added a Delete statement for the base table. I added this to the TableAdapter in the Dataset.

HTH

 

[pachjo]

I have read that you cannot delete from a union query?

 

[cjard]

Hi,

if I have a tableadapter that has a single table in it populated from two seperate database tables by use of a union query would I be able to call the follow?

tableadapter.table.rows.item.delete

tableadapter.update(table)

and have the rows from each pyhsical table deleted?

Thansk

no, unless you write the delete query yourself

 

[cjard]

I have read that you cannot delete from a union query?

It depends on the rdbms, but most likely youll have to write the delete query yourself because the wizard isnt artificially intelligent enough to do it for you, for obvious reasons.

 

[cjard]

'Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information'

in english: The wizard is not artificially intelligent enough to generate a delete query from a select statement that uses more than one table. Write the delete query yourself.

See, normally we only write:

SELECT * FROM table1 WHERE ...

So the wizard can say "ah, the table is called 'table1', i'll go and look up the primary key and schema of it, and from there i can work out the insert/update/delete queries"

Of course, in a query that exports no schema and doesnt guarantee a primary key, this cannot be done automatically..

 

[pachjo]

Very nicely explain

Alas me thinks I do not know how to id which item would be from which table as I too am as nutty as the wizard

I resorted to creating a function which accepts the table name, bit naff as it has to be called twice, but it works!

I thought I was being clever using a union but .......not so

Hey ho

 

[cjard]

well, heres another question.. why have the union at all.. why not just have one table?

 

[pachjo]

Because I have two tabpages that have a listview based on transactions from different tables.

The user can void any transaction they like which leaves the record on file and they can then chose to view all current transactions or all current transactions including voided ones.

On the maintenance tabpage they can select transactions to delete from the database that are voided as a housekeeping routine.

I draw in the required selected voided transactions into a datatable using a union query, which then allows access to the required records.

The problem comes when I try to delete them as from this post shows....I cannot delete based on a union query