Can’t save an Imported Excel sheet to Database in VB 2005

[nickj]

Hi guys
I have this program that imports and Excel spreadsheet to a database, but when I try and save the imported information, nothing is saved.
Here is my code I have so far….

Private Sub bindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bindingNavigatorSaveItem.Click
        If Me.Validate Then
            Me.StockBindingSource.EndEdit()
            Me.StockTableAdapter.Update(Me.StockDataSet.Stock)
        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation errors occurred.", "Save", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning)
        End If
    End Sub
 
   Private Sub ImportBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImportBtn.Click
        Dim DS As System.Data.DataSet
        Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
 
        MyConnection = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=D:\Stock.XLS; " & _
              "Extended Properties=Excel 8.0;")
        ' Select the data from Sheet1 of the workbook.
 
        MyAdapter = New System.Data.OleDb.OleDbDataAdapter( _
              "select * from [Sheet1$]", MyConnection)
        DS = New System.Data.DataSet()
        MyAdapter.Fill(DS, "StockDataGridView")
        MyConnection.Close()
        StockDataGridView.DataSource = DS.Tables("StockDataGridView") 'Tablegrid Name
    End Sub

Any ideas why this is not saving?

Thanks guys

Nick

[RobDog888]

Your only populating your datasset. You need to do an Insert action to place the data into your database.

[nickj]

Thanks for that RobDog888, how and were would i use the Insert?

[nickj]

This is what i have so far, but i get and error say "Object reference not set to an instance of an object."

Try
            MyConnection.Open()
            Dim command As String
            ' write the transaction to the customers database if it is valid
            command = "insert into StockDataGridView(ID, Code, Name, CatID, Category, SubCatID, SubCatergory, Price, Url)"
            'put the sql string into the adapter
            MyAdapter.InsertCommand.CommandText = command
            'do the insert
            MyAdapter.InsertCommand.ExecuteNonQuery()
        Catch exceptionObject As Exception
            MessageBox.Show(exceptionObject.Message)
        Finally
            MyConnection.Close()
        End Try

Thanks for your help

Nick