query db, delete based on results (vb.net)

[JerrodYoung]

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:

Private Sub cmdDelete_Click(sender As System.Object, e As System.EventArgs) Handles cmdDelete.Click

    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()

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

    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")

    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)


    cmdA.ExecuteNonQuery()
    cmdB.ExecuteNonQuery()

    cmdA.Dispose()
    cmdB.Dispose()
    con.Close()
    GC.Collect()

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

[hericles]

The problem is that you aren't storing the result of the first query in a temporary table of the database, you're storing it in a dataset in memory. The database has no knowledge that the dataset exists.
What I don't understand is why you are selecting the data out first when you just want to delete it anyway. Why can't you just go straight to the delete step? Your dataset doesn't hold any information that tblFactSales doesn't.

[JerrodYoung]

latest code snippet has syntax error in cmdB

        Dim cmdA As OleDbCommand = New OleDbCommand("SELECT PRODUCT, SHIPTO, TIMEID 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)
        '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 FS " & _
                                                    "FROM tblFactSales as FS INNER JOIN tmp T" & _
                                                    "ON FS.PRODUCT=T.PRODUCT AND FS.SHIPTO=T.SHIPTO AND FS.TIMEID=T.TIMEID " & _
                                                    "WHERE (FS.DATATYPE = 'FORECAST') AND (FS.TIMEID > '" & fRng & "' )", con)

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

[hericles]

You're missing a space on the line break at "INNER JOIN tmp T" & "ON FS..."
Its joining it together as INNER JOIN tmp TON FS

[JerrodYoung]

yes, i did find that, but it wasnt the only issue.

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.

Updated Code:

    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

[JerrodYoung]

@hericles - the first query finds the unique records, and the DELETE removes all related records. is there an easier way? im new at this, so I'm open to trying something new.

[hericles]

It would appear there isn't. I tried to use a select statement in the where clause but you can't reference the same table that is being updated (deleted from).
Try removing the tblFactSales.*. A delete statement should just be:

DELETE FROM tblFactSales INNER JOIN tmp T 
ON tblFactSales.PRODUCT=T.PRODUCT AND tblFactSales.SHIPTO=T.SHIPTO AND tblFactSales.TIMEID=T.TIMEID

I'm not sure if that will be the problem however because if you are getting to the part where the message box appears then there are no syntax errors in your SQL.