insert multiple rows with one query using ms access db

Question

I am trying to get multiple rows into a table hence my attempt to get the row number and put it into a for loop, the countC is exactly the same number of rows as the select statement, so the issue is not there

I'm using an oledb connection as my code is in vb asp.net but my database is in ms access 2003

For c As Integer = 1 To countC
        Dim cmdstring As String
        cmdstring = " INSERT INTO [KN - ProductionMachineAllocation] (BatchNo, ComponentID)       
                  SELECT POH.BatchNo, SSCDD.ComponentID
                FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY BatchNo ASC) AS rownumber
                    ([KN - ProductionOrderHeader] AS POH
                    INNER JOIN [FG - End Product Codes] AS EPC
                        ON POH.ProductID = EPC.ProductID)
                    INNER JOIN ([KN - ProductionOrderDetails] AS POD
                    INNER JOIN [FG - Style Size Comp Def Details]  AS SSCDD
                        ON POD.SizeID = SSCDD.SizeID) 
                        ON (POH.BatchNo = POD.BatchNo) 
                            AND (EPC.StyleID = SSCDD.StyleID)
                    WHERE POH.BatchNo = '" & BatchNo & "'
                     )  AS temptablename
                WHERE rownumber IN (" & c & ");"
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb")
        Dim cmd As New OleDbCommand(cmdstring)
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    Next

I found out that ms access doesn't support ROW_NUMBER() so I need to find another going through each row since ms access doesn't support multi row insert by insert into select statement such as mine, any suggestions around my problem?

Answer 1

Most databases are able to do all this work much more efficiently entirely in the database. Certainly in SQL Server I could get entire thing down to a single query. Access is a little different, since vbscript is its procedural language, rather than something more like t-sql. There's still probably a way to do it, but since what you have works, we can at least focus on making that better.

GridViews are visual constructs that will use up extra memory and resources. If Access won't do a real INSERT/SELECT, you can at least read direct from the previous result set into your insert. You can also improve on this significantly by using parameters and re-using a single open connection for all the inserts:

Dim cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb"
Dim SQLDown As String = _
     "SELECT DISTINCT POH.BatchNo, SSCDD.ComponentID
      FROM ([KN - ProductionOrderHeader] AS POH
      INNER Join [FG - End Product Codes] AS EPC
        On POH.ProductID = EPC.ProductID)
      INNER Join([KN - ProductionOrderDetails] AS POD
      INNER Join [FG - Style Size Comp Def Details]  AS SSCDD
           On POD.SizeID = SSCDD.SizeID) 
           On (POH.BatchNo = POD.BatchNo) 
                And (EPC.StyleID = SSCDD.StyleID)
     WHERE POH.BatchNo = ? "
Dim SQLUp As String = _
    " INSERT INTO [KN - ProductionMachineAllocation] 
      (BatchNo, ComponentID) 
       VALUES( ?, ? )"

Dim dt As New DataTable
Using con As New OleDbConnection(cnString), _
      cmd As New OleDbCommand(SQLDown, con)

    'Guessing at parameter type/length here.
    'Use the actual column type and size from your DB
    cmd.Parameters.Add("@BatchNo", OleDbType.VarWChar, 10).Value = BatchNo

    con.Open()
    dt.Load(cmd.ExecuteReader())
End Using

Using con As New OleDbConnection(cnString), _
      cmd As New OleDbCommand(SqlUp, con)

    'Guessing at parameter types/lengths again
    cmd.Parameters.Add("@BatchNo", OleDbType.VarWChar, 10)
    cmd.Parameters.Add("@ComponentID", OleDbType.Integer)

    'Connection is managed *outside of the loop*. Only one object created, only one negotiation with DB
    con.Open()
    For Each row As DataRow In dt.Rows
          cmd.Parameters(0).Value = row(0)
          cmd.Parameters(1).Value = row(1)
          cmd.ExecuteNonQuery()
    Next
End Using

Normally, with any ADO.Net provider you do not re-use your connection or command objects. You want a new connection object for every query sent to the DB to allow connection pooling to work correctly. Using the connection in a tight loop like this for the same query is one of the few exceptions.

I might be able to improve further by sticking with the active DataReader, rather than first loading it into a DataTable. That would allow us to avoid loading the entire result set into memory. You would only ever need one record in memory at a time. Certainly this would work for Sql Server. However, Access was designed mainly as a single-user database. It doesn't really like multiple active connections at once, and I'm not sure how it would respond.

It would also be nice to be able to do all of this work in a transactional way, where there's never any risk of it failing part way through the loop and getting stuck with half the updates. Sql Server would handle this via a single INSERT/SELECT query or with an explicit transaction. But, again, this isn't the kind of the Access is designed for. It probably does have a way to do this, but I'm not familiar with it.

Answer 2

OK SO I finally found a way around it, it's a bit of a long process but basically I loaded the SELECT statement(with multiple rows) into a gridview table and the used a for loop to insert it into my insert into statement. bellow is my code:

Displaying into a table

    Dim Adapter As New OleDbDataAdapter
    Dim Data As New DataTable
    Dim SQL As String
    Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb")
    Dim cmd As New OleDbCommand()
    grdvmachincomp.Visible = false
    SQL = "SELECT DISTINCT POH.BatchNo, SSCDD.ComponentID
                    FROM ([KN - ProductionOrderHeader] AS POH
                    INNER Join [FG - End Product Codes] AS EPC
                        On POH.ProductID = EPC.ProductID)
                    INNER Join([KN - ProductionOrderDetails] AS POD
                    INNER Join [FG - Style Size Comp Def Details]  AS SSCDD
                        On POD.SizeID = SSCDD.SizeID) 
                        On (POH.BatchNo = POD.BatchNo) 
                            And (EPC.StyleID = SSCDD.StyleID)
                    WHERE POH.BatchNo = '" & BatchNo & "'"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = SQL

    Adapter.SelectCommand = cmd
    Adapter.Fill(Data)

    grdvmachincomp.DataSource = Data
    grdvmachincomp.DataBind()
    cmd.Connection.Close()

Insert into through for loop

For c As Integer = 0 To grdvmachincomp.Rows.Count - 1
        Dim cmdstring As String
        cmdstring = " INSERT INTO [KN - ProductionMachineAllocation] (BatchNo, ComponentID) VALUES('" & grdvmachincomp.Rows(c).Cells(0).Text & "', " & grdvmachincomp.Rows(c).Cells(1).Text & ");"
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb")
        Dim cmd As New OleDbCommand(cmdstring)
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    Next