sSQL Update Deleting more than it should

[rwn]

I have this sSQL Update formula. The problem is the HOLD field either will contain a Y or a N. I want to keep all the Y. But when it runs against each row it only leaves the first Y and not any others.

icount = 0 'set the value to count the number of tickets
DataSet1.AcceptChanges()
For Each rowX As DataRow In DataSet1.Lot_Tracking_Hist.Rows
sSQL = "INSERT INTO [Lab Table] (LotNumber, TDate, FGNumber, Shift," & _
"TTime, Qty, OrderNumber, Batch, Code, Hold)" & _
" Values " & _
"('" & rowX.Item("LOTNUM_72").ToString & "'," & _
"#" & rowX.Item("TNXDTE_72") & "#," & _
"'" & rowX.Item("PRTNUM_72").ToString & "'," & _
"'" & rowX.Item("USRNAM_72").ToString & "'," & _
"'" & rowX.Item("TNXTIM_72").ToString & "'," & _
"'" & rowX.Item("TNXQTY_72").ToString & "'," & _
"'" & rowX.Item("ORDNUM_72").ToString & "'," & _
"'" & rowX.Item("UDFREF_72").ToString.Substring(0, 6) & "'," & _
"'" & rowX.Item("UDFREF_72").ToString.Substring(rowX.Item("UDFREF_72").ToString.Length - 19, 9) & "'," & _
"'" & rowX.Item("SUPCDE_01") & "')"

With objCommand ' this portion identifies the connection that will supply the data to be entered during the commands
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

sSQL = "UPDATE [Lab Table] SET [Lab Table].Hold= NULL WHERE ((([Lab Table].Hold)='N'));"


With objCommand
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

 

[Melagan]

And this?

icount = 0 'set the value to count the number of tickets
            DataSet1.AcceptChanges()
            For Each rowX As DataRow In DataSet1.Lot_Tracking_Hist.Rows
                sSQL = "INSERT INTO [Lab Table] (LotNumber, TDate, FGNumber, Shift," & _
                 "TTime, Qty, OrderNumber, Batch, Code, Hold)" & _
                   " Values " & _
                  "('" & rowX.Item("LOTNUM_72").ToString & "'," & _
                  "#" & rowX.Item("TNXDTE_72") & "#," & _
                  "'" & rowX.Item("PRTNUM_72").ToString & "'," & _
                  "'" & rowX.Item("USRNAM_72").ToString & "'," & _
                  "'" & rowX.Item("TNXTIM_72").ToString & "'," & _
                  "'" & rowX.Item("TNXQTY_72").ToString & "'," & _
                  "'" & rowX.Item("ORDNUM_72").ToString & "'," & _
                  "'" & rowX.Item("UDFREF_72").ToString.Substring(0, 6) & "'," & _
                  "'" & rowX.Item("UDFREF_72").ToString.Substring(rowX.Item("UDFREF_72").ToString.Length - 19, 9) & "'," & _
                  "'" & rowX.Item("SUPCDE_01") & "')"
[tab][tab][tab][COLOR=red]Next[/color]

                With objCommand ' this portion identifies the connection that will supply the data to be entered during the commands
                    .Connection = oOleDbConnection
                    .CommandText = sSQL
                    .CommandType = CommandType.Text
                    .ExecuteNonQuery()
                End With

  sSQL = "UPDATE [Lab Table] SET [Lab Table].Hold= NULL  WHERE ((([Lab Table].Hold)='N'));"


            With objCommand
                .Connection = oOleDbConnection
                .CommandText = sSQL
                .CommandType = CommandType.Text
                .ExecuteNonQuery()
            End With

How did this compile without the NEXT in your FOR EACH loop?

~Melagan
______
"It's never too late to become what you might have been.

 

[rwn]

Sorry didn't include all of the code.

icount = 0 'set the value to count the number of tickets
DataSet1.AcceptChanges()
For Each rowX As DataRow In DataSet1.Lot_Tracking_Hist.Rows
sSQL = "INSERT INTO [Lab Table] (LotNumber, TDate, FGNumber, Shift," & _
"TTime, Qty, OrderNumber, Batch, Code, Hold)" & _
" Values " & _
"('" & rowX.Item("LOTNUM_72").ToString & "'," & _
"#" & rowX.Item("TNXDTE_72") & "#," & _
"'" & rowX.Item("PRTNUM_72").ToString & "'," & _
"'" & rowX.Item("USRNAM_72").ToString & "'," & _
"'" & rowX.Item("TNXTIM_72").ToString & "'," & _
"'" & rowX.Item("TNXQTY_72").ToString & "'," & _
"'" & rowX.Item("ORDNUM_72").ToString & "'," & _
"'" & rowX.Item("UDFREF_72").ToString.Substring(0, 6) & "'," & _
"'" & rowX.Item("UDFREF_72").ToString.Substring(rowX.Item("UDFREF_72").ToString.Length - 19, 9) & "'," & _
"'" & rowX.Item("SUPCDE_01") & "')"

With objCommand ' this portion identifies the connection that will supply the data to be entered during the commands
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

'Count the number of entries and display them. Lets create a message box and display the results of data entered
icount += 1 'total number of tickets processed

Next

frmimport.Close() 'to close subform
If icount > 1 Then ' this section indicates the number of tickets available to be entered
MessageBox.Show("Tickets Entered - Press 'OK' to remove Duplicates, process will take a few moments.")

frmgathertickets.Show() 'to display subform "Refreshing View"

ElseIf MessageBox.Show(icount, "No Tickets Available") Then

End If ' this ends the counting and display of tickets

sSQL = "DELETE [Lab Table].TDate, [Lab Table].ID, [Lab Table].Qty, * FROM [Lab Table] WHERE ((([Lab Table].TDate) " & _
" Between Date()-10 And Date()) AND (([Lab Table].ID) Not In (SELECT Min(ID) as MinID FROM [Lab Table] " & _
"WHERE ([Lab Table].TDate Between Date()-10 And Date()) GROUP BY [Lab Table].LotNumber)) AND (([Lab Table].Qty)=[Lab Table].[Qty]));"

With objCommand
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With


sSQL = "UPDATE [Lab Table] SET [Lab Table].Hold= NULL WHERE ((([Lab Table].Hold)='N'));"

With objCommand
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With


Catch ex As Exception ' this Catch is required but the ex As Exception and MessageBox.Show(ex.ToString) below are only for error messages
MessageBox.Show(ex.ToString)

End Try
frmgathertickets.Close() 'to close delete subform
End Sub

 

[Melagan]

Well, I can't find anything wrong with the SQL on the surface. I wonder why you're calling DataSet1.AcceptChanges()? Have you already added information to the DataSet before AcceptChanges is called? Either way...perhaps have a look at the DataSet.Update() method used in conjunction with the OleDbCommandBuilder object under the System.Data.OleDB namespace.

Or..someone else can find the SQL error that I may have missed =) I think this is really more of a VB.NET / ADO.NET issue though.

~Melagan
______
"It's never too late to become what you might have been.

 

[rwn]

Thank you for reviewing. If I comment out:
sSQL = "UPDATE [Lab Table] SET [Lab Table].Hold= NULL WHERE ((([Lab Table].Hold)='N'));"

With objCommand
.Connection = oOleDbConnection
.CommandText = sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

The the appropriatte rows that should have a Y in the HOLD field are correct. Just when this is uncommented does it reomve all the Y and it should only remove the N.