DELETE Query Error - OleDbException - Record is deleted

Question

Hello all, and thank you in advance for any help you may be able to provide.  This might not be the correct forum, but it is an SQL related problem I'm having.

Symptom: DELETE query doesnt execute - OleDbException was unhandled - Record is deleted

Environment: Win7, VS2010, Office2007, DataSource: Access 2007 (Nwind.accdb)

I have a single form with 3 buttons, and 1 doesn't seem to like my SQL syntax.

1. Review (Query DB, display result in DataGridView) - Working
2. Email (Query DB, Export to Excel, Email from Outlook) - Working
3. Clear (Query DB, and DELETE records) - Not Working

The queries for each are basically the same.

Review & Export/Email Button Code

Dim cmd As OleDbCommand = New OleDbCommand("SELECT B.PRODUCT, B.SHIPTO, B.TIMEID " & _
	"FROM (SELECT PRODUCT, SHIPTO, TIMEID " & _
	"FROM tblFactSales " & _
	"WHERE (BILLTO = 'INPUT_BILLTO') AND (BRANCHPLANT = 'INPUT_BRANCHPLANT') AND (FRTHANDLE = 'INPUT_FRTHANDLE') AND (DATATYPE = 'FORECAST') AND (TIMEID > '" & fRng & "' )" & _
	"AND (SIGNEDDATA >= - .01) AND (SIGNEDDATA <= .01) AND (SALESDATA = 'short_tons')) AS A " & _
	"INNER JOIN " & _
	"(SELECT PRODUCT, SHIPTO, TIMEID " & _
	"FROM tblFactSales AS tblFactSales_1 " & _
	"WHERE (DATATYPE = 'FORECAST') AND (TIMEID > '" & fRng & "' )) AS B " & _
	"ON A.PRODUCT = B.PRODUCT AND A.SHIPTO = B.SHIPTO AND A.TIMEID = B.TIMEID", con)

Clear Button Code

Dim cmd As OleDbCommand = New OleDbCommand("DELETE B.* " & _
	"FROM ((SELECT PRODUCT, SHIPTO, TIMEID " & _
	"FROM tblFactSales " & _
	"WHERE (BILLTO = 'INPUT_BILLTO') AND (BRANCHPLANT = 'INPUT_BRANCHPLANT') AND (FRTHANDLE = 'INPUT_FRTHANDLE') AND (DATATYPE = 'MAY_FCST') AND (TIMEID > '" & fRng & "' )" & _
	"AND (SIGNEDDATA >= - .01) AND (SIGNEDDATA <= .01) AND (SALESDATA = 'short_tons')) AS A " & _
	"INNER JOIN " & _
	"(SELECT PRODUCT, SHIPTO, TIMEID " & _
	"FROM tblFactSales AS tblFactSales_1 " & _
	"WHERE (DATATYPE = 'MAY_FCST') AND (TIMEID > '" & fRng & "' )) AS B " & _
	"ON A.PRODUCT = B.PRODUCT AND A.SHIPTO = B.SHIPTO AND A.TIMEID = B.TIMEID)", con)

Again, any help would be greatly appreciated.

Answer 1

You wrote DELETE command the same way you wrote the SELECT - you can not do this.

From which table and by which criteria you want to delete rows?

I suggest you to start from writing a parameterized DELETE query in SSMS. Once you get it working, use exactly the same query from your code and keep parameters.

Never embed parameter valued inside the query.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I want to delete the rows resulting from the select.

Basically, here's the project in a nutshell...

1. user clicks "Review" which displays the records to be deleted
2. user clicks "Email" to send report for approval.
3. once approval is complete
4. user clicks "Clear" to remove the records.

I'm still learning, so please forgive me.

Answer 3

Does your SELECT statement work? Can you post the SELECT statement only?

I am trying to understand it:

SELECT B.PRODUCT, B.SHIPTO, B.TIMEID
	FROM (SELECT PRODUCT, SHIPTO, TIMEID 
	FROM tblFactSales 
	WHERE BILLTO = 'INPUT_BILLTO' AND BRANCHPLANT = 'INPUT_BRANCHPLANT' AND 
        FRTHANDLE = 'INPUT_FRTHANDLE' AND DATATYPE = 'FORECAST' AND TIMEID > @StartTimeID 
	AND SIGNEDDATA between .01 AND .01 AND SALESDATA = 'short_tons') AS A 
	INNER JOIN 
	(SELECT PRODUCT, SHIPTO, TIMEID 
	FROM tblFactSales AS tblFactSales_1 
	WHERE DATATYPE = 'FORECAST' AND TIMEID > @StartTimeID) AS B 
	ON A.PRODUCT = B.PRODUCT AND A.SHIPTO = B.SHIPTO AND A.TIMEID = B.TIMEID

So, you select Product, ShipTo, TimeID fields.

To delete these rows from the tblFactSales table you can use this:

;with cteToDelete AS (SELECT B.PRODUCT, B.SHIPTO, B.TIMEID
	FROM (SELECT PRODUCT, SHIPTO, TIMEID 
	FROM tblFactSales 
	WHERE BILLTO = 'INPUT_BILLTO' AND BRANCHPLANT = 'INPUT_BRANCHPLANT' AND 

        FRTHANDLE = 'INPUT_FRTHANDLE' AND DATATYPE = 'FORECAST' AND TIMEID > @StartTimeID 
	AND SIGNEDDATA between .01 AND .01 AND SALESDATA = 'short_tons') AS A 
	INNER JOIN 
	(SELECT PRODUCT, SHIPTO, TIMEID 
	FROM tblFactSales AS tblFactSales_1 
	WHERE DATATYPE = 'FORECAST' AND TIMEID > @StartTimeID) AS B 
	ON A.PRODUCT = B.PRODUCT AND A.SHIPTO = B.SHIPTO AND A.TIMEID = B.TIMEID)

DELETE B FROM tblFactSales B
WHERE EXISTS (select 1 from cteToDelete Del
WHERE Del.Product = B.Product and Del.ShipTo = B.ShipTo
and Del.TimeID = B.TimeID)

I suggest to turn both into stored procedures and call them from your code by passing @StartTimeID parameter.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

The SELECT was the first Code sample I posted, right above the DELETE.

Are you saying that this cant be done through VB, or it would be better as a stored procedure called from VB?

Again, I'm still learning.

Answer 5

You can invoke direct statement from VB also (just make sure you're using parameter), but I personally dislike embedded queries into VB.NET or C# code and prefer to use stored procedures for ease of maintenance and plan re-use.

If you prefer to keep it as inline query, keep it. I just wrote it as a whole query. Unfortunately, unlike C#, I don't think there is a way to write long query as one multi-line command, so most likely we will need to split it again into portions as your original was (but keep the parameter).

The second version shows the delete statement - again, you can split it into multiple strings, just keep the parameter.

My preference would be to put this statement into stored procedure.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Oops, I just re-read your original message - you're using Access, not SQL Server. In Access you don't have CTE, so you most likely will need to change CTE into derived table.

Also, if you're using Access, it's better to ask in the Access forum than SQL Server, as here we assume T-SQL dialect which is not the same for Access.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

The end result will be SQL.  I'm using Access as my development db, so I dont mess anything up.

I will see what i can figure out.  Thanks for all your help.  :)

Answer 8

I'll use like this:

DELETE FROM MYTABLE1

WHERE MYTABLE1.K1=DELETEKEY1 AND MYTABLE.KEY2=DELETEKEY2

FOR DELETEKEY'S one can use joins to get keys to delete.

---

Please Mark as answer if this answers your question Or Mark as helpful if you found this post was helpful. Trilok Negi

Answer 9

The end result will be SQL.  I'm using Access as my development db, so I dont mess anything up.

There are too many differences between Access and SQL Server for this to be a good idea. Only do this, if you need to support both SQL Server and Access. (Which will be quite a challange.)

If you want a local database to play with, download and install SQL Server Express.

---

Erland Sommarskog, SQL Server MVP, [e-mail]