Question transfer datatable into sql table

[newdbo]

Hi all, i'm totally newbie need help the code to transfer dataset/datatable into sql table. Before insert the data into database, first i have to edit some values. Please give me a clue. I'm trying to import excel to sql server 2000.

        Dim dsSQL As New DataSet
        ProgressBar1.Maximum = dsSource.Tables(0).Rows.Count
        Dim importDA As New SqlDataAdapter("SELECT * FROM " & tblname, oconnection)
        Dim importDB As New SqlCommandBuilder(importDA)
        'importDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
        importDA.Fill(dsSQL)

        'importDA.InsertCommand = importDB.GetInsertCommand
        ''MessageBox.Show(importDA.InsertCommand.CommandText.ToString)
        ''MessageBox.Show(importDB.GetInsertCommand.CommandText.ToString)
        'Dim r As DataRow

        'For Each r In dsSource.Tables(0).Rows
        '    r.SetAdded()
        '    ProgressBar1.PerformStep()

        'Next
        Dim r As DataRow
        Dim col As DataColumn
        For Each r In dsSource.Tables(0).Rows


            Dim drnew As DataRow = dsSQL.Tables(0).NewRow

            For Each col In dsSQL.Tables(0).Columns

                drnew(col.ColumnName) = r(col.ColumnName)

            Next

            dsSQL.Tables(0).Rows.Add(drnew)

            ProgressBar1.PerformStep()

        Next
        Label5.Text = "Proses import data...."
        importDA.Update(dsSource)
        dtSource = dsSource.Tables(0)
        'DataGridView2.DataSource = dtSource
        ProgressBar1.PerformStep()
        oconnection.Close()

Last time i run the application, it occurs an error which is : Column 'KEY' does not belong to table Table.
Please give me a clue...Thanks in advance

 

[jmcilhinney]

You can't use a CommandBuilder because that uses the SelectCommand as a base, but you're selecting from Excel and inserting into SQL Server, so that's no good. You need to use one OleDbDataAdapter to populate the DataTable from Excel and then a SqlDataAdapter with an appropriate InsertCommand to insert the data into SQL Server. You need to set the AcceptChangesDuringFill property of the OleDbDataAdapter to False so that all the rows remain in a state that will allow them to be inserted.

 

[newdbo]

can you give me code sample?

 

[jmcilhinney]

[ame=http://www.vbforums.com/showthread.php?t=469872]Retrieving and Saving Data in Databases[/ame]

 

[newdbo]

thank you jmcilhinney for your respond, but i have some values from excel that isn't appropriate with sql server datatype, i have to edit it before insert the values. can i update the values using dataset that i retrieve from oledb connection(excel)? eg : "-" from oledb dataset will be inserted into sqldbtypefloat. Please give me sample code. thanks in advance

 

[jmcilhinney]

Let me make sure I understand exactly what's happening here. You are retrieving data from an Excel workbook, then you want to transform some of the data, then you want to save the data to SQL Server. Is that correct?

 

[newdbo]

Re:Question transfer datatable into sql table

Yes, that's correct.But some values from excel dont have appropriate values for some columns. eg. 'QTY' column should have numeric values, but any of them is character such "-" which is inserted in excel (there is no restriction values of data type in excel). it occurs error in my application, i cant insert the values because i have to edit the values into numeric. How can i possibly do that?
this is the code that i used, but i didnt see any added row to my sql database.Please correct me if i'm wrong, i want to retrieve the data from my datatable but before that i have to edit some values so it will in the correct format.

strSQL = "select * from [" & ExcelSheetName & "]"

        Dim daSource As New OleDbDataAdapter(strSQL, XLSconnection)
        daSource.AcceptChangesDuringFill = False
        Dim dsSource As New DataSet
        daSource.Fill(dsSource)
        Dim dtSource As New DataTable
        dtSource = dsSource.Tables(0)
        'ProgressBar1.PerformStep()
        DataGridView1.DataSource = dtSource
        ProgressBar1.PerformStep()
        XLSconnection.Close()

        Dim dsSQL As New DataSet
        ProgressBar1.Maximum = dsSource.Tables(0).Rows.Count
        Dim importDA As New SqlDataAdapter("SELECT * FROM " & tblname, oconnection)
        Dim table As New DataTable
        ' Dim update As New SqlCommand("UPDATE " & tblname)
        'Dim importDB As New SqlCommandBuilder(importDA)
        Dim insert As New SqlCommand("INSERT INTO " & tblname & " (C/NC, KEY, PT,KDST,KDKB,KDAF,[Group TK],ID,ST_KJ,Status,ACC,SUB_ACC,Detail,[THN MSK],[BLN MSK],ITEM,KDKJ,MAIN,SUB,VOL_KJ,jROT,nROT,Std,QTY,HARGA,TOTAL,1,2,3,4,5,6,7,8,9,10,11,12,Cek,CostBln1,CostBln2,CostBln3,CostBln4,CostBln5,CostBln6,CostBln7,CostBln8,CostBln9,CostBln10,CostBln11,CostBln12) VALUES (@account, @KEY, @PT,@KDST,@KDKB,@KDAF,@grup,@ID,@ST_KJ,@Status,@ACC,@SUB_ACC,@Detail,@thn,@bln,@ITEM,@KDKJ,@MAIN,@SUB,@VOL_KJ,@jROT,@nROT,@Std,@QTY,@HARGA,@TOTAL,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@Cek,@CostBln1,@CostBln2,@CostBln3,@CostBln4,@CostBln5,@CostBln6,@CostBln7,@CostBln8,@CostBln9,@CostBln10,@CostBln11,@CostBln12)", oconnection)

        insert.Parameters.Add("@account", SqlDbType.NVarChar, 2, "C/NC")
        insert.Parameters.Add("@KEY", SqlDbType.NVarChar, 255, "KEY")
        insert.Parameters.Add("@PT", SqlDbType.NVarChar, 100, "PT")
        insert.Parameters.Add("@KDST", SqlDbType.NVarChar, 10, "KDST")
        insert.Parameters.Add("@KDKB", SqlDbType.NVarChar, 10, "KDKB")
        insert.Parameters.Add("@KDAF", SqlDbType.NVarChar, 10, "KDAF")
        insert.Parameters.Add("@grup", SqlDbType.NVarChar, 10, "[Group TK]")
        insert.Parameters.Add("@ID", SqlDbType.NVarChar, 100, "ID")
        insert.Parameters.Add("@ST_KJ", SqlDbType.NVarChar, 100, "ST_KJ")
        insert.Parameters.Add("@Status", SqlDbType.NVarChar, 10, "Status")
        insert.Parameters.Add("@ACC", SqlDbType.NVarChar, 150, "ACC")
        insert.Parameters.Add("@SUB_ACC", SqlDbType.NVarChar, 150, "SUB_ACC")
        insert.Parameters.Add("@Detail", SqlDbType.NVarChar, 200, "Detail")
        insert.Parameters.Add("@thn", SqlDbType.NVarChar, 10, "[THN MSK]")
        insert.Parameters.Add("@bln", SqlDbType.NVarChar, 10, "[BLN MSK]")
        insert.Parameters.Add("@ITEM", SqlDbType.NVarChar, 255, "ITEM")
        insert.Parameters.Add("@KDKJ", SqlDbType.NVarChar, 255, "KDKJ")
        insert.Parameters.Add("@MAIN", SqlDbType.NVarChar, 10, "MAIN")
        insert.Parameters.Add("@SUB", SqlDbType.NVarChar, 100, "SUB")
        insert.Parameters.Add("@VOL_KJ", SqlDbType.Float, 8, "VOL_KJ")
        insert.Parameters.Add("@jROT", SqlDbType.Float, 8, "jROT")
        insert.Parameters.Add("@Std", SqlDbType.Float, 8, "Std")
        insert.Parameters.Add("@QTY", SqlDbType.Float, 8, "QTY")
        insert.Parameters.Add("@HARGA", SqlDbType.Float, 8, "HARGA")
        insert.Parameters.Add("@TOTAL", SqlDbType.Float, 8, "TOTAL")
        insert.Parameters.Add("@1", SqlDbType.NVarChar, 2, "1")
        insert.Parameters.Add("@2", SqlDbType.NVarChar, 2, "2")
        insert.Parameters.Add("@3", SqlDbType.NVarChar, 2, "3")
        insert.Parameters.Add("@4", SqlDbType.NVarChar, 2, "4")
        insert.Parameters.Add("@5", SqlDbType.NVarChar, 2, "5")
        insert.Parameters.Add("@6", SqlDbType.NVarChar, 2, "6")
        insert.Parameters.Add("@7", SqlDbType.NVarChar, 2, "7")
        insert.Parameters.Add("@8", SqlDbType.NVarChar, 2, "8")
        insert.Parameters.Add("@9", SqlDbType.NVarChar, 2, "9")
        insert.Parameters.Add("@10", SqlDbType.NVarChar, 2, "10")
        insert.Parameters.Add("@11", SqlDbType.NVarChar, 2, "11")
        insert.Parameters.Add("@12", SqlDbType.NVarChar, 2, "12")
        insert.Parameters.Add("@CEK", SqlDbType.NVarChar, 2, "CEK")
        insert.Parameters.Add("@CostBln1", SqlDbType.Float, 8, "CostBln1")
        insert.Parameters.Add("@CostBln2", SqlDbType.Float, 8, "CostBln2")
        insert.Parameters.Add("@CostBln3", SqlDbType.Float, 8, "CostBln3")
        insert.Parameters.Add("@CostBln4", SqlDbType.Float, 8, "CostBln4")
        insert.Parameters.Add("@CostBln5", SqlDbType.Float, 8, "CostBln5")
        insert.Parameters.Add("@CostBln6", SqlDbType.Float, 8, "CostBln6")
        insert.Parameters.Add("@CostBln7", SqlDbType.Float, 8, "CostBln7")
        insert.Parameters.Add("@CostBln8", SqlDbType.Float, 8, "CostBln8")
        insert.Parameters.Add("@CostBln9", SqlDbType.Float, 8, "CostBln9")
        insert.Parameters.Add("@CostBln10", SqlDbType.Float, 8, "CostBln10")
        insert.Parameters.Add("@CostBln11", SqlDbType.Float, 8, "CostBln11")
        insert.Parameters.Add("@CostBln12", SqlDbType.Float, 8, "CostBln12")
        importDA.InsertCommand = insert
        importDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
importDA.Fill(dsSQL)
importDA.update(dsSQL)

Thanks in advance.

PS: Sorry, if i dont get your point, correct me if i'm wrong

 

[jmcilhinney]

Once you've populated the DataTable from Excel you can then add somem extra columns to it, loop through the rows and transform the data from the original columns to the new columns, then save the data to the database while ignoring the old columns containing the invalid data, e.g.

Dim table As New DataTable

'Build the schema as it might be from the Excel workbook.
With table.Columns
    .Add("Name", GetType(String))
    .Add("Number", GetType(String))
End With

'Populate the table as it might be from the Excel workbook.
With table.Rows
    .Add("Peter", "1")
    .Add("Paul", "Hello World")
    .Add("Mary", "-")
End With

'Add a new column to contain the real numbers.
table.Columns.Add("Number_New", GetType(Integer))

Dim number As Integer

'Transform the data.
For Each row As DataRow In table.Rows
    If Integer.TryParse(CStr(row("Number")), number) Then
        row("Number_New") = number
    Else
        row("Number_New") = DBNull.Value
    End If
Next

'Now save the data to the database from the Number_New column instead of the Number column.

 

[newdbo]

sorry jmcilhiney, please could you fix my code:
I didnt want to add a column to my excel datatable, but i want to edit the row values of datatable,eg: i want
to change any character or null values from numeric column using sql statement and update the datatable.
After i update the datatable, i want to insert all the row and column from excel datatable into a blank sql server table.
my previous code cannot add any single row to my sql table, why does this happened? please give me correction to my code.I dont add a primary key because it must has same format columns with excel file. Sorry, i'm totally newbie. thank you for responding me jmcilhiney.