Copying records

[Naveed_786]

Dear all,

I have problem i have table name "WO" in oracle db i want
"Whenever I start my program, I want it it look at all the works orders, and if any works order has a date that is exactly one month before today's date, then I want the program to display a message and create copies of those works orders with a schedule date of today's date and save it".

how to achieve this please provide me code

Thanks in advance.

Here is the structure of my database

DISCRIPTION VARCHAR2(200)
REQ_DATE DATE
WO_T VARCHAR2(30)
TASK_PER VARCHAR2(50)
ORIGINATOR VARCHAR2(70)
REQ_DEPT VARCHAR2(70)
EXTENSION NUMBER(10)
CRAFT VARCHAR2(50)
TASK_ASSG_TO VARCHAR2(70)
SCHEDULED_DATE DATE
SCHEDULED_FINISH_DATE DATE
COMPLETION_DATE DATE
ESTIMATED_HRS NUMBER(10)
REQ_TYPE VARCHAR2(50)
PRIORITY NUMBER(2)
ITEM_CODE NUMBER(30)
LOCATION VARCHAR2(70)
SUB_LOC VARCHAR2(70)
ADDED_DATE DATE
QTY NUMBER(30)
DONE_BY VARCHAR2(70)
EMP_ID NUMBER(20)
COMP_DU VARCHAR2(20)
COMMENTS VARCHAR2(2000)
PREP_BY VARCHAR2(50)
WO_USER VARCHAR2(30)
WO_NO NUMBER(25)
ITEM_NAME VARCHAR2(100)

Please also view the attached project file by following the given link

http://www.4shared.com/file/p6ikkUDw/ITPCHR1.html

[Naveed_786]

no body has any idea???

[Naveed_786]

Hello all,

Actually what i am doing is, i am trying to copy the records from my WO table and trying to change the scheduled_date like

if i have already entered a work oder and scheduled it on 26-SEO-2011 when the next month comes it should give me message do you want to crate work oders when i press yes it should copy the those records which has current date and change it month e.g in our example 26-SEP-2011 to 26-OCT-2011 and save it but i am unable to perform this task and similer procedure will be for other saved records when their scheduled date come it should copy it and change the month and save
This piece of code is on form load event

Private Sub QueryWorkorder()
	        Dim con As New OleDbConnection("Provider=MSDAORA.1;Data Source=(DESCRIPTION=" _
	    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=196.111.116.205)(PORT=1521)))" _
	    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Dummy)));" _
	    + "User Id=PCHR;Password=PCHR;")
	        Dim com As OleDbCommand = Nothing
	        Dim adapter As OleDbDataAdapter
	        Dim dt As New DataTable("WO")
	 
	        Try
	            Dim checkDate As DateTime = DateTime.Now.AddMonths(-1)
	            con.Open()
	            com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE SCHEDULED_DATE = '" & checkDate.ToString("dd-MM-yyyy") & "'", con)
	            If CInt(com.ExecuteScalar()) > 0 Then
	                con.Close()
	                If MessageBox.Show("Do you wish to generate workorder based on todays date?", "Workorder generation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
	                    com = New OleDbCommand("SELECT * FROM WO WHERE SCHEDULED_DATE = '" & checkDate.ToString("dd-MM-yyyy") & "'", con)
	                    adapter = New OleDbDataAdapter(com)
	                    adapter.Fill(dt)
	                    con.Close()
	 
	                    Dim frm As frmworkodr = New frmworkodr(dt)
	                    frm.Show()
	                End If
	            End If
	            con.Close()
	        Catch ex As Exception
	            If con.State = ConnectionState.Open Then
	                con.Close()
	            End If
	        End Try
	    End Sub

Private Sub Start_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	        QueryWorkorder()
	    End Sub

and this code is on work oder form

Private Sub GenerateWorkorder()
	        Dim dtDatasource As DataTable
	        dtDatasource = New DataTable("WO")
	         
	        If dtDataSource IsNot Nothing Then
	            For Each row As DataRow In dtDataSource.Rows
	                row("wo_no") = 0
	                row("SCHEDULED_DATE") = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").ToUpper
	            Next
	        End If
	    End Sub

and this the sub new class

Sub New(ByVal dt As DataTable)
	    InitializeComponent()
	    GenerateWorkorder()
	End Sub

and i am calling it on form load event

and i am changing the date from control panel and then running the project it didn't populate the message do you want to create record what should i do

[Naveed_786]

No body has any idea about this issue.

[adam_k]

I believe you need an insert statement and that would be all. No need to pull the records to the client and then insert them to the server. Have the server do the job for you. It will be quick, clean and it won't break if network connection is lost.

I don't know if Oracle uses the same syntax as SQL for insert, but you can try changing this

com = New OleDbCommand("SELECT * FROM WO WHERE SCHEDULED_DATE = '" & checkDate.ToString("dd-MM-yyyy") & "'", con)

to

com = New OleDbCommand("insert into WO 
(field1,field2,field3,SCHEDULED_DATE)
select field1,field2,field3, getdate() from WO WHERE SCHEDULED_DATE = '" & checkDate.ToString("dd-MM-yyyy") & "'", con)