Two inserts jam

[IxiRancid]

Im in a wee bit a jam. I use INSERT SELECT statement with an OleDb Command (see code below). This SELECT statement seeks from two different tables. My problem is this:
if the first table(s) contain data and the ExecuteNonQuery is done the other INSERT SELECT shuold be skipped.

How can I check if the first cmd.ExecutenonQuery has inserted some actual data? It performs the Execute as if there is data, but actually doesnt insert anything.

        Try
            OleDbConnection1.Open()

            For Each item As String In ListBox2.Items


                Dim cmd As OleDbCommand
                Dim insrt_str As String
                insrt_str = "INSERT INTO NMYLIB.BMNS_PF (CSCDNR, CSCHDNAME, B00CA1, B00CA2) SELECT CSCDNR, CSCHDNAME, F54A01, F54A02 FROM LBZVD02.CDCS00, LBEOD03.BMF00P, LBEOD03.BMF40P, LBEOD03.SDF99P, LBEOD03.CAFIXP, LBEOD03.CAF54P WHERE CSACCOPU = B40OPU AND CSIBSACC = B40ACC AND B40CLT = B00CLT AND CSCDTYPE = ATMC AND CSCDSTAT = *ACT AND B40STS <> C AND CSIBSACC = FIXTAC AND B00CLT = F54CLT AND F54RPT = 112N AND F54SEQ = 1 AND CSCDNR = 00005940" & item & ""
                cmd = New OleDbCommand(insrt_str, OleDbConnection1)
                cmd.ExecuteNonQuery()
if actual data inserted skip this next chunk of code
                Dim cmd1 As OleDbCommand
                Dim insrt_str1 As String
                insrt_str1 = "INSERT INTO NMYLIB.BMNS_PF (CSCDNR, CSCHDNAME, B00CA1, B00CA2) SELECT CSCDNR, CSCHDNAME, b00ca1, b00ca2 FROM LBZVD02.CDCS00, LBDATA.LMF00P, LBEOD03.BMF40P, LBEOD03.CAFIXP WHERE CSACCOPU = B40OPU AND CSIBSACC = B40ACC AND B40CLT = B00CLT AND CSCDTYPE = ATMC AND CSCDSTAT = *ACT AND B40STS NOT IN (C) AND csibsacc = FIXTAC AND CSCDNR = 00005940" & item & ""
                cmd1 = New OleDbCommand(insrt_str1, OleDbConnection1)
                cmd1.ExecuteNonQuery()


            Next
            OleDbConnection1.Close()
        Catch ex As Exception


        End Try

 

[PlausiblyDamp]

Check the return value of cmd.ExecuteNonQuery - it returns the number of rows modified, if 0 then no inserts took place.

 

[IxiRancid]

tried rAffected = cmd.ExecuteNonQuery but always got -1.
I checked some Google and what I found was that it return -1 for all except Insert, Delete, Update. Now my Insert uses a Select. Probably thats the cause. I tried changing SELECT DISTINCT but nothing changed.

Any other ideas?

 

[IxiRancid]

Well, I spent way too much time on this stuff, so I just made a SLOW workaround. Lets leave this thread open for any suggestions

This is the workaround bytheway:

        Try
            OleDbConnection1.Open()

            For Each item As String In ListBox2.Items

                Dim dr As OleDbDataReader
                Dim cmdDR As OleDbCommand = New OleDbCommand("SELECT DISTINCT CSCDNR, CSCHDNAME, F54A01, F54A02 FROM LBZVD02.CDCS00, LBEOD03.BMF00P, LBEOD03.BMF40P, LBEOD03.SDF99P, LBEOD03.CAFIXP, LBEOD03.CAF54P WHERE CSACCOPU = B40OPU AND CSIBSACC = B40ACC AND B40CLT = B00CLT AND CSCDTYPE = ATMC AND CSCDSTAT = *ACT AND B40STS <> C AND CSIBSACC = FIXTAC AND B00CLT = F54CLT AND F54RPT = 112N AND F54SEQ = 1 AND CSCDNR = 00005940" & item & "", OleDbConnection1)
                dr = cmdDR.ExecuteReader

                Dim cmd As OleDbCommand
                Dim insrt_str As String
                cmd = New OleDbCommand
                Dim cmd1 As OleDbCommand
                Dim insrt_str1 As String

                If dr.HasRows Then
                    dr.Close()
                    With cmd
                        .CommandType = CommandType.Text
                        .CommandText = "INSERT INTO NMYLIB.BMNS_PF (CSCDNR, CSCHDNAME, B00CA1, B00CA2) SELECT DISTINCT CSCDNR, CSCHDNAME, F54A01, F54A02 FROM LBZVD02.CDCS00, LBEOD03.BMF00P, LBEOD03.BMF40P, LBEOD03.SDF99P, LBEOD03.CAFIXP, LBEOD03.CAF54P WHERE CSACCOPU = B40OPU AND CSIBSACC = B40ACC AND B40CLT = B00CLT AND CSCDTYPE = ATMC AND CSCDSTAT = *ACT AND B40STS <> C AND CSIBSACC = FIXTAC AND B00CLT = F54CLT AND F54RPT = 112N AND F54SEQ = 1 AND CSCDNR = 00005940" & item & ""
                        .Connection = OleDbConnection1
                    End With
                    cmd.ExecuteNonQuery()
                Else
                    dr.Close()
                    insrt_str1 = "INSERT INTO NMYLIB.BMNS_PF (CSCDNR, CSCHDNAME, B00CA1, B00CA2) SELECT CSCDNR, CSCHDNAME, b00ca1, b00ca2 FROM LBZVD02.CDCS00, LBDATA.LMF00P, LBEOD03.BMF40P, LBEOD03.CAFIXP WHERE CSACCOPU = B40OPU AND CSIBSACC = B40ACC AND B40CLT = B00CLT AND CSCDTYPE = ATMC AND CSCDSTAT = *ACT AND B40STS NOT IN (C) AND csibsacc = FIXTAC AND CSCDNR = 00005940" & item & ""
                    cmd1 = New OleDbCommand(insrt_str1, OleDbConnection1)
                    cmd1.ExecuteNonQuery()
                End If

            Next
            OleDbConnection1.Close()
        Catch ex As Exception
            OleDbConnection1.Close()
            MsgBox(ex.ToString)
        End Try

 

[Nerseus]

Im not sure why it returns -1 instead of the actual rowcount. If you cant get that to work, you can always change your SQL to select the rowcount after the INSERT. If this is SQL Server you can put these all in one string. Youll have to use a method other than ExcecuteNonQuery - Id suggest ExecuteScalar.
In the code below, I snipped the INSERT statement down and appended a SELECT to the INSERT.

With cmd
    .CommandType = CommandType.Text
    .CommandText = "INSERT INTO NMYLIB.BMNS_PF (...) SELECT ..."
    [b].CommandText = .CommandText & " SELECT @@ROWCOUNT"[/b]
    .Connection = OleDbConnection1
End With

-ner

 

[IxiRancid]

I see your solution. Not at work currently, but Ill check it.
However the database is DB2, accessed by OleDb from iSeries Client Access. And the thing is that this query seeks in 6 different files, some in different libraries.
But on the other hand its a once-per-day thing, so this workaround is acceptable.

Thanks!