[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

[_HAWK_]

You are passing the dt to the constructor, but not using it in the GereateWorkOrder. In fact you create an empty datatable

Sub New(ByVal dt As DataTable)
        InitializeComponent()
        GenerateWorkorder(dt) '<- pass it to your sub
End Sub

Private Sub GenerateWorkorder(dtDataSource As DataTable)
             
        If dtDataSource IsNot Nothing Then
           'now you have access to your datatable you already filled
        End If
End Sub

[naveed_786]

It produces error on this line

GeneratrkordeeWor(dt)


Too many arguments to 'Private sub GeneratrkordeeWor()'

What could be the problem

[smohd]

You have told, you need to change the definition of your sub also:

Private Sub GenerateWorkorder(dtDataSource As DataTable) '<---Changes here allow it to pass parameters
        'Dim dtDatasource As DataTable <---So this line will be removed
    End Sub

This post has been edited by smohd: 11 October 2011 - 10:56 AM

[naveed_786]

Now it is producing error dtDatsouce is not declared

[smohd]

Can we see your updated code please?

[naveed_786]

Okay i have resoled the problem with SQL SERVER but after formatting code for Oracle Database it does not work here is the code please help me how to get it work for Oracle database

Private Sub GenerateWorkorder(ByVal source As DataTable)
        Try
            'Dim dtDatasource As DataTable = source.Clone
            Dim newWorkOrderNumber As Integer = Auto_no()

            If source IsNot Nothing Then
                For i As Integer = 0 To source.Rows.Count - 1
                    Dim row As DataRow = source.NewRow
                    row.ItemArray = source.Rows(i).ItemArray

                    row("wo_no") = newWorkOrderNumber
                    row("SCHEDULED_DATE") = DateTime.Now.ToString("dd-MMM-yyyy").ToUpper
                    newWorkOrderNumber += 1

                    source.Rows.Add(row)
                Next

                Dim con As New OleDbConnection(ConnectionString)
                Dim da As New OleDbDataAdapter("SELECT * FROM WO", con)
                Dim cb As New OleDbCommandBuilder(da)
                da.InsertCommand = cb.GetInsertCommand()
                da.UpdateCommand = cb.GetUpdateCommand

                da.Update(source)
            End If
            MessageBox.Show("GENERATION OF NEW WORKORDERS FINISHED SUCCESSFULLY", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            MessageBox.Show("FAILED TO STORE NEW WORKORDERS", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        End Try
    End Sub

and

Private Sub GenerateWorkOrderQuery()
        Try
            Dim checkDate As DateTime = DateTime.Now.AddMonths(-1)
            Dim con As New OleDbConnection(ConnectionString)
            Dim com As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim source As DataTable = Nothing

            con.Open()
            com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE SCHEDULED_DATE = to_date('" & checkDate.ToString("dd-MM-yyyy").ToUpper & "')", 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 = to_date('" & checkDate.ToString("dd-MM-yyyy") & "')", con)
                    com = New OleDbCommand("SELECT * FROM WO WHERE TO_CHAR(SCHEDULED_DATE, 'MM/DD/YYYY') = '" & checkDate.ToString("MM/dd/yyyy").ToUpper & "'", con)
                    da = New OleDbDataAdapter(com)
                    source = New DataTable("WO")
                    da.Fill(source)
                    con.Close()

                    GenerateWorkorder(source)
                End If
            End If
            con.Close()
        Catch ex As Exception
        End Try
    End Sub

i am calling it on form load event but nothing happens but when i was using SQL Server it was giving me message when i click on Yes it create the records but now with oracle database it is not giving me message and not doing anything.

Please Please help me.

[naveed_786]

NO body has any idea????

[smohd]

Put a message box in catch block(Never leave catch block empty), and see what happens, probably there is an error somewhere and trace the error message. Also put an else condition if scalar is zero or less and see what is happening.
Then post any error message you get or any result came out...

[naveed_786]

As you guide me i have tried but no error message and no result. situation is same.

[smohd]

Can I see your updated code? you cant say nothing happens because at least if the code doesnt enter in this condition If CInt(com.ExecuteScalar()) > 0 Then it will enter in its else condition.

[naveed_786]

Here is the updated Code

    Private Sub GenerateWorkOrderQuery()
        Try
            Dim checkDate As DateTime = DateTime.Now.AddMonths(-1)
            Dim con As New OleDbConnection(ConnectionString)
            Dim com As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim source As DataTable = Nothing

            con.Open()
            com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & 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 TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & checkDate.ToString("DD/MM/YYYY") & "'", con)
                    da = New OleDbDataAdapter(com)
                    source = New DataTable("WO")
                    da.Fill(source)
                    con.Close()

                    GenerateWorkorder(source)

                ElseIf CInt(com.ExecuteScalar()) <= 0 Then

                    MessageBox.Show("No Record Exists!", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)

                End If
            End If
            con.Close()
        Catch ex As Exception
            MessageBox.Show("Some Problem Exists", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        End Try
    End Sub

[smohd]

You misplace your else statement it should be else for the outer if and not inner if, so:

Private Sub GenerateWorkOrderQuery()
    Try
        Dim checkDate As DateTime = DateTime.Now.AddMonths(-1)
        Dim con As New OleDbConnection(ConnectionString)
        Dim com As OleDbCommand = Nothing
        Dim da As OleDbDataAdapter = Nothing
        Dim source As DataTable = Nothing

        con.Open()
        com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & 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 TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & checkDate.ToString("DD/MM/YYYY") & "'", con)
                da = New OleDbDataAdapter(com)
                source = New DataTable("WO")
                da.Fill(source)
                con.Close()

                GenerateWorkorder(source)
            End If 'follow this first
       Else

                MessageBox.Show("No Record Exists!", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)

            
        End If
        con.Close()
    Catch ex As Exception
        MessageBox.Show("Some Problem Exists", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
    End Try
End Sub

[naveed_786]

Now i have modified the code but still no error message

Private Sub GenerateWorkOrderQuery()
        Try
            Dim checkDate As DateTime = DateTime.Now.AddMonths(-1)
            Dim con As New OleDbConnection(ConnectionString)
            Dim com As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim source As DataTable = Nothing

            con.Open()
            com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & 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 TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & checkDate.ToString("DD/MM/YYYY") & "'", con)
                    da = New OleDbDataAdapter(com)
                    source = New DataTable("WO")
                    da.Fill(source)
                    con.Close()
                    GenerateWorkorder(source)
                End If
            Else
                MessageBox.Show("No Record Exists!", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
            End If
            con.Close()
        Catch ex As Exception
            MessageBox.Show("Some Problem Exists", "AN ERROR OCCURED", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        End Try
    End Sub

NOTE: My database is Oracle 9i

This post has been edited by naveed_786: 16 October 2011 - 05:55 AM

[smohd]

If the execution does not enter in if neither in else, means this method wan not called anywhere!!!
Which code block is calling GenerateWorkOrderQuery() subroutine?