S
Stavros Papadakis
Guest
Hi,
I m new in Basic .net. I use Access 2010 and I want to:
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.
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
Continue reading...
I m new in Basic .net. I use Access 2010 and I want to:
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.
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
Continue reading...