访问主键用法/角色 [英] Access Primary Key usage/role
问题描述
我的问题是有关将VB.net项目连接到Access DB的. 我必须在表中有一个主键,还是可以更改我的代码以不寻找PK?
我弹出一个错误,告诉我我没有主键.现在,如果我将"AdminID"设为主键,则系统可以正常工作.我只是在问是否必须这样做?
当前,我的组合框显示"AdminID",而我宁愿显示"AdminName"
谢谢.
Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= InfoSystem.accdb")
Dim objAdminDA As New OleDb.OleDbDataAdapter("Select *from tblAdmins", objConnection)
Dim objAdminCB As New OleDb.OleDbCommandBuilder(objAdminDA)
Dim objDataSet As New DataSet()
Public Sub Retrieve()
'Clears DataSet of any existing data
objDataSet.Clear()
'Fills schema - adds table structure information to DataSet
objAdminDA.FillSchema(objDataSet, SchemaType.Source, "tblAdmins")
'Fills DataSet with info from the DataAdapter
objAdminDA.Fill(objDataSet, "tblAdmins")
'Fill the DataSet with info from the Admin table
objAdminDA.FillSchema(objDataSet, SchemaType.Source, "tblAdmins")
objAdminDA.Fill(objDataSet, "tblAdmins")
'Empty combo box
cboxAdmin.Items.Clear()
'Loop through each row, adding the AdminName to the combo box
Dim i As Integer, strAdminID As String
For i = 1 To objDataSet.Tables("tblAdmins").Rows.Count
strAdminID = objDataSet.Tables("tblAdmins").Rows(i - 1).Item("AdminID")
cboxAdmin.Items.Add(strAdminID)
Next
'Select first item in the list
cboxAdmin.SelectedIndex = 0
FillAdminDetails()
End Sub
Public Sub FillAdminDetails()
Dim objRow As DataRow
objRow = objDataSet.Tables("tblAdmins").Rows.Find(cboxAdmin.SelectedItem.ToString)
txtStaffDept.Text = objRow.Item("Department")
txtStaffTitle.Text = objRow.Item("Title")
End Sub
Private Sub cboxAdmin_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboxAdmin.SelectedIndexChanged
FillAdminDetails()
End Sub
End Sub
您的CBO显示AdminID
,因为这就是您要填充的内容.如果将表绑定到CBO,则可以使用DisplayMember
显示一件事,并使用ValueMember
将另一件事返回到您的代码:
Private ACEConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= InfoSystem.accdb"
Private dtAdmin As DataTable
...
Private Sub SetupCBO
Dim SQL = "SELECT AdminId, AdminName FROM tblAdmin"
Using dbcon As New OleDbConnection(ACEConnStr)
Using cmd As New OleDbCommand(SQL, dbcon)
dbcon.Open()
dtAdmin = New DataTable
dtAdmin.Load(cmd.ExecuteReader())
End Using
End Using
cboAdmin.DataSource = dtAdmin
cboAdmin.DisplayMember = "AdminName" ' what to show
cboAdmin.ValueMember = "AdminId" ' what field to report
End Sub
首先要注意的是,没有代码可以直接填充CBO.使用DataSource
,cbo将从基础表中获取数据. DisplayMember
告诉它...可以显示,显示哪一列,并且ValueMember
允许您的代码能够获取该PrimaryKey/Id来肯定地知道哪个"Steve"或"Bob"正在登录. >
还请注意,您不必绝对只需要DataAdapter
即可填写一张桌子.您也不需要 DataSet
.如图所示,可以将Load
方法与DataReader
一起使用以直接填充表格. Using
块关闭并处置那些释放资源的对象.
绑定后,通常会使用SelectedValueChanged
事件并使用.SelectedValue
.
在这种情况下,SelectedItem
将是一个DataRowView
对象,因为NET创建了一个DataView
包装器.如果数据源是List(Of Employee)
或List(of Widget)
,则SelectedItem
将成为所选的Employee
或Widget
.
My question is in relation to connecting a VB.net project to an Access DB. Must I have a primary key in my table, or have is there a way to alter my code to not look for a PK?
I have an error popping up telling me that I have no primary key. Now if i make "AdminID" my primary key, the system works. I was just questioning as to whether or not this is required?
Currently my combobox displays "AdminID" where as I would rather it display "AdminName"
Thanks.
Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= InfoSystem.accdb")
Dim objAdminDA As New OleDb.OleDbDataAdapter("Select *from tblAdmins", objConnection)
Dim objAdminCB As New OleDb.OleDbCommandBuilder(objAdminDA)
Dim objDataSet As New DataSet()
Public Sub Retrieve()
'Clears DataSet of any existing data
objDataSet.Clear()
'Fills schema - adds table structure information to DataSet
objAdminDA.FillSchema(objDataSet, SchemaType.Source, "tblAdmins")
'Fills DataSet with info from the DataAdapter
objAdminDA.Fill(objDataSet, "tblAdmins")
'Fill the DataSet with info from the Admin table
objAdminDA.FillSchema(objDataSet, SchemaType.Source, "tblAdmins")
objAdminDA.Fill(objDataSet, "tblAdmins")
'Empty combo box
cboxAdmin.Items.Clear()
'Loop through each row, adding the AdminName to the combo box
Dim i As Integer, strAdminID As String
For i = 1 To objDataSet.Tables("tblAdmins").Rows.Count
strAdminID = objDataSet.Tables("tblAdmins").Rows(i - 1).Item("AdminID")
cboxAdmin.Items.Add(strAdminID)
Next
'Select first item in the list
cboxAdmin.SelectedIndex = 0
FillAdminDetails()
End Sub
Public Sub FillAdminDetails()
Dim objRow As DataRow
objRow = objDataSet.Tables("tblAdmins").Rows.Find(cboxAdmin.SelectedItem.ToString)
txtStaffDept.Text = objRow.Item("Department")
txtStaffTitle.Text = objRow.Item("Title")
End Sub
Private Sub cboxAdmin_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboxAdmin.SelectedIndexChanged
FillAdminDetails()
End Sub
End Sub
Your CBO shows AdminID
because that is what you fill it with. If you bind the table to the CBO, you can use DisplayMember
to show one thing, and ValueMember
to return another to your code:
Private ACEConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= InfoSystem.accdb"
Private dtAdmin As DataTable
...
Private Sub SetupCBO
Dim SQL = "SELECT AdminId, AdminName FROM tblAdmin"
Using dbcon As New OleDbConnection(ACEConnStr)
Using cmd As New OleDbCommand(SQL, dbcon)
dbcon.Open()
dtAdmin = New DataTable
dtAdmin.Load(cmd.ExecuteReader())
End Using
End Using
cboAdmin.DataSource = dtAdmin
cboAdmin.DisplayMember = "AdminName" ' what to show
cboAdmin.ValueMember = "AdminId" ' what field to report
End Sub
The first thing to note is that there is no code to populate the CBO directly. Using a DataSource
, the cbo will get the data from the underlying table. The DisplayMember
tells it which column to...well, display, and the ValueMember
allows your code to be able to fetch that PrimaryKey/Id to positively know which "Steve" or "Bob" is logging in.
Also note that you don't absolutely need a DataAdapter
just to fill one table. Nor do you need DataSet
. As shown, you can use the Load
method with a DataReader
to directly fill a table. The Using
blocks close and dispose of those objects freeing resources.
When bound, you would generally use the SelectedValueChanged
event and use the .SelectedValue
.
In this case, the SelectedItem
will be a DataRowView
object because NET creates a DataView
wrapper. If the data source was a List(Of Employee)
or a List(of Widget)
, the SelectedItem
will be the selected Employee
or Widget
.
这篇关于访问主键用法/角色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!