ODBC - unable to allocate an environment handle

Question

Hi I am trying to insert data into Navision database from a DataTable. That DataTable contain around 5000 records, if the records count is less then it's working fine but record count is around 5000 I am getting this error.

ERROR - unable to allocate an environment handle.

This is the code I am using

Public Function InsertToHHTTransferLine(ByVal dtTransferLn As DataTable, ByVal hhtNumber As String) As Integer
  Dim result As Integer
  Dim cn As OdbcConnection
  Dim dtTransferLine As DataTable
  cn = New OdbcConnection(ConnStr)
  Dim SqlStr As String = ""
  Try
  cn.Open()
  dtTransferLine = dtTransferLn
  Dim DocType As String
  DocType = "Purchase"
  Dim cmd As OdbcCommand
  Dim hhtNo As String
  hhtNo = hhtNumber
  If dtTransferLine.Rows.Count > 0 Then
  For i As Integer = 0 To dtTransferLine.Rows.Count - 1
  If dtTransferLine.Rows(i)("DOC_TYPE").ToString() = "0" Then
  DocType = "Purchase"

  End If
  If dtTransferLine.Rows(i)("DOC_TYPE").ToString() = "1" Then
  DocType = "Transfer Receipt"

  End If
  If dtTransferLine.Rows(i)("DOC_TYPE").ToString() = "2" Then
  DocType = "Transfer Shipment"

  End If
  If dtTransferLine.Rows(i)("DOC_TYPE").ToString() = "3" Then
  DocType = "Stock Count"

  End If
  Try
  SqlStr = "INSERT INTO ""HHT & Navision Line""(""Document Type"",""Document No_"",""HHT No_"",""Line No_"",""Item No_"",""Document Quantity"",""Scan Quantity"",""Unit Price"",""Posted"") VALUES('" & DocType & "','" & dtTransferLine.Rows(i)("DOC_NO").ToString() & "','" & hhtNo & "','" & dtTransferLine.Rows(i)("LINE_NO").ToString() & "','" & dtTransferLine.Rows(i)("ITEM_NO").ToString() & "'," & dtTransferLine.Rows(i)("DOC_QTY").ToString() & "," & dtTransferLine.Rows(i)("SCAN_QTY").ToString() & "," & dtTransferLine.Rows(i)("UNIT_PRICE").ToString() & ",0)"
  cmd = New OdbcCommand(SqlStr, cn)
  result = cmd.ExecuteNonQuery()
  Catch ex As Exception
  If (ex.Message.IndexOf("Illegal duplicate key") <> -1) Then
  CreateLog(SqlStr, "User1", "Duplicate()", ex.Message)
  Else
  CreateLog(SqlStr, "User1", "Other()", ex.Message)
  End If
  'CreateLog(SqlStr, "User1", "Other()", ex.Message)
  End Try

  Next
  End If
  Catch ex As Exception

  CreateLog(SqlStr, "User1", "InsertToHHTTransferLine()", ex.Message)
  result = -1
  Finally
  cn.Close()
  cn.Dispose()
  End Try

  Return result
 End Function

Answer 1

Could be that "cmd" variables need to be disposed before creating new ones? At least this is the only thing I can see in the code where you are consuming resources in a loop depending on the number of records.

Anyway this should be easy to identify with a debugger, just figure out the line giving you the error, and that will lead you to the answer.