Solved

datatable insert into sql

Posted on 2008-06-16
11
744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 143

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
 
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 143

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 143

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

724 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