Two inserts jam

[IxiRancid]

I'm 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 doesn't 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 that's 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. Let's 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]

I'm not sure why it returns -1 instead of the actual rowcount. If you can't 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. You'll have to use a method other than ExcecuteNonQuery - I'd 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 I'll 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 it's a once-per-day thing, so this workaround is acceptable.

 

Thanks!