Copy records, change date, save new...
-
I want to give my user the ability to copy a list of Orders (based on MR # and PreviousVisitDate) to a new VisitDate when they are creating one. So this is what I have to do.. 1. Select all Orders with the specified MR and PreviousVisitDate 2. Copy all of the returned records (to a new temp table??) 3. Change the Date in the returned records to the New Date they entered. So now I will have the original list and the new list that looks just like it but with a different Date. I am sure this is simple for most of you but I am struggling with it. Can anyone show me an example of how this would be done or point me to one that I can read through? Thanks for any help you can give me with this.
-
I want to give my user the ability to copy a list of Orders (based on MR # and PreviousVisitDate) to a new VisitDate when they are creating one. So this is what I have to do.. 1. Select all Orders with the specified MR and PreviousVisitDate 2. Copy all of the returned records (to a new temp table??) 3. Change the Date in the returned records to the New Date they entered. So now I will have the original list and the new list that looks just like it but with a different Date. I am sure this is simple for most of you but I am struggling with it. Can anyone show me an example of how this would be done or point me to one that I can read through? Thanks for any help you can give me with this.
How this is done depends on your existing implementation. How are the records being selected? In what control are they displayed? Is this control bound to a DataSet? If the displaying control is bound to a dataset, then the items returned by its SelectedItems property should be DataRow objects. All you would have to do is change the date in each DataRow, then possibly write the in that DataSet back to the database. If not bound, then you would have to get the record ID numbers from the SelectedItems of your control and make the changes to the database using Sql statements, or whatever else you're storing this data in. I can't give you a more consise answer, because we know nothing of your existing implementation.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
How this is done depends on your existing implementation. How are the records being selected? In what control are they displayed? Is this control bound to a DataSet? If the displaying control is bound to a dataset, then the items returned by its SelectedItems property should be DataRow objects. All you would have to do is change the date in each DataRow, then possibly write the in that DataSet back to the database. If not bound, then you would have to get the record ID numbers from the SelectedItems of your control and make the changes to the database using Sql statements, or whatever else you're storing this data in. I can't give you a more consise answer, because we know nothing of your existing implementation.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Hello Dave, thanks for your reply. When the user goes to enter a new VisitDate (text box) I have a checkbox below it for Copy Orders from existing Visit Date, when they check it They see a combo box with a list of previous Dates for this MR #. Once they select a previous Date and hit OK I need to run this behind the scenes. I need to find all of the orders from the selected Previous VisitDate and copy all of them and then change the date on those new records to the NewVisitDate that they entered in the text box. I know that the New Date and the Previous date are both coming over fine right now. I just need to start putting the SQL into place now. And that is where I got stuck. The form I am working with is connected to my Access 2003 database via a DataSet. If you need more information then just let me know. -- modified at 16:12 Wednesday 23rd May, 2007
-
Hello Dave, thanks for your reply. When the user goes to enter a new VisitDate (text box) I have a checkbox below it for Copy Orders from existing Visit Date, when they check it They see a combo box with a list of previous Dates for this MR #. Once they select a previous Date and hit OK I need to run this behind the scenes. I need to find all of the orders from the selected Previous VisitDate and copy all of them and then change the date on those new records to the NewVisitDate that they entered in the text box. I know that the New Date and the Previous date are both coming over fine right now. I just need to start putting the SQL into place now. And that is where I got stuck. The form I am working with is connected to my Access 2003 database via a DataSet. If you need more information then just let me know. -- modified at 16:12 Wednesday 23rd May, 2007
OK. I still know nothing of your implementation in code, so I'll just describe the generic versions. Retrieve the records for that MR and previous date putting them into a DataTable using a DataAdapter. Change the date field in each row in the table, then call Update on the DataAdpater to write the changes back to the database. Refresh your bound controls and you should be all set. Or, write an SQL UPDATE statement that selects all of the records that make the MR/PreviousDate restrictions and changes only those records to the new date. This would use an OldDbCommand and its ExecuteNonQuery method to launch the command. Again, refresh the bound controls.
UPDATE mytable SET mydatefield = ?newdate WHERE mydatefield = ?somedate AND MKID = ?selectedMKID
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
OK. I still know nothing of your implementation in code, so I'll just describe the generic versions. Retrieve the records for that MR and previous date putting them into a DataTable using a DataAdapter. Change the date field in each row in the table, then call Update on the DataAdpater to write the changes back to the database. Refresh your bound controls and you should be all set. Or, write an SQL UPDATE statement that selects all of the records that make the MR/PreviousDate restrictions and changes only those records to the new date. This would use an OldDbCommand and its ExecuteNonQuery method to launch the command. Again, refresh the bound controls.
UPDATE mytable SET mydatefield = ?newdate WHERE mydatefield = ?somedate AND MKID = ?selectedMKID
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Ok this is what I have so far I am trying to create a temp table and pull data into it.... Dim NewVisitDate As Date Dim PriorVisitDate As Date Dim SQL As String = String.Empty Dim DT As New DataTable("TempVisitTable") 'set Variables NewVisitDate = txtVisitDate.Text PriorVisitDate = Me.cbobxPriorDates.Text.ToString MR = frmQuestionnaire.txtMRNo.Text ' SQL = "SELECT OrderMR, OrderDate, [Order] FROM (Orders) WHERE (OrderMR='" & MR & "') and (OrderDate= #" & PriorVisitDate & "#)" Try Connection.Open() Dim Command As New OleDbCommand(SQL, Connection) Command.ExecuteReader() DT.Load(SQL) Catch ex As Exception End Try When I do this I get a message on the DT.Load(SQL) that says...Unable to cast object of type 'System.String' to type 'System.Data.IDataReader'. I am sure this is a syntax problem. Can someone tell me what I am doing wrong here?
-
Ok this is what I have so far I am trying to create a temp table and pull data into it.... Dim NewVisitDate As Date Dim PriorVisitDate As Date Dim SQL As String = String.Empty Dim DT As New DataTable("TempVisitTable") 'set Variables NewVisitDate = txtVisitDate.Text PriorVisitDate = Me.cbobxPriorDates.Text.ToString MR = frmQuestionnaire.txtMRNo.Text ' SQL = "SELECT OrderMR, OrderDate, [Order] FROM (Orders) WHERE (OrderMR='" & MR & "') and (OrderDate= #" & PriorVisitDate & "#)" Try Connection.Open() Dim Command As New OleDbCommand(SQL, Connection) Command.ExecuteReader() DT.Load(SQL) Catch ex As Exception End Try When I do this I get a message on the DT.Load(SQL) that says...Unable to cast object of type 'System.String' to type 'System.Data.IDataReader'. I am sure this is a syntax problem. Can someone tell me what I am doing wrong here?
CCG3 wrote:
DT.Load(SQL)
The Load method is looking for a DataReader as a parameter and you gave it a string. The ExecuteReader method returns a DataReader object, which you just ignored and dropped. This code should read:
Connection.Open()
Dim Command As New OleDbCommand(SQL, Connection)
Dim dr As DataReader
dr = Command.ExecuteReader()
DT.Load(dr)But, like I said before, another alternative would be to use an SQL UPDATE statement, where you would need to load this stuff into a temp table.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007