I've got an app I'm writing that has logic like this:
1. Check the database. If any of the items on List X are already in there,
post an error stating which items are duplicates.
2. Add the non-duplicate items to the database.
Unfortunately, it's checking the steps in reverse order for some reason, so
that it adds the items to the db, and then reports all of them as
duplicates (because now they are in the db). Here's my relevant code (SQL
function not included, since it's not relevant... when I run the SQL
function by itself, it operates fine):
Private Sub btnQuickStart_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnQuickStart.Click
[...]
CheckExisting(ProductList)
AddNewItems(ProductList)
End Sub
Private Sub CheckExisting(ByVal ProductList As String)
Dim strSQL As String = "SELECT * FROM fnSearchBySuite(" &
ProductList & ") WHERE SuiteCount = 1 AND SuiteID IN (SELECT SuiteID FROM
tblItems WHERE PresentationID = " & Request.QueryString("ID") & ")"
Dim Cmd As New SqlCommand(strSQL, cnSalesWiz)
Dim DA As New SqlDataAdapter(Cmd)
Dim DT As New DataTable
Dim DR As DataRow
DA.Fill(DT)
If DT.Rows.Count > 0 Then
DuplicateResults.InnerHtml = ""
DuplicateResults.InnerHtml &= "<span class=MainHeader>Duplicate
Items Detected</span>"
DuplicateResults.InnerHtml &= "The following groups were
already in your presentation, and have been ignored:<br><br>"
For Each DR In DT.Rows
DuplicateResults.InnerHtml &= DR("SuiteName") & "<br>"
Next
DuplicateResults.InnerHtml &= "<br><br>"
DuplicateResults.Visible = True
End If
End Sub
Private Sub AddNewItems(ByVal ProductList As String)
Dim strSQL As String = "SELECT * FROM fnSearchBySuite(" &
ProductList & ") WHERE SuiteCount = 1 and SuiteID NOT IN (SELECT SuiteID
FROM tblItems WHERE PresentationID = " & Request.QueryString("ID") & ")"
Dim Cmd As New SqlCommand(strSQL, cnSalesWiz)
Dim DA As New SqlDataAdapter(Cmd)
Dim DT As New DataTable
Dim DR As DataRow
DA.Fill(DT)
If DT.Rows.Count > 0 Then
AddedToPresentation.InnerHtml = ""
AddedToPresentation.InnerHtml &= "The following groups have
been added to your presentation:<br><br>"
Dim cmdAddProduct As New SqlCommand
With cmdAddProduct
.Connection = cnSalesWiz
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@PresentationID", SqlDbType.Int)
.Parameters.Add("@SuiteID", SqlDbType.Int)
End With
For Each DR In DT.Rows
With cmdAddProduct
.CommandText = "prAssignProduct"
.Parameters("@PresentationID").Value = CInt
(Request.QueryString("ID"))
.Parameters("@SuiteID").Value = CInt(DR("SuiteID"))
.ExecuteNonQuery()
End With
AddedToPresentation.InnerHtml &= DR("SuiteName") & "<br>"
Next
AddedToPresentation.InnerHtml &= "<br><br>"
AddedToPresentation.Visible = True
End If
End Sub