locked
Data transfer from one table to another RRS feed

  • Question

  • I am working in Vb Express with MsAccess as database. I have a form with Listview data from Listview is transferred to the TableCustomer it is retrieved from that table back to ListView and after some additions is stored back in  Tabloe Customer.
    I have another table Transaction in the same database which has some common fields of Customer Table in addition to its own fields. My two questions are :

    1. How the data of one table can be automatically transferred to other table in MsAccess?
    For example:
    How data of fields(CustId,Description,Quantity,Price,Amount) of TableCustomer(CustId,Description,Quantity,Price,Amount) can be transferred to TableTransaction(TransId,TransDate,AccountId,Description,Quantity, Price,Amount,CustId).
    2. How the Date in the column TransDate can be populated with the current date of the system.
    I hope i have been able to clear my queries.
    Please guide.
    Sunday, June 21, 2009 7:07 PM

Answers

  • Hi Sazd1,

    Thank you for your clarification. I can understand your issue better.

    For updating the data into Access, you can try the following code:
    Dim cmdText As String = "UPDATE Customers SET CustId = XXXX, Description = XXXX, Quantity = XXXX, Price = XXXX, Amount = XXXX" 
    WHERE [Filter]"
    Hope this helps.

    Thanks

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun Feng Monday, June 29, 2009 1:42 AM
    Thursday, June 25, 2009 4:07 AM

All replies

  • Hi Sazd1,

    How do you use Listview to show the data from Access? Like Datagridview? Do you mean that you want to store back any changes to TableCustomer and also store the related data to Table Transaction? Please clarify your issue more.

    For your second question, you can try getting the current system date when the application begins to transfer the data. Then upload this date to TableTransaction.

    Thanks
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 23, 2009 10:16 AM
  • Thanks for your reply YiChun.
    this is my code to show data in ListView from Access:
    'Transfer of Data from Table in MsAccess to ListView
            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                Dim drow As DataRow = ds.Tables(0).Rows(i)
     Dim lvi As ListViewItem = New   ListViewItem(drow("CustId").ToString)
                lvi.SubItems.Add(drow("Description").ToString)
                lvi.SubItems.Add(drow("Quantity").ToString)
    lvi.SubItems.Add(drow("Price").ToString)
    lvi.SubItems.Add(drow("Amount").ToString)
                ListView1.Items.Add(lvi)
            Next
    
    Yes you are very right to understand my query. I want to make changes in table Customers through Listview which i am able to do at present. Now the problem is to update these changes in Customer Table and also, as you rightly understood, to  store the related data in Table Transaction.
    Kindly advise.
    Tuesday, June 23, 2009 11:39 AM
  • Hi Sazd1,

    I've tried reproduced the code that you posted. It shows data which looks like Datagridview as the following.


    For updating TableCustomer :
    After the data in the Listview is changed, you can first delete the whole TableCustomer and insert all the data in the Listview into Access Table.
    Delete the Table:
    Dim SQLString As String = "Delete From TableCustomer"
    Insert the Table:
    Dim SQLString As String = "Insert into TableCustomer (CustId, Description, Quantity, Price, Amount) Values (XXXXX,XXXXX......)"

    For updating TableTransaction :
    You need to get the current system date like:
    Dim Today As Date = Date.Today
    
    Then update the related data in the Listview and the current system date to Access。

    I hope this can shed some light on your issue.

    Thanks

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Edited by YiChun Chen Wednesday, June 24, 2009 6:02 AM typo
    Wednesday, June 24, 2009 6:02 AM
  • Hi YiChun
    Thanks for your interest to reply my query. I really appreciate that.
    YiChun, the scenario of my application is like that:
    CustomerA comes and places some orders which are shown in LIstView and then inserted in Table Customers with an Id of CustomerA. Then CustomerB comes and the process is repeated with CustomerB. Now again CustomerA comes and places some additions in his orders , his previous data is retrieved from Table Customers with reference to his Id and displayed in LIstView and after making further additions to his data in ListView the new revised data from LIstView is to be transferred to the table Customers. My problem is that I cannot delete the table Customers and insert the new data of CustomerA in that table because the table Customers contains data of many customers. So my issue is how to update the new data of customerA in table Customers(it will be a parameterized query for some sort of updation or alteration in his existing data in table Customers).
    I hope i have been able to clear my point.
    Thanks once again for your interest to reply my query.
    Here is my code for insertion of data from ListView to table Customers.
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            Dim cmdText As String = "INSERT INTO Customers(CustId,Description,Quantity,Price,Amount) VALUES (?,?,?,?,?)"
            If con.State = ConnectionState.Closed Then con.Open()
            Dim Command As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
            Command.CommandType = CommandType.Text 'The default is CommandType.Text 
            Dim i As Integer
            For i = 0 To ListView1.Items.Count - 1
                With Command.Parameters
                    .Add("@CustId", OleDb.OleDbType.VarChar).Value = ListView1.Items(i).SubItems(0).Text
                    .Add("@Description", OleDb.OleDbType.VarChar).Value = ListView1.Items(i).SubItems(1).Text
                    .Add("@Quantity", OleDb.OleDbType.VarChar).Value = ListView1.Items(i).SubItems(2).Text
                    .Add("@Price", OleDb.OleDbType.VarChar).Value = ListView1.Items(i).SubItems(3).Text
                    .Add("@Amount", OleDb.OleDbType.VarChar).Value = ListView1.Items(i).SubItems(4).Text
                End With
            Next
            Command.ExecuteNonQuery()
            MsgBox("Record has been inserted  ")
    
        End Sub
    For my scenerio this insert code will be some sort of update query code. Please advise.
    Thursday, June 25, 2009 3:42 AM
  • Hi Sazd1,

    Thank you for your clarification. I can understand your issue better.

    For updating the data into Access, you can try the following code:
    Dim cmdText As String = "UPDATE Customers SET CustId = XXXX, Description = XXXX, Quantity = XXXX, Price = XXXX, Amount = XXXX" 
    WHERE [Filter]"
    Hope this helps.

    Thanks

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun Feng Monday, June 29, 2009 1:42 AM
    Thursday, June 25, 2009 4:07 AM
  • Thanks YiChun.

    I will try to make this Update query as a parameterized query for i will have to update table with multiple records from LIstview. After i will let you know the results.

    Thanks again for your quick response.

    Thursday, June 25, 2009 4:36 AM
  • Hi Sazd1,

    It's my pleasure. :)

    Look forward to your good news.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 25, 2009 4:37 AM