如何使用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 TryWhen 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 TryAnd 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屋!