Solved

datatable insert into sql

Posted on 2008-06-16
11
729 Views
Last Modified: 2013-11-26
Yes I have datatable I lilke to insert into MS-Sql 2005 database, I am not sure of the commands to do this
0
Comment
Question by:sevensnake77
  • 7
  • 3
11 Comments
 
LVL 4

Expert Comment

by:zx10r
ID: 21796801
What kind a database? Please be more descriptive with your question.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21796844
MS-Sql 2005 it is up top
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21796889
well I am trying to insert converted data into MS-sql server 2005. My insert parameter does not come up with an error but does not insert and data below.
        myReader.Close() 'Close connection string

        '*****************************Add Rows to DataTable**********************************
 

        Dim rowCt As Integer

        Dim row1 As DataRow

        row1 = exDataTable.NewRow
 

        For rowCt = 0 To i
 

            row1 = exDataTable.NewRow()

            row1("UPC") = exData(rowCt, 0)

            row1("Order") = exData(rowCt, 1)

            row1("Description") = exData(rowCt, 2)

            row1("PK") = exData(rowCt, 3)

            row1("SIZE") = exData(rowCt, 4)

            row1("SALE QTY") = exData(rowCt, 5)

            row1("SALE COST") = exData(rowCt, 6)

            row1("CASE COST") = exData(rowCt, 7)

            row1("DEAL") = exData(rowCt, 8)

            row1("DEAL COST") = exData(rowCt, 9)

            row1("UNIT QTY") = exData(rowCt, 10)

            row1("UNIT COST") = exData(rowCt, 11)

            row1("RETAIL") = exData(rowCt, 12)

            row1("PAL") = exData(rowCt, 13)

            row1("TIER") = exData(rowCt, 14)

            row1("TRIP PTS.") = exData(rowCt, 15)
 

            ' Be sure to add the new row to the DataRowCollection. 

            exDataTable.Rows.Add(row1)
 

        Next rowCt
 
 

        ''##################################################################
 

        '' ****** Here saving to the Recommend table from datagrid
 

        Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))

        ConnRecommendNew.Open()

        ' For Each row1 In DS.Tables(0).Rows

        Dim UPC As String = row1("UPC").ToString()

        Dim Description As String = row1("Description").ToString()

        Dim ImportRecommend As New SqlCommand

        ImportRecommend = ConnRecommendNew.CreateCommand
 
 

        ImportRecommend.CommandType = CommandType.Text

        ImportRecommend.CommandText = "Insert into SalesItems(Upc,Description) Values (@Upc, @Description)"

        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC

        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = Description

        ImportRecommend.ExecuteNonQuery()

        MyCommand = New System.Data.OleDb.OleDbDataAdapter(sqlStr, MyConnection)

        MyCommand.TableMappings.Add("Table", "SalesBatch")

        DataGrid1.DataSource = exDataTable

        DataGrid1.DataBind()
 
 

        ' ConnRecommendNew.Close()

        ConnRecommendNew.Close()

        MyConnection.Close()
 

        '  Next

    End Sub

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21797220
let's see if you want to learn the data adapter "way":
'##################################################################

 

        '' ****** Here saving to the Recommend table from datagrid

 

        Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))

        ConnRecommendNew.Open()
 

        Dim ImportRecommend As New SqlCommand

        ImportRecommend = ConnRecommendNew.CreateCommand

        ImportRecommend.CommandType = CommandType.Text

        ImportRecommend.CommandText = "Insert into SalesItems(Upc,Description) Values (@Upc, @Description)"

        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50, "UPC")

        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50, "Description")
 

        Dim da As New SqlDataAdapter(ImportRecommend)

        da.Update(exDataTable)
 

        MyCommand.TableMappings.Add("Table", "SalesBatch")

        DataGrid1.DataSource = exDataTable

        DataGrid1.DataBind()

 

 

        ' ConnRecommendNew.Close()

        ConnRecommendNew.Close()

        MyConnection.Close()

 

        '  Next

    End Sub

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21797356
I get this error

Update requires a valid InsertCommand when passed DataRow collection with new rows
But do you get what I am trying to do dude, I want after the information is transformed it will insert into MS-SQL but I have the whole code just in case at the bottom that starts the adtherythem


 Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))
        ConnRecommendNew.Open()
        ' For Each row1 In DS.Tables(0).Rows
        Dim UPC As String = row1("UPC").ToString()
        Dim Description As String = row1("Description").ToString()
        Dim ImportRecommend As New SqlCommand
        ImportRecommend = ConnRecommendNew.CreateCommand


        ImportRecommend.CommandType = CommandType.Text
        ImportRecommend.CommandText = "Insert into SalesItems(Upc,Description) Values (@Upc, @Description)"
        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC
        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = Description
        Dim da As New SqlDataAdapter(ImportRecommend)
        da.Update(exDataTable)
        MyCommand.TableMappings.Add("Table", "SalesBatch")
        ImportRecommend.ExecuteNonQuery()
        MyCommand = New System.Data.OleDb.OleDbDataAdapter(sqlStr, MyConnection)
        MyCommand.TableMappings.Add("Table", "SalesBatch")
        DataGrid1.DataSource = exDataTable
        DataGrid1.DataBind()
 Private DS As DataSet

    Private exData(1000, 15) As String

    Private toUPC(1000)

    Private fromKCtoUPC(1000) 'KC = Krasdale Code

    Protected WithEvents Button2 As System.Web.UI.WebControls.Button

    Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid

    Private exDataTable As DataTable

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        exDataTable = New DataTable

        'Create 16 columns with string as their type

        exDataTable.Columns.Add("UPC", String.Empty.GetType())

        exDataTable.Columns.Add("Order", String.Empty.GetType())

        exDataTable.Columns.Add("Description", String.Empty.GetType())

        exDataTable.Columns.Add("PK", String.Empty.GetType())

        exDataTable.Columns.Add("SIZE", String.Empty.GetType())

        exDataTable.Columns.Add("Sale QTY", String.Empty.GetType())

        exDataTable.Columns.Add("Sale Cost", String.Empty.GetType())

        exDataTable.Columns.Add("Case Cost", String.Empty.GetType())

        exDataTable.Columns.Add("Deal", String.Empty.GetType())

        exDataTable.Columns.Add("Deal Cost", String.Empty.GetType())

        exDataTable.Columns.Add("Unit QTY", String.Empty.GetType())

        exDataTable.Columns.Add("Unit Cost", String.Empty.GetType())

        exDataTable.Columns.Add("Retail", String.Empty.GetType())

        exDataTable.Columns.Add("PAL", String.Empty.GetType())

        exDataTable.Columns.Add("Tier", String.Empty.GetType())

        exDataTable.Columns.Add("Trip Pts.", String.Empty.GetType())

    End Sub

    Public Sub loadT1()
 

        Dim DS As System.Data.DataSet

        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        Dim MyConnection As System.Data.OleDb.OleDbConnection

        Dim sqlStr As String = "SELECT * FROM [100120080404$]", extendedString As String
 

        extendedString = "\Excel 8.0;HDR=Yes;IMEX=1\"";"";"

        MyConnection = New System.Data.OleDb.OleDbConnection( _

              "provider=Microsoft.Jet.OLEDB.4.0; " & _

              "data source=C:\Excel\Grocery.XLS; " & _

              "Extended Properties=Excel 8.0;")
 

        Dim command As New OleDbCommand(sqlStr, MyConnection)

        MyConnection.Open()
 

        Dim myReader As OleDbDataReader = command.ExecuteReader()

        '*************************Code begins here****************************************************

        Dim i As Integer, j As Integer, makeChange As Boolean

        i = 0 : j = 0

        While myReader.Read() 'Populate exData array with DataReader
 

            fromKCtoUPC(1000) = myReader(0) 'Read in Krasdale Code and UPC
 

            Dim l As Integer = Len(myReader(0).ToString())

            If l = 6 Then

                For j = 0 To 15

                    exData(i, j) = myReader(j).ToString()

                    If j >= 15 Then

                        i = i + 1

                    End If

                Next
 

            Else

                toUPC(i - 1) = myReader(0) 'Collect UPC

            End If

        End While

        '*************************Array is Loaded with data form 100120080404***************************

        'Begin fill in omitted areas

        '***********************************************************************************************

        'Check if these indices are populated

        Dim q As Integer, r As Integer, y As Integer

        Dim start As Integer = 0, start1 As Integer = 0

        For q = 0 To i
 

            For r = 0 To (1 - 1)

                'Condition 1

                If exData(q, 3) <> "" And exData(q, 4) <> "" And exData(q, 5) <> "" And exData(q, 6) = "" _

                And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" And exData(q, 10) <> "" _

                And exData(q, 11) = "" And exData(q, 12) <> "" Then

                    Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 10)

                    For y = start To q

                        Dim x As String, z As String : x = exData(y, 4)

                        If exData(y, 3) = "" And exData(y, 4) = "" And exData(y, 5) = "" And exData(y, 6) = "" _

                        And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" And exData(y, 10) = "" _

                        And exData(y, 11) = "" And exData(y, 12) = "" Then
 

                            exData(y, 3) = exData(q, 3)

                            exData(y, 4) = exData(q, 4)

                            exData(y, 5) = SplitFront(a)

                            exData(y, 6) = SplitBack(a)

                            exData(y, 7) = exData(q, 7)

                            exData(y, 8) = exData(q, 8)

                            exData(y, 9) = exData(q, 9)

                            exData(y, 10) = SplitFront(b)

                            exData(y, 11) = SplitBack(b)

                            exData(y, 12) = exData(q, 12)

                        End If

                    Next y

                    start = q

                    'Condition 2

                ElseIf exData(q, 3) <> "" And exData(q, 4) <> "" And exData(q, 5) = "" And exData(q, 6) = "" _

                And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" And exData(q, 10) <> "" _

                And exData(q, 11) = "" And exData(q, 12) = "" Then

                    Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 10)

                    For y = start To q

                        If exData(y, 3) = "" And (exData(y, 4) = "" Or exData(y, 4) <> "") And exData(y, 5) = "" _

                        And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _

                        And exData(y, 10) = "" And exData(y, 12) = "" And exData(y, 6) = "" _

                        And exData(y, 11) = "" Then

                            exData(y, 3) = exData(q, 3)

                            If exData(y, 4) = "" Then

                                exData(y, 4) = exData(q, 4)

                            End If

                            exData(y, 5) = SplitFront(a)

                            exData(y, 6) = SplitBack(a)

                            exData(y, 7) = exData(q, 7)

                            exData(y, 8) = exData(q, 8)

                            exData(y, 9) = exData(q, 9)

                            exData(y, 10) = SplitFront(b)

                            exData(y, 11) = SplitBack(b)

                            exData(y, 12) = exData(q, 12)

                        End If

                    Next y

                    start = q

                End If

            Next

        Next

        '***********************************************************************************************

        'Go over data second time and fill in the blanks

        '***********************************************************************************************

        Dim count As Integer

        For q = 0 To i

            If exData(q, 5) = "" And exData(q, 6) = "" Then

                If count = 0 Then

                    start = q

                End If

                count = count + 1

            Else

                Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 12)

                For y = start To q

                    If exData(y, 5) = "" And exData(y, 6) = "" And exData(y, 12) = "" Then

                        exData(y, 5) = SplitFront(a)

                        exData(y, 6) = SplitBack(a)

                        exData(y, 12) = b

                    End If

                Next y

            End If

        Next q

        '***********************************************************************************************

        'Go over data third time and fill Sale Qty and Sale Cost where applicable

        '***********************************************************************************************

        For q = 0 To i

            Dim a As String, b As String : a = exData(q, 5)

            If exData(q, 5) <> "" Then

                Dim l = InStr(exData(q, 5), "/")

                If l > 0 Then

                    exData(q, 5) = SplitFront(a)

                    exData(q, 6) = SplitBack(a)

                End If

            End If

        Next q
 

        For q = 0 To i

            Dim a As String : a = exData(q, 10)

            If exData(q, 10) <> "" Then

                Dim l = InStr(exData(q, 10), "/")

                If l > 0 Then

                    exData(q, 10) = SplitFront(a)

                    exData(q, 11) = SplitBack(a)

                End If

            End If

        Next q

        '**********************************************************************************************

        'Go over data and change Krasdale Code to UPC

        '**********************************************************************************************

        For q = 0 To i

            Try

                exData(q, 0) = toUPC(q) 'Update from Krasdale Code to UPC 

            Catch

            End Try

        Next q
 

        For q = 0 To i

            Dim l As Integer = Len(exData(q, 0))

            If l = 6 Then

                exData(q, 0) = fromKCtoUPC(q + 1)

            End If

            ' Response.Write(DS)

        Next q

        '***********************************************************************************************

        myReader.Close() 'Close connection string

        '*****************************Add Rows to DataTable**********************************
 

        Dim rowCt As Integer

        Dim row1 As DataRow

        row1 = exDataTable.NewRow
 

        For rowCt = 0 To i
 

            row1 = exDataTable.NewRow()

            row1("UPC") = exData(rowCt, 0)

            row1("Order") = exData(rowCt, 1)

            row1("Description") = exData(rowCt, 2)

            row1("PK") = exData(rowCt, 3)

            row1("SIZE") = exData(rowCt, 4)

            row1("SALE QTY") = exData(rowCt, 5)

            row1("SALE COST") = exData(rowCt, 6)

            row1("CASE COST") = exData(rowCt, 7)

            row1("DEAL") = exData(rowCt, 8)

            row1("DEAL COST") = exData(rowCt, 9)

            row1("UNIT QTY") = exData(rowCt, 10)

            row1("UNIT COST") = exData(rowCt, 11)

            row1("RETAIL") = exData(rowCt, 12)

            row1("PAL") = exData(rowCt, 13)

            row1("TIER") = exData(rowCt, 14)

            row1("TRIP PTS.") = exData(rowCt, 15)
 

            ' Be sure to add the new row to the DataRowCollection. 

            exDataTable.Rows.Add(row1)
 

        Next rowCt
 
 

        ''##################################################################
 

        '' ****** Here saving to the Recommend table from datagrid
 

        Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))

        ConnRecommendNew.Open()

        ' For Each row1 In DS.Tables(0).Rows

        Dim UPC As String = row1("UPC").ToString()

        Dim Description As String = row1("Description").ToString()

        Dim ImportRecommend As New SqlCommand

        ImportRecommend = ConnRecommendNew.CreateCommand
 
 

        ImportRecommend.CommandType = CommandType.Text

        ImportRecommend.CommandText = "Insert into SalesItems(Upc,Description) Values (@Upc, @Description)"

        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC

        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = Description

        Dim da As New SqlDataAdapter(ImportRecommend)

        da.Update(exDataTable)

        MyCommand.TableMappings.Add("Table", "SalesBatch")

        ImportRecommend.ExecuteNonQuery()

        MyCommand = New System.Data.OleDb.OleDbDataAdapter(sqlStr, MyConnection)

        MyCommand.TableMappings.Add("Table", "SalesBatch")

        DataGrid1.DataSource = exDataTable

        DataGrid1.DataBind()
 
 

        ' ConnRecommendNew.Close()

        ConnRecommendNew.Close()

        MyConnection.Close()

        '  Next

    End Sub
 
 
 
 
 
 
 

    Private Function SplitFront(ByVal s As String) As String

        Dim l = InStr(s, "/")

        If l > 0 Then

            SplitFront = Mid(s, 1, l - 1)

        Else

            SplitFront = s

        End If

    End Function

    Private Function SplitBack(ByVal s As String) As String

        Dim l = InStr(s, "/")

        If l > 0 Then

            SplitBack = Mid(s, (l + 1), (Len(s) - 1))

        Else

            SplitBack = s

        End If

    End Function
 

    'EVENT HANDLERS

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        loadT1()

    End Sub

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Author Comment

by:sevensnake77
ID: 21802797
getting an error
Update requires a valid InsertCommand when passed DataRow collection with new rows. 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 

Exception Details: System.InvalidOperationException: Update requires a valid InsertCommand when passed DataRow collection with new rows.
 

Source Error: 
 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  
 

Stack Trace: 
 
 

[InvalidOperationException: Update requires a valid InsertCommand when passed DataRow collection with new rows.]

   System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +1662

   System.Data.Common.DbDataAdapter.Update(DataTable dataTable) +139

   A1S.WebForm2.loadT1() in \\10.52.90.1\a1s\store\Upload\WebForm2.aspx.vb:280

   A1S.WebForm2.Button1_Click(Object sender, EventArgs e) in \\10.52.90.1\a1s\store\Upload\WebForm2.aspx.vb:319

   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57

   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18

   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

   System.Web.UI.Page.ProcessRequestMain() +1273
 

 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21803158
sorry, my fault...
the dataadapter needs a select query and the insert command as "insertcommand":
Dim da As New SqlDataAdapter("select * from yourtable where 1=2")

da.InsertCommand = ImportRecommend

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21803311
Ok I add the insert and select command into the stored procedure seems like the only way.
but i am still getting the null reference

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object

select * from [A1S].[dbo].[SalesItems] where upc = @Upc

Insert into SalesItems(Upc,Description) Values (@Upc, @Description)
      
 Dim ImportRecommend As New SqlCommand("spA1SInsertExcel", ConnRecommendNew)

        ImportRecommend.CommandType = CommandType.StoredProcedure

        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50, "UPC")

        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50, "Description")
 

        Dim da As New SqlDataAdapter(ImportRecommend)

        da.InsertCommand = ImportRecommend

        da.Update(exDataTable)

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21804338
sorry this does work, I was coming up with an error
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

but I looked into the database and the info was there so how can i stop the error from showing up
 Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))

        ConnRecommendNew.Open()

        ' For Each row1 In DS.Tables(0).Rows

        'Dim ImportRecommend As New SqlCommand

        Dim ImportRecommend As New SqlCommand("spA1SInsertExcel", ConnRecommendNew)

        ImportRecommend.CommandType = CommandType.StoredProcedure

        ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50, "UPC")

        ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50, "Description")
 

        Dim da As New SqlDataAdapter(ImportRecommend)

        da.InsertCommand = ImportRecommend

        da.Update(exDataTable)
 

        MyCommand.TableMappings.Add("Table", "SalesBatch")

        DataGrid1.DataSource = exDataTable

        DataGrid1.DataBind()

        ImportRecommend.ExecuteNonQuery()
 

        ' ConnRecommendNew.Close()

        ConnRecommendNew.Close()

        MyConnection.Close()

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21804901
remove:
        ImportRecommend.ExecuteNonQuery()

and what is:
     MyConnection.Close()
doing there?
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21805030
you need ExecuteNONQuery to insert into any database you shouldn't have to use it for a select statement.
but I have it working and MYConnection.Close is to close the connection after everything.

Thanks guy
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now