Click here to Skip to main content
14,774,504 members
Please Sign up or sign in to vote.
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:

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
Updated 10-Jun-20 13:51pm
v3

It shows no error because you swallow the exception:
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:
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!
   
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[^]
   
v2
Comments
Member 13895315 4-Jul-18 7:10am
   
Thanks friends,

I have tried both :

1. Removed surrounding [ ' ]
2. MessageBox.Show(ex.Message)

it shows "Syntax Error in quarry expression '@Sap_No);

How can solve the problem? Please Help.
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()
   
Comments
CHill60 11-Jun-20 8:37am
   
Reasons for my downvote:
- You have provided a solution that would leave the OP vulnerable to SQL Injection attacks - see SQL Injection | OWASP[^]
tl;dr - Never concatenate user input like that to create SQL queries
- You have not actually attempted to answer the question, you have responded only to the title of the post. Please read questions carefully to make sure your solution is relevant
- You have not included any error handling - which was actually the problem with the OP's code - see Solution 1
- Also, you have used the default names for controls - completely meaningless to us
- Also, the question was answered 2 years ago

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900