Results 1 to 9 of 9

Thread: How to upload datatable into SQL row by row and field by field ?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    478

    How to upload datatable into SQL row by row and field by field ?

    I used sqlbulcopy to upload datatable to sql but got an error said that "string convert to decimal error".

    Since table has about 300 fields and the error don't indicate where the error is.

    How to upload datatable into SQL row by row and field by field so that I could check the error?

    Or, use another way instead of datatable?

  2. #2
    Fanatic Member Toph's Avatar
    Join Date
    Oct 2014
    Posts
    655

    Re: How to upload datatable into SQL row by row and field by field ?

    Search up on Insert statements for SQL. It will help a lot.

  3. #3
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: How to upload datatable into SQL row by row and field by field ?

    Hello,

    * Create a connection to the database
    * Create a command
    * Create the SQL INSERT with parameters
    * Create parameters for the command
    * Open connection
    * In a for/next or for/each of the DataTable, for each iteration set the parameter values from the params created above.
    * ExecuteNonQuery and check the results. You may get an error as before, let it throw an exception,
    examine the row to figure out which field is the issue or wrap the ExecuteNonQuery in a Try/Catch and place a break point there
    then when hit examine the row data

    Simple creation of a parameter
    SqlClient.SqlParameter("@inputone", SqlDbType.Int)

    Also, please consider your table design, 300 fields is excessive. Usually in most cases multiple tables with relations setup in the database or in your SQL will be best.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    478

    Does sqlbulkcopy class have index to indicate where is error existing?

    I used code below to upload datatable (from CSV) using sqlbulkcopy but got an error said "can't conversion string to decimal ..."

    There are about 2 million row in which there are 290 columns to upload.

    Does sqlbulkcopy class have index to indicate where is error existing?


    Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, Nothing)

    bc.DestinationTableName = "test"

    dbConn.Open()

    bc.WriteToServer(dt) // error appended here

  5. #5
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Does sqlbulkcopy class have index to indicate where is error existing?

    If you are filling a DataTable, then its schema should match that of the destination Table.

    You can load the schema into an empty DataTable like this:
    Code:
    Dim dt As New DataTable
    Dim conn As New SqlClient.SqlConnection("Your ConnectionString")
    Dim da As New SqlClient.SqlDataAdapter("Select * From TableName", conn)
    da.FillSchema(dt, SchemaType.Source)
    Now you can load the DataTable and it will throw an error when you add records to it if the data types are wrong. The DataRow can do implicit conversions from a String to the proper data type if you load it something like this:
    Code:
    Dim r As DataRow = dt.NewRow
    ' simulate a line read from the CSV file
    ' assume the datatypes are:  Int32, Int32, String, String, DateTime
    Dim line As String = "1,23,some string,another string,1/31/2014"
    Dim parsedCSVLine As String() = line.Split(","c)
    r.ItemArray = parsedCSVLine
    dt.Rows.Add(r)
    A more proper technique would be to covert each piece from the split string using something like this pattern as you will know where the conversion failed and can take appropriate action (skip record, log it, etc.).

    Code:
    Dim r As DataRow = dt.NewRow
    Dim tmp As Int32
    If Int32.TryParse(parsedCSVLine(0), tmp) Then
       r(0) = tmp
    Else
       ' fail
    End If
    
    If Int32.TryParse(parsedCSVLine(1), tmp) Then
       r(1) = tmp
    Else
       ' fail
    End If
    ...

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Does sqlbulkcopy class have index to indicate where is error existing?

    When I'm doing bulk copies, here's how I do it:

    1) Create a table in the database with all of the fields from the source file. Depending on how dirty the data is, I'll type what I can (numbers, dates, etc) otherwise, everything is a raw string
    2) create a datatable that mirrors the table from step 1
    3) Suck the data in and stuff it into the data table
    4) Feed the data table to the SQLBulkCopy object
    5) Once all of the data is in the table, I run validations against it - as part of the table def, I'll also include a field on the end that lets me know if it's bad or not
    6) After scrubbing the data and validating it, I then select any records that pass and insert them into their final table(s).
    7) Optionally I'll either flush out all the data, or at least flush out the ones that passed validation and were moved.

    But the key is that your datatable you send to the BulkCopy HAS TO MATCH FIELD FOR FIELD IN ORDER the fields of the destination table.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: How to upload datatable into SQL row by row and field by field ?

    Step 1 - Don't post duplicate threads - http://www.vbforums.com/showthread.p...error-existing all it does is create confusion and fractures the responses you get.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Does sqlbulkcopy class have index to indicate where is error existing?

    Please don't post duplicate threads - http://www.vbforums.com/showthread.p...field-by-field

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to upload datatable into SQL row by row and field by field ?

    I've merged the two threads. Try not to create duplicates. It's better to keep a single thread going so all the information can be in one place.

    Cheers
    FD
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Click Here to Expand Forum to Full Width