如何使用VB 2015在访问表中插入，更新，删除记录 [英] How do I insert, update, delete records in access table using VB 2015

问题描述

嗨朋友们，



我正在使用Visual Basic-2015并在后端使用Access数据库开发项目。我的代码如下。但是在运行代码之后，它既不是在访问表中添加记录也不是给出任何错误。我犯了什么错误？请帮忙...



提前致谢。

Mohan



我尝试过：

 进口 System.Data.OleDb 
 模块 Module1 
 
 公共 Dbconnection 作为 新 OleDbConnection（ < span class =code-string> Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D：\ FFLeaves\FFLeaves.mdb; Persist Security Info = True; Jet OLEDB：Database Password = 01935097）
 
 
 ' 添加新员工 
 
 公共 AddEmpCmd 作为 新 OleDbCommand（ 从Emp_中选择* Master，Dbconnection）
 公共 AddEmpAdapter  As  新 OleDbDataAdapter（AddEmpCmd）
 公共 AddEmpCommandbuilder  As  新 OleDbCommandBuilder（AddEmpAdapter）
 公共 AddEmpDataSet  As  新 DataSet 
 公共 AddEmpReader  As  OleDbDataReader 
 
 结束 模块 
 
 **************************************** 
 Form1保存按钮单击 event  
 ********************************* ****** 
 私人  Sub  BtnSave_Click（sen der 作为 对象，e  As  EventArgs） 句柄 BtnSave.Click 
 '  *** ************************************************** ***************************************  
 BtnSave.Enabled =  False  
 
 Dbconnection.Open（）
 AddEmpCmd.CommandType = CommandType.Text 
 
 AddEmpCmd。 CommandText =   INSERT INTO EMP_MASTER _ 
&  （CurrYear，Name）VALUES _ 
&  （@ CurrYear，@Sap_No]）; 
 
 尝试 
 AddEmpCmd.Parameters.AddWithValue（ '@ CurrYear '，CmbYear.Text）
 AddEmpCmd.Parameters.AddWithValue（  @ Sap_No，TxtSapID.Text）
 
 
 AddEmpCmd.ExecuteNonQuery（）
 Dbconnection.Close（）
 
  Catch  ex  As 例外
 
 结束 尝试 
 
 
 结束  Sub  

解决方案

它显示没有错误，因为你吞下了例外：

  T. ry  
 AddEmpCmd.Parameters.AddWithValue（ '@ CurrYear'， CmbYear.Text）
 AddEmpCmd.Parameters.AddWithValue（  @ Sap_No，TxtSapID.Text ）
 
 
 AddEmpCmd.ExecuteNonQuery（）
 Dbconnection.Close（）
 
  Catch  ex  As 例外
 
 结束 尝试 

当你这样做时，就好像错误从未发生过 - 你没有得到任何消息，你没有得到任何错误。你可以看看问题是什么，因为你放弃了告诉你的所有信息！



从这开始：

< pre lang =vb> 尝试
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 尝试

这将为您提供有关调试器输出窗格的基本信息。在MessageBox行上添加一个断点，你可以使用调试器从Exception对象获取更多信息。



一旦你有了这个，可能会更清楚问题是什么是 - 但没有它你只是猜测！

我试图从下面的行删除周围的[']：

 AddEmpCmd.Parameters.AddWithValue（'@ CurrYear'，CmbYear.Text）

注意＃1：

即使官方文档声明OleDb提供程序不支持命名参数，我相信我可以说MS Access数据库引擎的OleDb提供程序部分支持命名参数，但是一个条件必须满足：在 OleDbParameterCollection <中添加参数时/ a> [ ^ ]您必须保留sql语句中参数的顺序。

注意＃2：

将业务逻辑与数据访问逻辑分开是一种很好的编程习惯。请参阅：

演练：创建数据访问和ASP.NET中的业务逻辑层 [ ^ ]

教程1：创建数据访问层 [ ^ ]

教程2：创建业务逻辑层 [< a href =https://msdn.microsoft.com/en-us/library/aa581779.aspxtarget =_ blanktitle =New Window> ^ ]

编写可移植数据访问层 [ ^ ]



Finall y，我建议阅读这篇优秀的文章：

通过ADO.NET接口简化数据库访问 [ ^ ]

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

解决方案

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[^]

这篇关于如何使用VB 2015在访问表中插入，更新，删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！