?
Solved

datatable insert into sql

Posted on 2008-06-16
11
Medium Priority
?
757 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:Seven price
  • 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:Seven price
ID: 21796844
MS-Sql 2005 it is up top
0
 
LVL 9

Author Comment

by:Seven price
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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:Seven price
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:Seven price
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:Seven price
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:Seven price
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:Seven price
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
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…

850 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