Saving Data MS Access in Datagridview without using the wizard

[jared.geli]

Hi again............

I'm having problems in saving the data in my datagrid to my Database

Imports System.Data.OleDb
Public Class Form2
    Dim con As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\RMC\Documents\Vehicle.accdb;")
    Dim cmd As OleDbCommand
    Dim adpt As OleDbDataAdapter
    Dim cb As OleDbCommandBuilder
    Dim sql, lol As String

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        adpt = New OleDbDataAdapter("Select * From Data1", con)

        Dim DtSet As New DataSet
        adpt.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        con.Close()
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        If TextBox1.Text = "" Then
            adpt = New OleDbDataAdapter("Select * From Data1", con)
            Dim DtSet As New DataSet
            adpt.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
        Else
            adpt = New OleDbDataAdapter("Select * From Data1 WHERE [Plate#] Like '" & TextBox1.Text & "%'", con)
            Dim DtSet As New DataSet
            adpt.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
        End If
    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click

        cb = New OleDbCommandBuilder(adpt)
        Dim DtSet As New DataSet
        Validate()
        adpt.Update(DtSet.Tables("Data1"))

    End Sub
End Class

It says "Value cannot be null. Parameter name: dataTable". I also tried changing "DtSet.Tables("Data1")" to "DtSet.Tables(0)" and it says cannot find table 0

[Begginnerdev]

Try changing your fill statement to incorporate a table name:

adpt.Fill(DtSet,"Data1")

Now you can access the table like so:

IF Not IsNothing(DtSet.Tables("Data1")) And DtSet.Tables("Data1").Rows.Count > 0 Then
    'Do work
End If

[jared.geli]

still getting some error but this time it's object reference not set to an instance of an object

[Begginnerdev]

On what line of code is the null reference being thrown?

[jared.geli]

This one

 If Not IsNothing(DtSet.Tables("Data1")) And DtSet.Tables("Data1").Rows.Count > 0 Then

it says Object reference not set to an instance of an object.

[Begginnerdev]

When stepping though the code which part is showing null?

DtSet or Data1?

[kRod]

I see in 3 subs you are creating the same named dataset--> DtSet. The DataSet needs to be Global enough for the consumers of it within the project. This is probably why you are getting the * object reference not set to an instance of an object* error.

[jared.geli]

Thanks Begginerdev, kRod! Fixed the error about the object reference error now I'm getting another error and it says:
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

By the way here's my code for now

Imports System.Data.OleDb
Public Class Form2

    Dim adpt As OleDbDataAdapter
    Dim DtSet As DataSet
    Dim cmd As OleDbCommand

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=C:\Vehicle.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Data1", con)
        con.Open()
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        DtSet = New DataSet()
        adpt.Fill(DtSet, "Data1")
        DataGridView1.DataSource = DtSet.Tables("Data1").DefaultView
        con.Close()
        con = Nothing
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Button1.Click
        Me.Validate()
        Me.adpt.Update(Me.DtSet.Tables("Data1"))
    End Sub

[jared.geli]

Update: I put primary key in my Table because it is required. I can add records now but I can't edit and delete records. I didn't include the the column where the primary key is. My DataGridView1 is unbounded

Imports System.Data.OleDb
Public Class Form2
    Dim adpt As OleDbDataAdapter
    Dim DtSet As DataSet
    'Dim cmd As OleDbCommand

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=G:\Office\Vehicle.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT DateFile, Plate, Driver, OdometerBefore, OdometerAfter, Gas, CostofGas FROM Data1", con)
        con.Open()
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        DtSet = New DataSet
        adpt.Fill(DtSet, "Data1")
        DataGridView1.DataSource = DtSet.Tables("Data1").DefaultView
        con.Close()
        con = Nothing
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Validate()
        Me.adpt.Update(Me.DtSet.Tables("Data1"))
        Me.DtSet.AcceptChanges()
    End Sub

End Class

[jared.geli]

I fixed the error I just need to have a primary key but I want now is hide the column where the primary key is. I don't want not to include it in Select Query because the "UpdateCommand is not supported against a SelectCommand that does not return any key column information" will pop up gain. I just want to hide the entire column

[jared.geli]

I already figured out how to hide it thanks again Begginnerdev and kRod!

Datagridview1.columns(0).Visible = False 
'0 means the first column
'In my case I want to hide the primary key which is the first column