Group Statement Error

[anon1971]

I am trying to have this statement delete mulipule records based on a project ID. Any idea would be great

sql = "SELECT ProjectID FROM [Notes] WHERE [Projects].[ProjectID]=" & session("DebtorID") & GROUP BY ProjectID
rs.open sql, conn, 3, 3
rs.delete
rs.close

 

[foxbox]

this selects the non-unque projectID's:

select projectID, count(*)
from MyTable
Group by projectID
having count(*) > 1


in your SQL i see "FROM Notes"
and WHERE [projects]...?

 

[foxbox]

and why not simple delete records this way:

cSQL = "DELETE FROM [Projects] WHERE [Projects].[ProjectID]=" & session("DebtorID")

conn.execute (cSQL)

?

 

[gmmastros]

You're missing quotes.

[tt][blue]
sql = "SELECT ProjectID FROM [Notes] WHERE [Projects].[ProjectID]=" & session("DebtorID") & [!]"[/!] GROUP BY ProjectID[!]"[/!]
[/blue][/tt]

Still... there's something a little hinky about your query. You appear to be selecting from a notes table, but your where clause references [[!]Projects[/!]].[ProjectID][/!], which appears to be another/different table.

If you explain a little more about your table structures and how these two tables relate to each other, I'm sure we can find a better way. You see, there's really no reason to return a recordset to ASP just so you can delete the data. Instead, you should be able to write a single query that does all the work for. I can help with this, but I would need to understand how the 2 tables relate to each other.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon1971]

Thanks guys I choose foxbox way and it works great. However I did have the code messed up gmmastros so thanks to you both.


SQL = "DELETE FROM [NotesD] WHERE [NotesD].[ProjectID]=" & session("DebtorID")

conn.execute (SQL)
conn.close

 

[anon1971]

One more thing if there is a quick solution to how I could prompt yes or no to the user asking them do they want to really delete this record that would be awsome

 

[foxbox]

like this:

<a href="delete.asp" onClick="return confirm('Are you sure you want to delete?');">Delete</a>

 

[anon1971]

Thanks I could not rap my head arround that for nothing. No sleep don't help thanks guys.