MS Access Bulk Insert exits app
- by Brij
In the web service, I have to bulk insert data in MS Access database.
First, There was single complex Insert-Select query,There was no error but app exited after inserting some records.
I have divided the query to make it simple and using linq to remove complexity of query. now I am inserting records using for loop. there are approx 10000 records. Again, same problem. I put a breakpoint after loop, but No hit.
I have used try catch, but no error found.
For Each item In lstSelDel
Try
qry = String.Format("insert into Table(Id,link,file1,file2,pID) values ({0},""{1}"",""{2}"",""{3}"",{4})", item.WebInfoID, item.Links, item.Name, item.pName, pDateID)
DAL.ExecN(qry)
Catch ex As Exception
Throw ex
End Try
Next
Public Shared Function ExecN(ByVal SQL As String) As Integer
Dim ret As Integer = -1
Dim nowConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "DataBase\\mydatabase.mdb;"
Dim nowCon As System.Data.OleDb.OleDbConnection
nowCon = New System.Data.OleDb.OleDbConnection(nowConString)
Dim cmd As New OleDb.OleDbCommand(SQL, nowCon)
nowCon.Open()
ret = cmd.ExecuteNonQuery()
nowCon.Close()
nowCon.Dispose()
cmd.Dispose()
Return ret
End Function
After exiting app, I see w3wp.exe uses more than 50% of Memory.
Any idea, What is going wrong?
Is there any limitation of MS Access?
