如何使用 VB 和 SQL 将记录添加到 Access 中的关系数据库 [英] How to use VB and SQL to add Records to a Relational Database in Access

问题描述

我正在使用 Visual Basic 创建表单应用程序，作为大学项目的一部分.我已经使用 SQL 语句从 access 数据库中读取数据，但在写入时遇到了一些问题.我想冒险猜测这是由于数据库在表之间存在关系.

I'm working in Visual Basic to create a forms applications as part of a college project. I've used SQL statements to read from the access database but I'm having some trouble writing to it. I'd like to hazard a guess it's due to the database having relationships between tables.

这是我第一次尝试做一些实质性的事情，vb 不是我选择的语言.期望代码充其量是糟糕的.如果有人提供我可以用来改进的资源链接，我将不胜感激.

This is my first go at making something substantial, and vb is not my language of choice. Expect the code to be poor at best. If anyone has links to resources that I could use to improve I'd be immensely grateful.

异常:

抛出异常:'System.Data.OleDb.OleDbException:'没有为一个或多个必需参数提供值.'

Exception thrown: 'System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.'

异常位置:'commAddToStaff.ExecuteNonQuery()'

Exception location: 'commAddToStaff.ExecuteNonQuery()'

两个 try 语句在运行时都会捕获异常.我尝试在参数中提供数据，而不是使用文本框中的数据，但这并没有解决问题.

Both try statements are catching exceptions when ran. I've attempted providing data in the parameters rather than using data from a text box, but this hasn't resolved the issue.

代码:

Private Sub btnAddStaffMember_Click(sender As Object, e As EventArgs) Handles btnAddStaffMember.Click

    'Dimension tblStaff Parameters 
    Dim AddEmployeeIDParam As New OleDb.OleDbParameter("@AddEmployeeID", txtAddEmployeeID.Text)
    Dim AddForenameParam As New OleDb.OleDbParameter("@AddForename", txtAddForename.Text)
    Dim AddSurnameParam As New OleDb.OleDbParameter("@AddSurname", txtAddSurname.Text)
    Dim AddDOBParam As New OleDb.OleDbParameter("@AddDOB", txtAddDOB.Text)
    Dim AddUserTierParam As New OleDb.OleDbParameter("@AddUserTier", txtAddUserTier.Text)

    'Dimension tblContacts Parameters
    Dim conContact As New OleDb.OleDbConnection("Provider=Microsoft.......")
    Dim commContactCount As New OleDb.OleDbCommand("Select Count(*) FROM tblContacts", conContact)
    commContactCount.Connection.Open()                 
    Dim ContactID = commContactCount.ExecuteScalar + 1     'Calculate the contactID of the new record
    commContactCount.Connection.Close()                  'Close the connection
    Dim AddContactIDParam As New OleDb.OleDbParameter("@AddContactID", ContactID)
    Dim AddAddressParam As New OleDb.OleDbParameter("@AddAddress", txtAddAddress.Text)
    Dim AddPostcodeParam As New OleDb.OleDbParameter("@AddPostcode", txtAddPostcode.Text)
    Dim AddEmailParam As New OleDb.OleDbParameter("@AddEmail", txtAddEmail.Text)
    Dim AddMobileNoParam As New OleDb.OleDbParameter("@AddMobileNo", txtAddMobileNumber.Text)

    Dim conAddToStaff As New OleDb.OleDbConnection("Provider=Microsoft....")
    Dim commAddToStaff As New OleDb.OleDbCommand("Insert Into tblStaff (EmployeeID, Forename, Surname, DOB, User_Tier, ContactID) Values (@AddEmployeeID, @AddForename, @AddSurname, @AddDOB, @AddUserTier, @AddContactID)", conAddToStaff)
    commAddToStaff.Parameters.Add(AddEmployeeIDParam)
    commAddToStaff.Parameters.Add(AddForenameParam)
    commAddToStaff.Parameters.Add(AddSurnameParam)
    commAddToStaff.Parameters.Add(AddDOBParam)
    commAddToStaff.Parameters.Add(AddUserTierParam)

    Dim commAddToContact As New OleDb.OleDbCommand("Insert Into tblContacts (ContactID, Address, Postcode, Email, Mobile_Number) Values (@AddContactID, @AddAddress, @AddPostcode, @AddEmail, @AddMobileNo)", conContact)
    commAddToContact.Parameters.Add(AddContactIDParam)
    commAddToContact.Parameters.Add(AddAddressParam)
    commAddToContact.Parameters.Add(AddPostcodeParam)
    commAddToContact.Parameters.Add(AddEmailParam)
    commAddToContact.Parameters.Add(AddMobileNoParam)

    Try
        commAddToStaff.Connection.Open()                 'Open a connection to the database
        commAddToStaff.ExecuteNonQuery()                 'Execute the command
        commAddToStaff.Connection.Dispose()              'Remove unmanaged resources
        commAddToStaff.Connection.Close()                'Close the connection
    Catch ex As Exception
        MessageBox.Show("Error with staff")
    End Try

    Try
        commAddToContact.Connection.Open()                 'Open a connection to the database
        commAddToContact.ExecuteNonQuery()                 'Execute the command
        commAddToContact.Connection.Dispose()              'Remove unmanaged resources
        commAddToContact.Connection.Close()                'Close the connection
    Catch ex As Exception
        MessageBox.Show("Error with contacts")
    End Try

    MessageBox.Show("Reached")

    Me.Hide()       'Close the Current screen
    StaffDB_Add_Staff_Security_Question.Show()  'Open the Add Security Question Screen
End Sub

推荐答案

您在此处将六个值插入到六列中:

You are inserting six values into six columns here:

Dim commAddToStaff As New OleDb.OleDbCommand("Insert Into tblStaff (EmployeeID, Forename, Surname, DOB, User_Tier, ContactID) Values (@AddEmployeeID, @AddForename, @AddSurname, @AddDOB, @AddUserTier, @AddContactID)", conAddToStaff)

但您只向此处的命令添加五个参数:

but you only add five parameters to the command here:

commAddToStaff.Parameters.Add(AddEmployeeIDParam)
commAddToStaff.Parameters.Add(AddForenameParam)
commAddToStaff.Parameters.Add(AddSurnameParam)
commAddToStaff.Parameters.Add(AddDOBParam)
commAddToStaff.Parameters.Add(AddUserTierParam)

SQL 代码中 @AddContactID 占位符的参数在哪里?

Where's the parameter for the @AddContactID placeholder in the SQL code?

作为记录，以下是我倾向于为此类任务编写代码的方式，忽略生成 ContactID 值的可怕方式:

For the record, here's how I would tend to write code for that sort of task, ignoring the horrible way you're generating the ContactID value:

Using connection As New OleDbConnection("connection string here")
    connection.Open()

    Dim contactCount As Integer

    Using contactCountCommand As New OleDbCommand("SELECT COUNT(*) FROM tblContacts", connection)
        contactCount = CInt(contactCountCommand.ExecuteScalar())
    End Using

    Dim contactId = contactCount + 1

    Using staffCommand As New OleDbCommand("INSERT INTO tblStaff (EmployeeID, Forename, Surname, DOB, User_Tier, ContactID) Values (@EmployeeID, @Forename, @Surname, @DOB, @User_Tier, @ContactID)", connection)
        With staffCommand.Parameters
            .Add("@EmployeeID", OleDbType.VarChar, 50).Value = txtAddEmployeeID.Text
            .Add("@Forename", OleDbType.VarChar, 50).Value = txtAddForename.Text
            .Add("@Surname", OleDbType.VarChar, 50).Value = txtAddSurname.Text
            .Add("@DOB", OleDbType.Date).Value = CDate(txtAddDOB.Text) 'Why isn't this coming from a DateTimePicker?
            .Add("@User_Tier", OleDbType.VarChar, 50).Value = txtAddUserTier.Text
            .Add("@ContactID", OleDbType.Integer).Value = contactId
        End With

        staffCommand.ExecuteNonQuery()
    End Using

    Using contactCommand As New OleDbCommand("INSERT INTO tblContacts (ContactID, Address, Postcode, Email, Mobile_Number) Values (@ContactID, @Address, @Postcode, @Email, @Mobile_Number)", connection)
        With contactCommand.Parameters
            .Add("@ContactID", OleDbType.Integer).Value = contactId
            .Add("@Address", OleDbType.VarChar, 50).Value = txtAddAddress.Text
            .Add("@Postcode", OleDbType.VarChar, 50).Value = txtAddPostcode.Text
            .Add("@Email", OleDbType.VarChar, 50).Value = txtAddEmail.Text
            .Add("@Mobile_Number", OleDbType.VarChar, 50).Value = txtAddMobileNumber.Text
        End With

        contactCommand.ExecuteNonQuery()
    End Using
End Using

这篇关于如何使用 VB 和 SQL 将记录添加到 Access 中的关系数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！