Thread: query db, delete based on results

ok, I have what I hope is an easy problem to fix. i'm try to query a db, store to a temp table, and then query the same db, inner joined with first query. please help. I'm currently getting "access cant find table/query" error. I've tried putting quotes around tmp, and that doesnt work either.

Code:

'Set/Open Connection
Dim con As OleDbConnection =New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb")
con.Open()

'Set TIMEID
Dim Yr, Mnth, fRng As String
Yr = Year(Now)
 Mnth = Format(Month(Now),"00")
fRng = Yr + Mnth +"00"

'Query 1/Fill Temp Table (tmp)
Dim cmdA As OleDbCommand =New OleDbCommand("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')", con)

Dim sda As OleDbDataAdapter =New OleDbDataAdapter(cmdA)
Dim ds As DataSet =New DataSet()
sda.Fill(ds,"tmp")

'Query 2, joined with Query 1
Dim cmdB As OleDbCommand =New OleDbCommand("DELETE tblFactSales "& _
"FROM tmp INNER JOIN tblFactSales "& _
"ON tmp.PRODUCT = tblFactSales.PRODUCT AND tmp.SHIPTO = tblFactSales.SHIPTO AND tmp.TIMEID = tblFactSales.TIMEID "& _
"WHERE (DATATYPE = 'FORECAST') AND (TIMEID > '"& fRng &"' )", con)

'Execute Queries
cmdA.ExecuteNonQuery()
cmdB.ExecuteNonQuery()

'Clean Up
cmdA.Dispose()
cmdB.Dispose()
con.Close()
GC.Collect()

'Confirmation
MessageBox.Show("Records Removed Successfully.","Clear Complete", _
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1) 

Can't find it cos it doesn't exist. You're using it as a source table but you've never actually created it or filled it. You execute cmdA after you've attempted to use its results in any case.

havent totally fixed it, but I think I'm getting closer. I'm getting a "Record is deleted" error now on cmdB.ExecuteNonQuery. It creates & populates the tmp table, but nothing gets deleted from the main table.

Private Sub cmdDelete_Click(sender As System.Object, e As System.EventArgs) Handles cmdDelete.Click
'Set/Open Connection
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb")
con.Open()

'Set TIMEID
Dim Yr, Mnth, fRng As String
Yr = Year(Now)
Mnth = Format(Month(Now), "00")
fRng = Yr + Mnth + "00"

'SELECT, to find unique IDs (PRODUCT, SHIPTO, TIMEID, DATATYPE)
Dim cmdA As OleDbCommand = New OleDbCommand("SELECT PRODUCT, SHIPTO, TIMEID, DATATYPE INTO tmp IN 'C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb'" & _
"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')", con)

'DELETE, joined with cmdA results
Dim cmdB As OleDbCommand = New OleDbCommand("DELETE tblFactSales.* " & _
"FROM tblFactSales INNER JOIN tmp T " & _
"ON tblFactSales.PRODUCT=T.PRODUCT AND tblFactSales.SHIPTO=T.SHIPTO AND tblFactSales.TIMEID=T.TIMEID", con)

'Execute Queries
cmdA.ExecuteNonQuery()
cmdB.ExecuteNonQuery()

'Clean Up
cmdA.Dispose()
cmdB.Dispose()
con.Close()
GC.Collect()

'Confirmation
MessageBox.Show("Records Removed Successfully.", "Clear Complete", _
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
End Sub