How do I insert, update, delete records in access table using VB 2015

Question

1.00/5 (1 vote)

See more:

Hi Friends,

I am using Visual Basic-2015 and developing a project using Access Database in back end.My codes are given below. But after running code, it is neither Add records in access table nor given any error. What I committing mistake ? Please help...

Thanks in advance.
Mohan

What I have tried:

VB.NET

Imports System.Data.OleDb
Module Module1

    Public Dbconnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FFLeaves\FFLeaves.mdb;Persist Security Info=True;Jet OLEDB:Database Password=01935097 ")


    ' For Adding New Employee

    Public AddEmpCmd As New OleDbCommand("Select * from Emp_Master", Dbconnection)
    Public AddEmpAdapter As New OleDbDataAdapter(AddEmpCmd)
    Public AddEmpCommandbuilder As New OleDbCommandBuilder(AddEmpAdapter)
    Public AddEmpDataSet As New DataSet
    Public AddEmpReader As OleDbDataReader

End Module

****************************************
Form1 Save Button click event
***************************************
 Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
            '********************************************************************************************
            BtnSave.Enabled = False

        Dbconnection.Open()
        AddEmpCmd.CommandType = CommandType.Text

        AddEmpCmd.CommandText = "INSERT INTO EMP_MASTER" _
            & "(CurrYear, Name) VALUES" _
            & "(@CurrYear, @Sap_No]) ; "

        Try
            AddEmpCmd.Parameters.AddWithValue("'@CurrYear'", CmbYear.Text)
            AddEmpCmd.Parameters.AddWithValue("@Sap_No", TxtSapID.Text)


            AddEmpCmd.ExecuteNonQuery()
            Dbconnection.Close()

        Catch ex As Exception

        End Try


    End Sub

Posted 2-Jul-18 5:07am

Member 13895315

Updated 10-Jun-20 12:51pm

Maciej Los

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-07-02T05:32:00

It shows no error because you swallow the exception:

VB

Try
    AddEmpCmd.Parameters.AddWithValue("'@CurrYear'", CmbYear.Text)
    AddEmpCmd.Parameters.AddWithValue("@Sap_No", TxtSapID.Text)
    
    
    AddEmpCmd.ExecuteNonQuery()
    Dbconnection.Close()

Catch ex As Exception

End Try

When you do that, it's as if the error never occured - you get no message, you get no error. And you can;t see what the problem was, because you discard all the info that tells you!

Start with this:

VB

Try
    AddEmpCmd.Parameters.AddWithValue("'@CurrYear'", CmbYear.Text)
    AddEmpCmd.Parameters.AddWithValue("@Sap_No", TxtSapID.Text)
    AddEmpCmd.ExecuteNonQuery()
    Dbconnection.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

And that will give you the basic info on the Output Pane of the debugger. add a breakpoint on the MessageBox line, and you can get further info from the Exception object using the debugger.

Once you have that, it may be clearer what the problem is - but without it you are just guessing!

Maciej Los · Answer 2 · 2018-07-02T23:27:00

I'd try to remove surrounding ['] from below line:

AddEmpCmd.Parameters.AddWithValue("'@CurrYear'", CmbYear.Text)

Note #1:

Even if official documentation states that OleDb provider does not support named parameters, i believe i can say that OleDb provider for MS Access database engine "partially supports" named parameters, but one condition have to be met: when adding parameters into OleDbParameterCollection[^] you have to preserve the order in which the parameters exists in sql statement.

Note #2:

There's good programming practice to separate bussiness logic from data access logic. See:
Walkthrough: Creating the Data Access and Business Logic Layers in ASP.NET[^]
Tutorial 1: Creating a Data Access Layer[^]
Tutorial 2: Creating a Business Logic Layer[^]
Writing a Portable Data Access Layer[^]

Finally, i'd suggest to read this excellent article:
Simplified Database Access via ADO.NET Interfaces[^]

katkot_rewsh · Answer 3 · 2020-06-10T12:51:00

for delete use something like that

Dim cnn As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\database.mdb;Persist Security Info=False;"
        cnn.Open()
        cmd.Connection = cnn
        cmd.CommandText = "delete from Drums where DrumType = '" & ComboBox7.SelectedItem & "'" 'delete from database
        cmd.ExecuteNonQuery()
        cnn.Close()
        MessageBox.Show("All data removed sucssefully")

for insert use something like that

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\database.mdb;Persist Security Info=False;"
                cnn.Open()
                cmd1.Connection = cnn
                cmd1.CommandText = "insert into Flange (Flange) VALUES ('" & Val(ComboBox14.Text) & "')"
                cmd1.ExecuteNonQuery()
                cnn.Close()

for read use something like that

Dim Cmd As New OleDbCommand 'cmd as command to use
        Cmd.Connection = conn 
        Cmd.CommandText = "select * from Drums where Lagging = '" & ComboBox12.SelectedItem & "' and Locall = '" & ComboBox11.SelectedItem & "'" 
        conn.Open() 'open connection
        dr = Cmd.ExecuteReader
        While dr.Read

            Label54.Text = dr("Code").ToString()

        End While
        dr.Close()
        conn.Close()

How do I insert, update, delete records in access table using VB 2015

3 solutions

Solution 1

Solution 2

Note #1:

Note #2:

Solution 3

Add your solution here

Preview 0