mixing data source types in query...?

[ccbryan]

Hi folks...

Brief bit of background: I'm primarily using an IBM DB2 connection for my data, e.g. "Dim custDA2 As New IBM.Data.DB2.iSeries.iDB2DataAdapter." The db2 stuff is set to use a connection string in my project settings, so tablenames in queries that use custDA2 are naturally mapped to tables in the DB2 database. Fair enough, and it works fine. However, I want to construct a subquery that uses a dataset that I build within my form, something like:

"DELETE FROM ItemComp WHERE ItemComp_Item = '" & Trim(Me.lblProduct.Text) & "'" & _
   " and ItemComp_Comp NOT IN (SELECT Comp from " & ds.Tables("ProdCompList") & ")"

This code shows a design-time error "Operator '&' is not defined for types 'String' and 'System.Data.Datatable'."

The DB2 driver does support subqueries. this runs without error, for instance:

"DELETE FROM ItemComp WHERE ItemComp_Item= '" & Trim(Me.lblProduct.Text) & "'" & _
                " and ItemComp_Comp NOT IN (SELECT Comp from CompTable)"

Basically, I've pulled a list of components for a particular product into a dataset (ds.tables("ProdCompList") and I've allowed the user to edit that dataset, including deleting items. Now I need to apply any deletes back to the original datasource... but I have to refer to both a connectionstring datasource and to that local dataset.

Anyone have any ideas how to do this?

Thanks...

Chandler

 

[jmcilhinney]

Your SQL code is executed on your database, which knows nothing of your DataSet. What you need to do is extract the appropriate data from your local data, insert that into your SQL code and then pass that to your database. Your database then works with the data without any knowledge of where it came from. Exactly how you would do that depends on the query but, as you're using an IN clause, here's one I prepared earlier:

Using Parameters with an SQL IN Clause

 

[ccbryan]

Thanks jmcilhinney, I really appreciate your response. That got me going. Here's how I'm using it:

  'Now remove any rows that have been deleted
        Dim query As New StringBuilder("DELETE FROM InvComp WHERE Product = '" & Trim(Me.lblProduct.Text) & "' " & _
                                        "AND trim(Component) ")

        Select Case dsPersistent.Tables("ComponentList").Rows.Count
            Case 1

                query.Append("NOT = @MyValue")
                cmd.Parameters.AddWithValue("@Myvalue", dsPersistent.Tables("ComponentList").Rows.Item("ID"))
            Case Is > 1
                query.Append("NOT IN (")
                Dim paramName As String
                For index As Integer = 0 To dsPersistent.Tables("ComponentList").Rows.Count - 1 Step 1

                    paramName = "@MyValue" & index
                    If index > 0 Then

                        query.Append(", ")
                    End If
                    query.Append(paramName)
                    cmd.Parameters.AddWithValue(paramName, Trim(dsPersistent.Tables("ComponentList").Rows(index).Item("ID")))
                Next index
                query.Append(")")
        End Select

Chandler