0

At the beginning welcome all.

I have a big problem and I can not deal with it. I would like to refresh dategridview after adding and editing data.
I try to add ShowData() function on closing Form2 and that didn't work.
Thanks for any help or advises :)

and link to program - Click Here

Form1

    Imports System.Data.OleDb
    Public Class Form1
        Public dbconn As New OleDbConnection
        Dim adt As New OleDbDataAdapter
        Dim ds As New DataSet

        Dim datatable As New DataTable
        Dim cmd As New OleDbCommand

        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dbconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"
            showData() 'show database values in datagridview
            Button1.Text = "Give"
            Button2.Text = "Return"
        End Sub
        Public Sub showData()
            Dim dbcommand As String
            dbcommand = "SELECT * FROM keys"
            adt = New OleDbDataAdapter(dbcommand, dbconn)
            datatable = New DataTable
            adt.Fill(datatable)
            DataGridView1.DataSource = datatable
        End Sub

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim form As New Form2
            form.Button3.Text = "Add"
            dbconn.Dispose()
            dbconn.Close()
            form.ShowDialog()
        End Sub

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim form As New Form2(CInt(CStr(DataGridView1.CurrentRow.Cells(0).Value)))
            form.Button3.Text = "Edit"
            form.ComboBox1.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString()
            form.TextBox2.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString()
            form.TextBox2.Enabled = False
            form.ComboBox1.Enabled = False
            dbconn.Dispose()
            dbconn.Close()

            form.ShowDialog()
        End Sub

        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            showData()
        End Sub
    End Class

Form2

Imports System.Data.OleDb
    Public Class Form2
        Dim provider As String
        Dim dataFile As String
        Dim connString As String
        Dim sql As String
        Dim myconn As OleDbConnection = New OleDbConnection

        Private currentRowIdentifier As Integer

        Public Sub New()
            ' This call is required by the designer.
            InitializeComponent()

            ' Add any initialization after the InitializeComponent() call.
        End Sub

        Public ReadOnly Property CurrentId As Integer
            Get
                Return currentRowIdentifier
            End Get
        End Property
        Public Sub New(ByVal pIdentifier As Integer)

            ' This call is required by the designer.
            InitializeComponent()

            ' Add any initialization after the InitializeComponent() call.
            currentRowIdentifier = pIdentifier
        End Sub

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            myconn.Close()
            Me.Close()
        End Sub

        Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ComboBox1.Items.Add("Office1")
            ComboBox1.Items.Add("Office2")
            ComboBox1.Items.Add("Key1")
            Label1.Text = "Key"
            Label2.Text = "Give"
            Label3.Text = "Name"
            Label4.Text = "Return"
            TextBox0.Visible = False
        End Sub
        Sub Add()
            Dim main As New Form1
            provider = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"
            connString = provider & dataFile
            myconn.ConnectionString = connString
            myconn.Open()
            Dim str As String
            str = "Insert into keys([1], [2], [3], [4]) Values (?,?,?,?)"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myconn)
            cmd.Parameters.Add(New OleDbParameter("key", CType(ComboBox1.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("give", CType(TextBox2.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("name", CType(TextBox3.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("return", CType(TextBox4.Text, String)))
            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                main.showData()
                myconn.Close()
            Catch ex As Exception
                MessageBox.Show("Error")
            End Try
            Me.Close()
        End Sub
        Sub Edit()
            Dim main As New Form1

            If String.IsNullOrWhiteSpace(myconn.ConnectionString) Then
                myconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"
            End If
            If myconn.State = ConnectionState.Closed Then
                myconn.Open()
            End If

            If ComboBox1.Text <> "" And TextBox2.Text <> "" And TextBox3.Text <> "" And TextBox4.Text <> "" Then

                sql = "UPDATE KEYS SET [1] = @key, 2 = @give, 3 = @name, 4 = @ret WHERE id = @id"

                Dim cmd As OleDbCommand = New OleDbCommand(sql, myconn)
                cmd.Parameters.AddWithValue("@key", ComboBox1.Text)
                cmd.Parameters.AddWithValue("@give", TextBox2.Text)
                cmd.Parameters.AddWithValue("@name", TextBox3.Text)
                cmd.Parameters.AddWithValue("@ret", TextBox4.Text)
                cmd.Parameters.AddWithValue("@id", currentRowIdentifier)

                Dim Affected As Integer = 0
                Try
                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        MessageBox.Show("Updated")
                    Else
                        MessageBox.Show("Updated failed")
                    End If
                    cmd.Dispose()
                    myconn.Close()
                    Me.Close()
                Catch ex As Exception
                    MessageBox.Show($"Error: {ex.Message}")
                End Try
            Else
                MessageBox.Show("Empty fields!")
            End If
        End Sub

        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            If Button3.Text = "Add" Then
                Add()
            End If
            If Button3.Text = "Edit" Then
                Edit()
            End If
        End Sub
    End Class
2
Contributors
5
Replies
39
Views
3 Months
Discussion Span
Last Post by xrj
0

Besides the need of brackets for sql in reserved names as in [key] or [name], to communicate between form1 and form2 there may be a raising event.

Imports System.Data.OleDb
Public Class Form1
    Public dbconn As New OleDbConnection
    Dim adt As New OleDbDataAdapter
    Dim ds As New DataSet

    Dim datatable As New DataTable
    Dim cmd As New OleDbCommand
    WithEvents form2 As New Form2
    Dim connStr As String = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dbconn.ConnectionString = connStr
        showData() 'show database values in datagridview
        Button1.Text = "Give"
        Button2.Text = "Return"
    End Sub
    Public Sub showData()
        dbconn.ConnectionString = connStr
        Dim dbcommand As String
        dbcommand = "SELECT * FROM keys"
        adt = New OleDbDataAdapter(dbcommand, dbconn)
        datatable = New DataTable
        adt.Fill(datatable)
        DataGridView1.DataSource = datatable
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        form2.Button3.Text = "Add"
        dbconn.Dispose()
        dbconn.Close()
        form2.ShowDialog()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim form As New Form2(CInt(CStr(DataGridView1.CurrentRow.Cells(0).Value)))
        form.Button3.Text = "Edit"
        form.ComboBox1.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString()
        form.TextBox2.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString()
        form.TextBox2.Enabled = False
        form.ComboBox1.Enabled = False
        dbconn.Dispose()
        dbconn.Close()

        form.ShowDialog()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        showData()
    End Sub

    Private Sub form2_UpdateDGV() Handles form2.UpdateDGV
        showData()
    End Sub
End Class

Edited by xrj

0
Imports System.Data.OleDb
Public Class Form2
    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim sql As String
    Dim myconn As OleDbConnection = New OleDbConnection

    Event UpdateDGV()
    Private currentRowIdentifier As Integer

    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
    End Sub

    Public ReadOnly Property CurrentId As Integer
        Get
            Return currentRowIdentifier
        End Get
    End Property
    Public Sub New(ByVal pIdentifier As Integer)

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        currentRowIdentifier = pIdentifier
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        myconn.Close()
        Me.Hide()
    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ComboBox1.Items.Add("Office1")
        ComboBox1.Items.Add("Office2")
        ComboBox1.Items.Add("Key1")
        Label1.Text = "Key"
        Label2.Text = "Give"
        Label3.Text = "Name"
        Label4.Text = "Return"
        TextBox0.Visible = False
    End Sub
    Sub Add()
        provider = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"
        connString = provider & dataFile
        myconn.ConnectionString = connString
        myconn.Open()
        Dim str As String
        str = "Insert into keys([key],give,[name],return) VALUES (?,?,?,?)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myconn)
        cmd.Parameters.AddWithValue("@key", ComboBox1.Text)
        cmd.Parameters.AddWithValue("@give", TextBox2.Text)
        cmd.Parameters.AddWithValue("@name", TextBox3.Text)
        cmd.Parameters.AddWithValue("@return", TextBox4.Text)
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myconn.Close()
            RaiseEvent UpdateDGV()
        Catch ex As Exception
            MessageBox.Show("Error")
        End Try
        Me.Close()
    End Sub
    Sub Edit()
        Dim main As New Form1

        If String.IsNullOrWhiteSpace(myconn.ConnectionString) Then
            myconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=key.mdb"
        End If
        If myconn.State = ConnectionState.Closed Then
            myconn.Open()
        End If

        If ComboBox1.Text <> "" And TextBox2.Text <> "" And TextBox3.Text <> "" And TextBox4.Text <> "" Then

            sql = "UPDATE KEYS SET [1] = @key, 2 = @give, 3 = @name, 4 = @ret WHERE id = @id"

            Dim cmd As OleDbCommand = New OleDbCommand(sql, myconn)
            cmd.Parameters.AddWithValue("@key", ComboBox1.Text)
            cmd.Parameters.AddWithValue("@give", TextBox2.Text)
            cmd.Parameters.AddWithValue("@name", TextBox3.Text)
            cmd.Parameters.AddWithValue("@ret", TextBox4.Text)
            cmd.Parameters.AddWithValue("@id", currentRowIdentifier)

            Dim Affected As Integer = 0
            Try
                Affected = cmd.ExecuteNonQuery()
                If Affected = 1 Then
                    MessageBox.Show("Updated")
                Else
                    MessageBox.Show("Updated failed")
                End If
                cmd.Dispose()
                myconn.Close()
                Me.Close()
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.Message}")
            End Try
        Else
            MessageBox.Show("Empty fields!")
        End If
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If Button3.Text = "Add" Then
            Add()
        End If
        If Button3.Text = "Edit" Then
            Edit()
        End If
    End Sub
End Class
0

I get no error with the above code. You may include Try-Catchs, if you do know how, and see a more explanatory error.

0

Sincerely, I just have tried the 'add' method for which you were asking, at least I think so. The rest of code is your task.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.