how to connect ms access 2010 and vs2010

[topsykretts]

Program crash in "cmd.ExecuteNonQuery()" row

Public Class Form1
    Dim conn As New OleDb.OleDbConnection
    Private Sub RefreshData()
        If Not conn.State = ConnectionState.Open Then
            'open connection
            conn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT Oznaka as [Oznaka], " & _
                                             "ReN as [ReN], RmN " & _
                                             " FROM Tabela ORDER BY Oznaka", conn)
        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.DataGridView1.DataSource = dt

        'close connection
        conn.Close()
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As New OleDb.OleDbCommand
        If Not conn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            conn.Open()
        End If

        cmd.Connection = conn
        'check whether add new or update
        If Me.txtOznaka.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO Tabela(Oznaka, ReN, RmN) " & _
                                " VALUES('" & Me.txtOznaka.Text & "','" & Me.txtReN.Text & "','" & _
                                Me.txtRmN.Text & "'')"

            cmd.ExecuteNonQuery()
        Else
            'update data in table
            cmd.CommandText = "UPDATE Tabela " & _
                        " SET Oznaka=" & Me.txtOznaka.Text & _
                        ", ReN='" & Me.txtReN.Text & "'" & _
                        ", RmN='" & Me.txtRmN.Text & "'" & _
                                   " WHERE Oznaka=" & Me.txtOznaka.Tag
            cmd.ExecuteNonQuery()

        End If
        'refresh data in list
        RefreshData()
        'clear form
        'Me.btnClear.PerformClick()

        'close connection
        conn.Close()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDb.OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\baza.mdb"
        '
        'get data into list
        Me.RefreshData()
    End Sub

   

End Class

 

[jmcilhinney]

You shouldn't be calling ExecuteNonQuery at all. You're doing it backwards. Don't modify the database first and then requery the database. Modify your DataTable first, which will be reflected in the grid, and then use the same data adapter to save the changes back to the database. Check this out for some ADO.NET examples:

Retrieving and Saving Data in Databases

You should concentrate on the ones using a data adapter. Also not the use of parameters rather than string concatenation. Finally, you don't need to use Application.StartupPath when you can do this:

conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=|DataDirectory|\baza.mdb"

 

[topsykretts]

Thank you jmcilhinney. Your solution helps me and my program is almost done.