none
Edit access file in second form

    Question

  • Hello, 
    I made a program and I have a problem with editing the database using the second form. 
    This is my Program : 
    link to file -> File
     Form 1 : 

    Imports System.Data.OleDb
    Public Class Form1
        Dim 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
            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
    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 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()
            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.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()
                myconn.Close()
            Catch ex As Exception
                MessageBox.Show("Error")
            End Try
            Me.Close()
        End Sub
        Sub Edit()
            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 key='" & ComboBox1.Text & "', give='" & TextBox2.Text & "', name='" & TextBox3.Text & "', return='" & TextBox4.Text & "' where id=" & TextBox0.Text & ""
                Dim cmd As OleDbCommand = New OleDbCommand(sql, myconn)
                Try
                    cmd.ExecuteNonQuery()
                    cmd.Dispose()
                    myconn.Close()
                    Me.Close()
                Catch ex As Exception
                    MessageBox.Show("Error")
                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()
            If Button3.Text = "Edit" Then Edit()
        End Sub
    End Class



    Wednesday, December 20, 2017 1:16 PM

Answers

  • Thank you for you hint. Probably yours solution is very good, or even better then my but I think it's better to correct one small mistake that write it all over again, learn something new... and don't make more mistakes in similar projects ;)

    With no disrespect, I would abandon how you are coding in this project and look at how I do things.

    Here is the fix, search for KAREN in code for changes,

    https://1drv.ms/u/s!AtGAgKKpqdWjiRLg4Wq2n-clK6xk


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by marek_maro Wednesday, December 20, 2017 7:36 PM
    Wednesday, December 20, 2017 6:53 PM
    Moderator

All replies

  • Why all your beginner guys and girls make it so difficult for yourself. 

    Why a second form. If you really misses space than make a tabcontrol with a user control which is a panel. 

    http://dotnetrix.co.uk/tabcontrol.htm


    Success
    Cor

    Wednesday, December 20, 2017 1:47 PM
  • Hello,

    Can you please specify the issue? The more details the better as at this point looking at code does not always provide insight to the issue at hand.

    Also, you should not place data operations in your forms.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 20, 2017 2:10 PM
    Moderator
  • Hello,
    thank you for any help.

    Adding new record works, but if you want to return Key (edit record) program fails. 
    • Edited by marek_maro Wednesday, December 20, 2017 2:55 PM
    Wednesday, December 20, 2017 2:54 PM
  • I would suggest using a class, the one below is a model to create your's from. There are methods for reading, adding, updating and deleting. Each method that modifies data is wrapped in a try/catch and sets a class level property HasException to true so if a method fails and you check HasException then the Exception property hold the exception to examine. Note the add method returns the new primary key if the insert is successful.

    Public Class Operations
        ''' <summary>
        ''' Build a connection string where AppDomain.CurrentDomain.BaseDirectory
        ''' point to the same folder as the executable, if the database is in a
        ''' different folder then change it to point there.
        ''' </summary>
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database.accdb")
        }
    
        Private mHasException As Boolean
        Public ReadOnly Property HasException As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Private mException As Exception
        Public ReadOnly Property Exception As Exception
            Get
                Return mException
            End Get
        End Property
    
        Public Property CustomersDataTable As DataTable
        ''' <summary>
        ''' Initialize our DataTable
        ''' </summary>
        Public Sub New()
            CustomersDataTable = New DataTable
        End Sub
        ''' <summary>
        ''' Get data from customers table
        ''' </summary>
        Public Sub GetCustomerData()
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                ContactTitle, 
                                Country, 
                                CompanyName
                            FROM 
                                Customers 
                            ORDER BY 
                                CompanyName ASC
                        </SQL>.Value
    
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Remove customer by primary key
        ''' </summary>
        ''' <param name="CustomerId"></param>
        ''' <returns></returns>
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                mHasException = True
                mException = ex
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Update customer by primary key
        ''' </summary>
        ''' <param name="CustomerId"></param>
        ''' <param name="Name"></param>
        ''' <param name="Contact"></param>
        ''' <returns></returns>
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal Name As String, ByVal Contact As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
                        cmd.Parameters.AddWithValue("@Identifier", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                mHasException = True
                mException = ex
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Add new customer, the argument Identfier returns the new primary key for
        ''' the newly added customer record
        ''' </summary>
        ''' <param name="Name"></param>
        ''' <param name="Contact"></param>
        ''' <param name="Identfier"></param>
        ''' <returns></returns>
        Public Function AddNewRow(ByVal Name As String, ByVal Contact As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", Contact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                mHasException = True
                mException = ex
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 20, 2017 3:51 PM
    Moderator
  • Thank you for you hint. Probably yours solution is very good, or even better then my but I think it's better to correct one small mistake that write it all over again, learn something new... and don't make more mistakes in similar projects ;)
    Wednesday, December 20, 2017 5:10 PM
  • Thank you for you hint. Probably yours solution is very good, or even better then my but I think it's better to correct one small mistake that write it all over again, learn something new... and don't make more mistakes in similar projects ;)

    With no disrespect, I would abandon how you are coding in this project and look at how I do things.

    Here is the fix, search for KAREN in code for changes,

    https://1drv.ms/u/s!AtGAgKKpqdWjiRLg4Wq2n-clK6xk


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by marek_maro Wednesday, December 20, 2017 7:36 PM
    Wednesday, December 20, 2017 6:53 PM
    Moderator
  • Ok. I understand and don't feel disrespect. I know that my skills aren't at a high level. 
    But thank you for you time :)
    Wednesday, December 20, 2017 7:36 PM
  • Karen please help! Last problem is it possible to refresh Form1 using the ShowDialog() function after closing Form2 ??

    • Edited by marek_maro Wednesday, January 3, 2018 4:32 PM
    Wednesday, January 3, 2018 4:32 PM