importing excel

Hello all,

I'm really having a hard time understanding this tutorial.  I'm trying to follow tutorials to get my spreadsheet into an existing sql server database.  Here is my code.  I have a spreadsheet with two columns (ReturnDocumentID and ReturnRecordType) and a table in the database with those two columns and an ID column.  I can connect to and bind the data to a gridview with no problems, but when I try to insert it into the database I get errors about my return method or a primary key violation.  Any pointers would be great.

Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
        PanelUpload.Visible = False
        PanelView.Visible = False
        PanelImport.Visible = True

        LabelImport.Text = ""

        Dim objCommand As New OleDbCommand()
        objCommand = ExcelConnection()

        Dim reader As OleDbDataReader
        reader = objCommand.ExecuteReader()

        While reader.Read()
            Dim ID As Integer = 0

            Dim ReturnDocumentID As String = Convert.ToString(reader("ReturnDocumentID"))
            Dim ReturnRecordType As String = Convert.ToString(reader("ReturnRecordType"))
            ID = GetRmaNumber(ReturnDocumentID, ReturnRecordType)

        End While

    End Sub
Protected Function GetRmaNumber(ByVal ReturnDocumentID As String, ByVal ReturnRecordType As String) As String

        ReturnDocumentID = Left(ReturnDocumentID, 100)
        ReturnRecordType = Left(ReturnRecordType, 50)
        Dim ID As Integer = 0
            Dim RmaAdapter As New EddieTableAdapters.RmaDataTableAdapter
            Dim RmaDataTable As Eddie.RmaDataDataTable = Nothing
            RmaDataTable = RmaAdapter.GetDataByRmaNumber(ReturnDocumentID)

            If Not RmaDataTable Is Nothing Then
                If RmaDataTable.Rows.Count > 0 Then
                    If Not RmaDataTable(0).ID = Nothing Then
                        ID = RmaDataTable(0).ID
                    End If
                End If
            End If

            If ID = 0 Then
                ID = Convert.ToInt32(RmaAdapter.InsertQuery(ReturnDocumentID, ReturnRecordType))
            End If
            Return ID
        Catch ex As Exception
            LabelImport.Text = ex.ToString
            Return 0
        End Try
    End Function
11/21/2008 8:14:18 PM 91979 articles. 4 followers. Follow

5 Replies

Similar Articles

[PageSpeed] 49
Get it on Google Play
Get it on Apple App Store

your converting both fields to strings should one be a different data type?

11/21/2008 8:42:06 PM

I don't think so.  Sample data: ReturnDocumentID(RMA000123), ReturnRecordType(INV000456) so I used strings.  I tried using both nchar and varchar in the database and that didn't seem to make a difference.

11/21/2008 9:03:04 PM

 If you are in a test database can you remove the primary key or unique constrain to see if you are trying to insert two values that are the same? This would eliminate one possible error.

11/21/2008 9:12:03 PM

Hello,  I've been working on this quite a bit and I've almost got it.  The only problem left is that sometimes I get an error that says "Object cannot be cast from DBNull to other types."  Is this from having blank cells in my spread sheet?  Here is my code.

Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
        PanelUpload.Visible = False
        PanelView.Visible = False
        PanelImport.Visible = True

        LabelImport.Text = ""

        Dim objCommand As New OleDbCommand()
        objCommand = ExcelConnection()

        Dim objReader As OleDbDataReader
        objReader = objCommand.ExecuteReader()

        While objReader.Read()
            Dim Record_ID As Integer = 0

            Dim ReturnDocumentID As String = Convert.ToString(objReader("ReturnDocumentID"))
            Dim SVCReturnType As String = Convert.ToString(objReader("SVCReturnType"))
            Dim ReturnReference As String = Convert.ToString(objReader("ReturnReference"))
            Dim EntryDate As Date = Convert.ToDateTime(objReader("EntryDate"))
            Dim ReturnDate As Date = Convert.ToDateTime(objReader("ReturnDate"))
            Dim CustomerName As String = Convert.ToString(objReader("CustomerName"))
            Dim ItemNumber As String = Convert.ToString(objReader("ItemNumber"))
            Dim Quantity As String = Convert.ToString(objReader("QTY"))
            Dim ExtendedCost As Decimal = Convert.ToDecimal(objReader("ExtendedCost"))
            Dim ExtendedPrice As Decimal = Convert.ToDecimal(objReader("ExtendedPrice"))
            Dim ReasonCode As String = Convert.ToString(objReader("ReasonCode"))
            Dim ReasonDesc As String = Convert.ToString(objReader("ReasonDesc"))

            Record_ID = ImportData(ReturnDocumentID, SVCReturnType, ReturnReference, EntryDate, ReturnDate, CustomerName, _
                                   ItemNumber, Quantity, ExtendedCost, ExtendedPrice, ReasonCode, ReasonDesc)

            LabelImport.Text &= Record_ID & " " & ReturnDocumentID & " " & ItemNumber & "<br>"
        End While
    End Sub

    Protected Function ImportData(ByVal ReturnDocumentID As String, ByVal SVCReturnType As String, ByVal ReturnReference As String, _
                                  ByVal EntryDate As Date, ByVal ReturnDate As Date, ByVal CustomerName As String, _
                                  ByVal ItemNumber As String, ByVal Quantity As String, ByVal ExtendedCost As Decimal, _
                                  ByVal ExtendedPrice As Decimal, ByVal ReasonCode As String, ByVal ReasonDesc As String) As Integer

        ReturnDocumentID = Left(ReturnDocumentID, 50)
        SVCReturnType = Left(SVCReturnType, 20)
        ReturnReference = Left(ReturnReference, 30)
        EntryDate = Left(EntryDate, 20)
        ReturnDate = Left(ReturnDate, 20)
        CustomerName = Left(CustomerName, 100)
        ItemNumber = Left(ItemNumber, 20)
        Quantity = Left(Quantity, 50)
        ExtendedCost = Left(ExtendedCost, 20)
        ExtendedPrice = Left(ExtendedPrice, 20)
        ReasonCode = Left(ReasonCode, 20)
        ReasonDesc = Left(ReasonDesc, 75)

        Dim Record_ID As Integer = 0
            Dim RecordAdapter As New EddieTableAdapters.RecordTableAdapter
            Dim RecordDataTable As Eddie.RecordDataTable = Nothing
            RecordDataTable = RecordAdapter.GetRmaDataByRmaAndItemNumber(ReturnDocumentID, ItemNumber)

            If Not RecordDataTable Is Nothing Then
                If RecordDataTable.Rows.Count > 0 Then
                    If Not RecordDataTable(0).Record_ID = Nothing Then
                        Record_ID = RecordDataTable(0).Record_ID
                        LabelImport.Text = LabelImport.Text & _
                        "<font color=red>Member Found, Not Imported: " & "</font>" & _
                        " ID:  " & Record_ID & "  " & ReturnDocumentID & ".<br>"
                    End If
                End If
            End If

            If Record_ID = 0 Then
                Record_ID = Convert.ToInt32(RecordAdapter.InsertRmaQuery(ReturnDocumentID, SVCReturnType, ReturnReference, _
                                            EntryDate, ReturnDate, CustomerName, ItemNumber, Quantity, ExtendedCost, _
                                            ExtendedPrice, ReasonCode, ReasonDesc))
                LabelImport.Text = LabelImport.Text & _
                        "<font color=blue>Member Imported: " & "</font>" & _
                        " ID:  " & Record_ID & "  " & ReturnDocumentID & ".<br>"
            End If

            Return Record_ID

        Catch ex As Exception
            LabelImport.Text &= ex.ToString
            Return 0
        End Try

    End Function
12/3/2008 6:10:30 PM

It turns out it's fine for the cell to be empty, but buried in my spreadsheet were a couple of entries that had #NUM of #REF in them.  Once I got rid of those it works fine.  Another question: How can I run a validation before I import the data that will check for #REF and #NUM and replace them with something?  I can do it with a string like so.

Dim ReturnReference As String = Convert.ToString(objReader("ReturnReference"))
            If ReturnReference.Length = 0 Then
                ReturnReference = "Empty"
            End If
 But I haven't found a way to do it with a date.  Any suggestions?
12/4/2008 2:33:28 PM

Similar Artilces:

Getting started with Excel
Hi everyone,  Can someone please provide me with a link that will tech me how to Create, Read/Write and deal with Excel files.  What I am doing right now is passing a SQL string to a class which is exporting values in .csv format. Then the user will open it with excel by default and save it as excel. I have Visual Studio 2008 and I am creating ASP.NET 2.0 webpages. (I guess I could do 3.5 but my server does not have 3.5 framework yet)  Oh Lord, you can image where it goes from here. He fixes the cable? Friend, For creating Excel File :

Getting Start With .net
hi i'm intersted to learn (vb or delphi) . i programming with delphi6 but i'd like to tp know something about programming in internet specially with .net . i hear about with delphi 7 that i can programming with delphi in .net .... is it right ? what 's our opinian about it ? and better to learn Vb.Net or continu with delphi(or thanks. Ali Darabian I think there is some support for Delphi in .Net. However, you will find much more support from the user community if you choose a more popular language such as VB.Net or C#. Since your al...

how to get start with .NET
the net help people installed  to my desktop 1-microsoft visual studio .NET enterprise developer 2003 2-microsoft .NET framework 1.1 what else do i need ...???  and how can i try with some simple code in VS.Net and run it to see wat happen...????? please help If you want to develope web applications, then you'll also need IIS on your PC.  That'll require XP-Professional or Windows 2000/2003, but XP-Home edition won't allow IIS installation. As for how to get started, you can search the web for some tutorials or buy a good book on VS.  The topic is way too vast to...

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installed Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

Where to start and how did you get started?
Hi, I have had a feeling for a long time to jump into the adds market for some time due to my low income (shooting for the stars) and I don't have anything better to do these days. But I find it really difficult not to give up due to lack of guides (what I can find atleast) and all the hard words which only makes me confused. But I won't give up. Never. Not sure if this is the right place to post this, but I deffinitly would be really greatfull for help getting started. How did you get started? +If there are any teacher that wanna show me the ropes private, I can give payment with ...

Getting started with JScript.NET
I'm a Javascript and Actionscript developer, and the thought of being able to do server-sides in something I know was too good to resist. Does anyone know some good tutorials for developing ASP.NET pages in JScript?  I can find lots of console code, but simple things are hard: like I can't even figure out how to print something out to the web page (just a string). What I'm really trying to do is access a database, verify a login, and print JSON out to the response.  What should I do to start?  Is there a better way to do this? (IE: using some JSON library already?). I'm commun...

Excel as import to
Has anyone experimented with using excel files as import to a app? I have a user who is ... reluctant to give up his personal sheets... Thanks! MVP...

How do I get started with AJAX.Net?
Hi All, I'm a seasoned developer but have not yet ventured into atlas et al.  I prefer books, but will take whatever I can get if it's good and will get me going. Any recommendations? Thx in advance. Jeff try here first then learn from sample and docJames Wu (MIB426).NET is only way to goMCP, MCSE, MCDBA, MCSD, MCAD I find the materials online display features and implementations but are lacking architectural depth, and am seeking more comprehensive resources. By architectural depth, I mean that I cannot watch ...

Getting Started with .net and a few questions
Hello I've done some asp and php programming along with various other languages in the past and slowing getting my head around .net.I have a small project of creating a Holiday Booking system on our Intranet and just trying to get the first part to work properly will hopefully get me sorted for the whole project. The Intranet is running on .net 2 and I prefer to use the VB code rather than C#. This is connecting to an SQL server.My initial page that I am trying to create is just for an admin user to view which dates in a table have been specified as Bank Holidays and be able to add ...

Getting started .net website
Hi folks,Does anyone here know of a good website to just sit back and read for learning .net? Thanks in advance.Baldeep Right here... Click the Get Started and Learn sections here. Quite a few out there - - this one, has a lot of good videos -  also, but then, I might be a little prejudiced David WierMCP/ - where to look first!Please Vote for ASPNet101 - 'Best Community Resource'!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(P...

Help me start (Import .NET assembly...)
Hi, In one of my projects I have to use some API made by another company. They provided us with an ActiveX, after importing it in Delphi, I got this. Please point me in the right direction on how to access the function AckCDRs for example. I have no idea where to start. Here is the unit generated by Delphi (very long, if I can't pot it here, I'll put it in Attachments with the same name). TIA. -ioan unit XMLCOM_TLB; {$TYPEDADDRESS OFF} // Unit must be compiled without type-checked pointers. {$WARN SYMBOL_PLATFORM OFF} {$WRITEABLECONST ON} {$VARPROPSETTER ON} ...

Getting Started: OLE & Excel
We are experience PB (7) developers that are about to embark on our first mission into OLE. We need to work with Excel spreadsheets and save them as BLOBs to the database. What issue will we face? What would be a good reference manual to start with? Thanks, Tom Houser, To what extent do we need to worry about different versions of Excel??? "Terry Dykstra" <> wrote in message > Take a look at the selectblob(), updateblob() functions. > The enc...

Importing complex Excel file into .Net
Greetings, I have an Excel file that has calculations galore. I want to import and have the same functionality of this file in .Net and be able to download it to an Excel file from the browser. Has anyone done this? I can export to xml but have yet to figure how to bind it to the MS Office Spreadsheet Object found in the toolbox of VB.Net. Please help :) thx and Happy Coding Ed...

Getting Started Without VS.NET
Hi all, does anyone know how any online resources that can show me how to create web apps that include code-behind, and uer controls without using VS.NET? I have VS.NET in work, but would also like to do work at home, but cannot install VS.NET on my home laptop because I simply dont' have the space or processor power. Any online articles would be welcome, as will any open-source apps - I currently have SharpDevelop, but I find it unstable as it crashes my laptop alot of the time. Thanks Tryst Did you try Web Martix? It is a FREE tool from Microsoft.Regards, Narayana ...

How to Import data from Excel into MS SQL in .Net?
Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.  I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work. - Most simple and direct way I've tried is creating a oledb connection to...

Web resources about - importing excel -

Facebook Expands Feed Connect (er, Feed Importing) to More Sites
About six weeks ago, Facebook launched Feed Importing for the first time, allowing users to connect their Facebook accounts to Flickr, Yelp, ...

YouTube - iPad importing photos from floppy disk.
You are using an outdated browser, which YouTube no longer supports. Since some features on YouTube may not work, you are viewing a lightweight ...

Tony Abbott considers importing US-style school program with private industry involvement
Tony Abbott is considering an unprecedented Americanisation of the school education system with radical changes that could see HECS-style fees ...

Importing fuel will prove better for the consumer
Importing fuel will prove better for the consumer

Man accused of importing drugs to Canberra post office boxes to stand trial
A man accused of importing MDMA and cocaine into the ACT using false identities and a series of post office boxes has pleaded not guilty and ...

Canberra restaurateur jailed for importing cocaine
A Canberra restaurateur has been jailed for importing $114,000 worth of cocaine from the United States.

Urine test fail for man facing drug importing sentence
A Canberra man who pleaded guilty to importing a commercial quantity of drugs to his front door has admitted&nbsp;taking a banned drug&nbsp;weeks ...

Hong Kong man charged with importing 150kg of crystal methamphetamine into Sydney
A Hong Kong national is charged with importing 150 kilograms of ice following a joint-agency investigation in Sydney.

Man, 91, charged with importing cocaine hidden in soap boxes into Australia
A 91-year-old Sydney man has been charged with importing cocaine hidden in soap into Australia, prompting police to warn travellers to beware ...

Google Drive adds support for importing OpenOffice (ODF) file formats
Google announced today that it’s now officially supporting ODF files in Google Drive allowing users to import all three major ODF file formats. ...

Resources last updated: 11/22/2015 8:40:19 AM