Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Getting data from spreadsheet and saving it into an Oracle table

    I have the following code in my program that retrieves all the data from an Excel spreadsheet and imports it into an Oracle table. However I get the message:
    An exception has occurred. Source: System.Data.
    Description: ORA-01400: cannot insert NULL into ("vn_inv.inv_num").

    I have 5 rows of data in the spreadsheet and the code imports all 5 rows into the Oracle table before this error occurs.

    I don't understand why it is trying to copy a Null row into the table. When the code retrieves the data from the spreadsheet, does it copy all rows even if they are empty? If it does, is there a way to only retrieve rows with data?

    Imports System.Data.OracleClient
    Imports CrystalDecisions.CrystalReports.Engine
    Imports CrystalDecisions.Shared
    Imports oExcel = Microsoft.Office.Interop.Excel
    Imports System.Data
    Imports System.Data.OleDb

    Public Class frmMain

    Private Sub cmdImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdImport.Click


    Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=h:\vendor_invoices.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
    Dim xlda As New OleDbDataAdapter("select * from [sheet1$] ", xlconn)
    Dim xldt As New DataTable
    xlda.Fill(xldt)
    xlda.Dispose()
    xlconn.Close()

    Dim conn As New OracleConnection
    conn.ConnectionString = "Password=aaa;User ID=aaa;Data Source=fsd_aaa"
    Dim da As New OracleDataAdapter("select inv_num,po_num,wr_id,inv_type,date_change,descript ion,inv_lab_amount, " _
    & "inv_mat_amount,inv_ser_amount,inv_tot_amount,po_v endor,date_created,time_created,date_mod,time_mod from vn_inv", conn)
    Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
    Dim ds As New DataSet
    da.Fill(ds, "tempemp")

    For Each xlrow As DataRow In xldt.Rows
    Dim dr As DataRow = ds.Tables("tempemp").NewRow
    For Each col As DataColumn In ds.Tables("tempemp").Columns
    dr(col.ColumnName) = xlrow(col.ColumnName)
    Next
    ds.Tables("tempemp").Rows.Add(dr)
    Next
    Try
    da.Update(ds, "tempemp")
    Catch ex As Exception
    'Error Checking happens here, this just throws the error
    MessageBox.Show("An exception has occurred." _
    & ControlChars.CrLf & "Source: " & ex.Source _
    & ControlChars.CrLf & "Description: " _
    & ex.Message, "Continue")
    End Try

    da.Dispose()
    conn.Close()



    Thanks,
    SBR
    Last edited by sbr7770; 03-31-06 at 14:28.

  2. #2
    Join Date
    Mar 2006
    Posts
    8
    you should try copying the 5 rows onto a new sheet.

    I have noticed excel sometimes doesnt recognise null rows as null rows.

    you can see if this is the case by doing ctrl+shft+end and seeing where the data ends.

  3. #3
    Join Date
    Mar 2006
    Posts
    8
    Also, you can use the Oracle COM interface if you are running on Windows.

  4. #4
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Getting data from spreadsheet & saving it into an Oracle table

    I found some code this morning that would test for null in a data row. Here's how I resolved it:
    For Each xlrow As DataRow In xldt.Rows
    Added these lines>> Dim TextEntered As String
    >>>>> TextEntered = xlrow("inv_num").ToString
    >>>>>> If TextEntered = "" Then
    >>>>>> Exit For
    >>>>>> Else
    Dim dr As DataRow = ds.Tables("tempemp").NewRow
    For Each col As DataColumn In ds.Tables("tempemp").Columns
    dr(col.ColumnName) = xlrow(col.ColumnName)
    Next
    ds.Tables("tempemp").Rows.Add(dr)
    >>>>>>> End If
    Next

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •