在Access中编辑记录 [英] Edit a record in Access

问题描述

我是Basic .net的新手.我使用Access 2010，并且想要:

I 'm new in Basic .net. I use Access 2010 and I want to:

1.搜索记录2.编辑并3.保存.这是我写的代码.问题在于，当我更改日期等字段时，会将相同的日期添加到所有记录中.

1. search for a record 2. edit and 3. save . This is the code I wrote. The problem is that when I change a field like date, it puts same date to all records.

导入System.Data.OleDb

公开课编辑

   昏暗的提供程序作为字符串
   昏暗的dataFile作为字符串
   昏暗的connString作为字符串
   将公共myConnection设置为OleDbConnection =新的OleDbConnection
   公开Dr As OleDbDataReader

   私有子IOQBindingNavigatorSaveItem_Click(ByVal发送者为System.Object，ByVal e为System.EventArgs)
       Me.Validate()
       Me.IOQBindingSource.EndEdit()
       Me.TableAdapterManager.UpdateAll(Me.InquiriesDBDataSet)

   结束子

    Private Sub Edit_Load(ByVal发送者为System.Object，ByVal e为System.EventArgs)处理MyBase.Load
       'TODO:这行代码将数据加载到'InquiriesDBDataSet.IOQ'表中.您可以根据需要移动或删除它.
       Me.IOQTableAdapter.Fill(Me.InquiriesDBDataSet.IOQ)

       provider ="Provider = Microsoft.ACE.OLEDB.12.0; Data Source ="
       dataFile ="p:\ InquiriesDB.accdb"
       connString =提供程序& dataFile
       myConnection.ConnectionString = connString

   结束子

   私有子BTEditIQOSearch_Click(ByVal发送者为System.Object，ByVal e为System.EventArgs)处理BTEditIQOSearch.Click

       myConnection.Open()
       Dim str As String
       str =" SELECT * FROM IOQ WHERE(inq_ref ='"& TBEditInqRef.Text&"'))< br/>        Dim cmd As OleDbCommand =新的OleDbCommand(str，myConnection)
       dr = cmd.ExecuteReader
      而Dr.Read()

           TBEditInqCreditTerms.Text = dr("inq_credit_terms").ToString
           TBEditInqCustomer.Text = dr("inq_customer").ToString
           TBEditInqCustomerRef.Text = dr("inq_customer_ref").ToString
           TBEditInqDateCreated.Text = dr("inq_date_creat").ToString
           TBEditInqDescription.Text = dr("inq_description").ToString
           TBEditInqMakerType.Text = dr("inq_maker_type").ToString
           TBEditInqRemarks.Text = dr("inq_remarks").ToString
           TBEditInqSerNumb.Text = dr("inq_serial_num").ToString
           TBEditInqSupplier.Text = dr("inq_supplier").ToString
           TBEditInqVessel.Text = dr("inq_vessel").ToString
           TBEditOrdAmount.Text = dr("ord_amount").ToString
           TBEditOrdCommission.Text = dr("ord_commission").ToString
           TBEditOrdConfirmationDate.Text = dr("ord_order_conf_date").ToString
           TBEditOrdCurrency.Text = dr("ord_currency").ToString
           TBEditOrdCustomersRef.Text = dr("ord_customers_ref").ToString
           TBEditOrdCustomerVatNumber.Text = dr("ord_customers_vat_numbers").ToString
           TBEditOrdDate.Text = dr("ord_date").ToString
           TBEditOrdDeliveryDate.Text = dr("ord_delivery_date").ToString
           TBEditOrdDeliveryInstructions.Text = dr("ord_delivery_instr").ToString
           TBEditOrdForwInstructionDate.Text = dr("ord_forw_instr_date").ToString
           TBEditOrdInvoiceDetails.Text = dr("ord_inv_details").ToString
           TBEditOrdPaymentReceipt.Text = dr("ord_paym_receipt").ToString
           TBEditOrdReadinessDate.Text = dr("ord_rediness_date").ToString
           TBEditOrdShippingDetails.Text = dr("ord_shipp_details").ToString
           TBEditOrdSupplierRef.Text = dr("ord_suppliers_ref").ToString
           TBEditOrdTips.Text = dr("ord_tips").ToString
           TBEditOrdTipsRemarks.Text = dr("ord_tips_remarks").ToString
           TBEditOrdShipingDate.Text = dr("ord_shipp_date").ToString
           TBEditOrdInvoiceDate.Text = dr("ord_inv_date").ToString
           TBEditQuotAmount.Text = dr("quot_amount").ToString
           TBEditQuotCreditTerms.Text = dr(" _ credit_terms").ToString
           TBEditQuotCurrency.Text = dr("quot_currency").ToString
           TBEditQuotCustomerRef.Text = dr("quot_customers_ref").ToString
           TBEditQuotDelivery.Text = dr("quot_delivery").ToString
           TBEditQuotOfferSent.Text = dr("quot_offer_sent").ToString
           TBEditQuotRemarks.Text = dr("quot_remarks").ToString
           TBEditQuotReminder.Text = dr("quot_reminder").ToString
           TBEditQuotSupplierRef.Text = dr(" supplier_ref).ToString
           TBEditQuotValidity.Text = dr("quot_validity").ToString

      结束时
       myConnection.Close()

   结束子

   私有子BT_SaveIQO_Click(ByVal发送者为System.Object，ByVal e为System.EventArgs)处理BT_SaveIQO.Click

       myConnection.Open()
       Dim str As String
       str ="更新[IOQ] set [inq_credit_terms] ='" & TBEditInqCreditTerms.Text& "'，[inq_customer] ='" & TBEditInqCustomer.Text& "'，[inq_customer_ref] ='" & TBEditInqCustomerRef.Text & "' ，[inq_date_creat] ='" & TBEditInqDateCreated.Text& "' ，[inq_description] ='" & TBEditInqDescription.Text& "' ，[inq_maker_type] ='" & TBEditInqMakerType.Text& "' ，[inq_remarks] = " & TBEditInqRemarks.Text& "' ，[inq_serial_num] ='" & TBEditInqSerNumb.Text& "' ，[inq_supplier] ='" & TBEditInqSupplier.Text& "' ，[inq_vessel] ='" & TBEditInqVessel.Text& "' ，[ord_amount] ='" & TBEditOrdAmount.Text& "' ，[ord_commission] ='" & TBEditOrdCommission.Text& "' ，[ord_order_conf_date] ='" & TBEditOrdConfirmationDate.Text& "' ，[ord_currency] ='" & TBEditOrdCurrency.Text& "' ，[ord_customers_ref] ='" & TBEditOrdCustomersRef.Text& "' ，[ord_customers_vat_numbers] ='" & TBEditOrdCustomerVatNumber.Text& "'，[ord_date] ='" & TBEditOrdDate.Text& "' ，[ord_delivery_date] ='" & TBEditOrdDeliveryDate.Text& "' ，[ord_delivery_instr] ='" & TBEditOrdDeliveryInstructions.Text& "'，[ord_forw_instr_date] ='" & TBEditOrdForwInstructionDate.Text& "' ，[ord_inv_details] ='" & TBEditOrdInvoiceDetails.Text& "' ，[ord_paym_receipt] ='" & TBEditOrdPaymentReceipt.Text& "'，[ord_rediness_date] ='" & TBEditOrdReadinessDate.Text& "'，[ord_shipp_details] ='" & TBEditOrdShippingDetails.Text& "'，  [ord_suppliers_ref] ='" & TBEditOrdSupplierRef.Text& "' ，[ord_tips] ='" & TBEditOrdTips.Text& "' ，[ord_tips_remarks] ='" & TBEditOrdTipsRemarks.Text & "' ，[ord_shipp_date] ='" & TBEditOrdShipingDate.Text& "'，[ord_inv_date] ='" & TBEditOrdInvoiceDate.Text& "'，[quot_amount] ='" & TBEditQuotAmount.Text& "' ，[quot_credit_terms] ='" & TBEditQuotCreditTerms.Text& "'，[quot_currency] ='" & TBEditQuotCurrency.Text& "' ，[quot_customers_ref] ='" & TBEditQuotCustomerRef.Text& "' ，[quot_delivery] ='" & TBEditQuotDelivery.Text & "'，[quot_offer_sent] ='" & TBEditQuotOfferSent.Text& "' ，[quot_remarks] ='" & TBEditQuotRemarks.Text& "' ，[quot_reminder] ='" & TBEditQuotReminder.Text& "' ，[quot_supplier_ref] ='" & TBEditQuotSupplierRef.Text& "'，[quot_validity] ='" & TBEditQuotValidity.Text& "'<

       Dim cmd As OleDbCommand =新的OleDbCommand(str，myConnection)
      试试
           cmd.ExecuteNonQuery()
           cmd.Dispose()
           myConnection.Close()
      异常捕获
           MsgBox(ex.Message)

      结束尝试
       Me.Close()


   结束子
结束班

Imports System.Data.OleDb

Public Class Edit

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Public myConnection As OleDbConnection = New OleDbConnection
    Public dr As OleDbDataReader

    Private Sub IOQBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.IOQBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.InquiriesDBDataSet)

    End Sub

    Private Sub Edit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'InquiriesDBDataSet.IOQ' table. You can move, or remove it, as needed.
        Me.IOQTableAdapter.Fill(Me.InquiriesDBDataSet.IOQ)

        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
        dataFile = "p:\InquiriesDB.accdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString

    End Sub

    Private Sub BTEditIQOSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTEditIQOSearch.Click

        myConnection.Open()
        Dim str As String
        str = "SELECT * FROM IOQ WHERE (inq_ref = '" & TBEditInqRef.Text & "')"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        dr = cmd.ExecuteReader
        While dr.Read()

            TBEditInqCreditTerms.Text = dr("inq_credit_terms").ToString
            TBEditInqCustomer.Text = dr("inq_customer").ToString
            TBEditInqCustomerRef.Text = dr("inq_customer_ref").ToString
            TBEditInqDateCreated.Text = dr("inq_date_creat").ToString
            TBEditInqDescription.Text = dr("inq_description").ToString
            TBEditInqMakerType.Text = dr("inq_maker_type").ToString
            TBEditInqRemarks.Text = dr("inq_remarks").ToString
            TBEditInqSerNumb.Text = dr("inq_serial_num").ToString
            TBEditInqSupplier.Text = dr("inq_supplier").ToString
            TBEditInqVessel.Text = dr("inq_vessel").ToString
            TBEditOrdAmount.Text = dr("ord_amount").ToString
            TBEditOrdCommission.Text = dr("ord_commission").ToString
            TBEditOrdConfirmationDate.Text = dr("ord_order_conf_date").ToString
            TBEditOrdCurrency.Text = dr("ord_currency").ToString
            TBEditOrdCustomersRef.Text = dr("ord_customers_ref").ToString
            TBEditOrdCustomerVatNumber.Text = dr("ord_customers_vat_numbers").ToString
            TBEditOrdDate.Text = dr("ord_date").ToString
            TBEditOrdDeliveryDate.Text = dr("ord_delivery_date").ToString
            TBEditOrdDeliveryInstructions.Text = dr("ord_delivery_instr").ToString
            TBEditOrdForwInstructionDate.Text = dr("ord_forw_instr_date").ToString
            TBEditOrdInvoiceDetails.Text = dr("ord_inv_details").ToString
            TBEditOrdPaymentReceipt.Text = dr("ord_paym_receipt").ToString
            TBEditOrdReadinessDate.Text = dr("ord_rediness_date").ToString
            TBEditOrdShippingDetails.Text = dr("ord_shipp_details").ToString
            TBEditOrdSupplierRef.Text = dr("ord_suppliers_ref").ToString
            TBEditOrdTips.Text = dr("ord_tips").ToString
            TBEditOrdTipsRemarks.Text = dr("ord_tips_remarks").ToString
            TBEditOrdShipingDate.Text = dr("ord_shipp_date").ToString
            TBEditOrdInvoiceDate.Text = dr("ord_inv_date").ToString
            TBEditQuotAmount.Text = dr("quot_amount").ToString
            TBEditQuotCreditTerms.Text = dr("quot_credit_terms").ToString
            TBEditQuotCurrency.Text = dr("quot_currency").ToString
            TBEditQuotCustomerRef.Text = dr("quot_customers_ref").ToString
            TBEditQuotDelivery.Text = dr("quot_delivery").ToString
            TBEditQuotOfferSent.Text = dr("quot_offer_sent").ToString
            TBEditQuotRemarks.Text = dr("quot_remarks").ToString
            TBEditQuotReminder.Text = dr("quot_reminder").ToString
            TBEditQuotSupplierRef.Text = dr("quot_supplier_ref").ToString
            TBEditQuotValidity.Text = dr("quot_validity").ToString

        End While
        myConnection.Close()

    End Sub

    Private Sub BT_SaveIQO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BT_SaveIQO.Click

        myConnection.Open()
        Dim str As String
        str = "update [IOQ] set [inq_credit_terms] = '" & TBEditInqCreditTerms.Text & "', [inq_customer] = '" & TBEditInqCustomer.Text & "', [inq_customer_ref] = '" & TBEditInqCustomerRef.Text & "' , [inq_date_creat] = '" & TBEditInqDateCreated.Text & "' , [inq_description] = '" & TBEditInqDescription.Text & "' , [inq_maker_type] = '" & TBEditInqMakerType.Text & "' , [inq_remarks] = '" & TBEditInqRemarks.Text & "' , [inq_serial_num] = '" & TBEditInqSerNumb.Text & "' , [inq_supplier] = '" & TBEditInqSupplier.Text & "' , [inq_vessel] = '" & TBEditInqVessel.Text & "' , [ord_amount] = '" & TBEditOrdAmount.Text & "' , [ord_commission] = '" & TBEditOrdCommission.Text & "' , [ord_order_conf_date] = '" & TBEditOrdConfirmationDate.Text & "' , [ord_currency] = '" & TBEditOrdCurrency.Text & "' , [ord_customers_ref] = '" & TBEditOrdCustomersRef.Text & "' , [ord_customers_vat_numbers] = '" & TBEditOrdCustomerVatNumber.Text & "', [ord_date] = '" & TBEditOrdDate.Text & "' , [ord_delivery_date] = '" & TBEditOrdDeliveryDate.Text & "' , [ord_delivery_instr] = '" & TBEditOrdDeliveryInstructions.Text & "', [ord_forw_instr_date] = '" & TBEditOrdForwInstructionDate.Text & "' , [ord_inv_details] = '" & TBEditOrdInvoiceDetails.Text & "' , [ord_paym_receipt] = '" & TBEditOrdPaymentReceipt.Text & "', [ord_rediness_date] = '" & TBEditOrdReadinessDate.Text & "', [ord_shipp_details] = '" & TBEditOrdShippingDetails.Text & "',  [ord_suppliers_ref] = '" & TBEditOrdSupplierRef.Text & "' , [ord_tips] = '" & TBEditOrdTips.Text & "' , [ord_tips_remarks] = '" & TBEditOrdTipsRemarks.Text & "' , [ord_shipp_date] = '" & TBEditOrdShipingDate.Text & "', [ord_inv_date] = '" & TBEditOrdInvoiceDate.Text & "', [quot_amount] = '" & TBEditQuotAmount.Text & "' , [quot_credit_terms] = '" & TBEditQuotCreditTerms.Text & "', [quot_currency] = '" & TBEditQuotCurrency.Text & "' , [quot_customers_ref] = '" & TBEditQuotCustomerRef.Text & "' , [quot_delivery] = '" & TBEditQuotDelivery.Text & "', [quot_offer_sent] = '" & TBEditQuotOfferSent.Text & "' , [quot_remarks] = '" & TBEditQuotRemarks.Text & "' , [quot_reminder] = '" & TBEditQuotReminder.Text & "' , [quot_supplier_ref] = '" & TBEditQuotSupplierRef.Text & "', [quot_validity] = '" & TBEditQuotValidity.Text & "'"

        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        Me.Close()


    End Sub
End Class

推荐答案

您的UPDATE语句需要WHERE子句，以便仅更新具有该列值的行.通常，这是唯一列，例如主键列.我无法通过查看您的代码来确定哪一列.下面是一个例子. 我还建议使用命令参数:

Your UPDATE statement requires a WHERE clause so that only the rows with that column value are updated. Generally this is a unique column such as a primary key column. I can't determine which column that would be by looking at your code. Below is an example. I also recommend using Command Parameters:

        Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        AccessConnection.Open()
        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("UPDATE Table1 SET Changed = ?, Product = ? WHERE [record id] = ?", AccessConnection)

        Dim QueryParameter As New OleDbParameter("@Param1", OleDbType.VarChar)
        QueryParameter.Value = SomeValue
        AccessCommand.Parameters.Add(QueryParameter)

        QueryParameter = New OleDbParameter("@Param2", OleDbType.VarChar)
        QueryParameter.Value = AnotherValue
        AccessCommand.Parameters.Add(QueryParameter)

        QueryParameter = New OleDbParameter("@Param3", OleDbType.VarChar)
        QueryParameter.Value = RecordIDValue
        AccessCommand.Parameters.Add(QueryParameter)

        AccessCommand.CommandType = CommandType.Text

        AccessCommand.ExecuteNonQuery()

        AccessConnection.Close()

这篇关于在Access中编辑记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！