Cor,
Hi. Many thanks for the code snippet. You have demonstrated how to work with
datarows which my code is lacking (I use a long winded way to achieve a
similar result...Fortran heritage is showing here).
Below is the actual code. What it does:
1. Displays a form showing a job list (this reads the JOBS table)
2. User selects a job and selects a button.
3. This buttons calls the jobconsol() function and uses the JOBNO to select
the records from OPERATIONS and JOBDETL tables
4. jobconsol() then attempts to manipulate the records by:
* Finds a duplicate records in OPERATIONS based on OPERNAME field
* If a duplicate is found it needs to move to the JOBDETL table and
modify the OPERUNIID field to point to the first OPERATIONS record
* After this the duplicates in OPERATIONS will be deleted...this is
not in the code yet.
The problem with the code is highligthed by the asterixs. This IF statement
is never true because the drCurrentJobdetl("operuniid") is always set to the
OPERATIONS.UNIID value. Very weird.
Imports System.Data
Imports FirebirdSql.Data.Firebird
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Shared myConnectionString As String 'The database connection
string
Public Shared myConnection As FbConnection 'A FB Connection to the
Database
Public Shared myTxn As FbTransaction 'A FB Transaction
Public Shared selectCmd As String 'The Select SQL
statement for displaying the list in the datagrid
Public Shared mycommand As FbCommand 'A SQL statement to
execute against a data source
Public Shared mycommand1 As FbCommand 'A SQL statement to
execute against a data source
Public Shared mycommand2 As FbCommand 'A SQL statement to
execute against a data source
Public Shared myReader As FbDataReader 'For reading a
forward-only stream of rows
Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MenuItem2.Click
End
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExit.Click
End
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
BindGrid()
End Sub
Sub Connect()
'Connection string
myConnectionString = "Database=c:\Program Files\Ezijobz
SME\Database\Demo\EJDB.FDB;User=SYSDBA;
Password=masterkey;Dialect=3;Server=localhost;Pooling=False"
myConnection = New FbConnection(myConnectionString)
myConnection.Open()
myTxn = myConnection.BeginTransaction()
End Sub
Sub BindGrid()
Dim dataAdapter As FbDataAdapter ' The FB Data Adapter
Dim DS As New DataSet ' The dataset
Connect()
dataAdapter = New FbDataAdapter
selectCmd = "SELECT jobs.jobno, jobs.jobname, jobs.jobstats,
jobs.custname, operations.squences, operations.opername, operations.descript,
operations.uniid " & _
"FROM jobs INNER JOIN operations " & _
"ON jobs.jobno = operations.jobno "
' "WHERE jobs.jobstats = ""QUOTED"" OR jobs.jobstats = ""ORDERED""
OR jobs.jobstats = ""RELEASED"""
' "ORDER BY jobs.jobno"
mycommand = New FbCommand(selectCmd, myConnection, myTxn)
Try
dataAdapter.SelectCommand = mycommand
dataAdapter.Fill(DS, "JOBS") 'filldataset
dgJobList.DataSource = DS.Tables("JOBS")
dgJobList.SetDataBinding(DS, "JOBS")
dgJobList.Refresh()
'DataGrid1.DataBind()
dataAdapter.Dispose()
myTxn.Dispose()
mycommand.Dispose()
myConnection.Close()
Catch Exp As FbException
myTxn.Dispose()
mycommand.Dispose()
myConnection.Close()
Exit Sub
End Try
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim msg As String
Dim title As String
Dim style As MsgBoxStyle
Dim response As MsgBoxResult
Dim selectedCell As System.Windows.Forms.DataGridCell
selectedCell = dgJobList.CurrentCell
selectedJobNo = dgJobList.Item(selectedCell.RowNumber,
selectedCell.ColumnNumber)
msg = "You are about to consolidate job number " & selectedJobNo & "
WARNING: Do you want to continue? There is no undo facility!!!"
style = MsgBoxStyle.DefaultButton2 Or MsgBoxStyle.Critical Or
MsgBoxStyle.YesNo
title = "Confirm the Job to Consolidate" ' Define title.
' Display message.
response = MsgBox(msg, style, title)
If response = MsgBoxResult.Yes Then ' User chose Yes.
JobConsol()
End
'Dim MyForm As New Form2
'MyForm.Visible = True ' NOT Form2.Visible = True ...
your object is MyForm
'MyForm.Show()
'MyForm.Label4.Text = CStr(selectedJobNo)
Else
' Perform some other action.
End If
End Sub
Function JobConsol()
MsgBox(selectedJobNo, MsgBoxStyle.DefaultButton2, "Starting the JOB
CONSOL function")
Dim ConsolCmd1 As String ' The SQL command for OPERATIONS
Dim ConsolCmd2 As String ' The SQL command for JOBDETL
Dim dataAdapter As FbDataAdapter ' The FB Data Adapter
Dim DS1 As New DataSet ' The dataset
Dim DS2 As New DataSet ' The dataset
Connect() ' Call the routine
dataAdapter = New FbDataAdapter 'Set the data adapter
'Load dataset with OPERATIONS records where @JOBNO
ConsolCmd1 = "SELECT jobno, squences, opername, uniid FROM
operations WHERE jobno = @JOBNO ORDER BY jobno"
mycommand1 = New FbCommand(ConsolCmd1, myConnection, myTxn) 'Set
the SQL
mycommand1.Parameters.Add("@JOBNO", FbDbType.Char)
mycommand1.Parameters("@JOBNO").Value = selectedJobNo
dataAdapter.SelectCommand = mycommand1 'Set the Data Adapter
dataAdapter.Fill(DS1, "Operations") 'Fill the Dataset with
OPERATIONS records
'Load dataset with JOBDETL records where @JOBNO
ConsolCmd2 = "SELECT jobno, operuniid, jobdetno, refid FROM jobdetl
WHERE jobno = @JOBNO ORDER BY jobno"
mycommand2 = New FbCommand(ConsolCmd2, myConnection, myTxn) 'Set
the SQL
mycommand2.Parameters.Add("@JOBNO", FbDbType.Char)
mycommand2.Parameters("@JOBNO").Value = selectedJobNo
dataAdapter.SelectCommand = mycommand2 'Set the Data Adapter
dataAdapter.Fill(DS2, "Jobdetl") 'Fill the Dataset with JOBDETL
records
'Define the data tables
Dim tblOperations As DataTable ' Define the OPERATIONS data table
tblOperations = DS1.Tables("Operations") ' Assign the data table
Dim tblJobdetl As DataTable ' Define the JOBDETL data table
tblJobdetl = DS2.Tables("Jobdetl") ' Assign the data table
'Declare and initialise the row counters for OPERATIONS
Dim intRowNoOperationsCurrent As Integer 'Declare and set the first
row to "0"
intRowNoOperationsCurrent = 0
Dim intRowNoOperationsNext As Integer 'Declare and set the second
row to "1"
intRowNoOperationsNext = 1
'Declare and initialise the row counters for JOBDETL
Dim intRowNoJobdetlCurrent As Integer 'Declare and set the first row
to "0"
intRowNoJobdetlCurrent = 0
'Declare and determine the number of records in the datasets
Dim intRecordNoOperations As Integer 'OPERATIONS
intRecordNoOperations = tblOperations.Rows.Count
Dim intRecordNoJobdetl As Integer ' JOBDETL
intRecordNoJobdetl = tblJobdetl.Rows.Count
' Declare and initialise a record counter for
Dim intRecordNoCountOperations As Integer ' OPERATIONS
intRecordNoCountOperations = 0
Dim intRecordNoCountJobdetl As Integer ' JOBDETL
intRecordNoCountJobdetl = 0
'Testing the decleration of datarows at this point
Dim drCurrentOperations As DataRow ' Current datarow for OPERATIONS
Dim drNextOperations As DataRow ' Next datarow for OPERATIONS
Dim drCurrentJobdetl As DataRow ' Current datarow for JOBDETL
' Loop for each CURRENT OPERATION record
Do While intRowNoOperationsNext <> intRecordNoOperations
' Loop for each NEXT OPERATION record
Do While intRowNoOperationsNext <> intRecordNoOperations
' Declare and initialise the OPERATIONS datarows
'Dim drCurrentOperations As DataRow ' Current datarow for
OPERATIONS
drCurrentOperations =
DS1.Tables("Operations").Rows(intRowNoOperationsCurrent)
'Dim drNextOperations As DataRow ' Next datarow for OPERATIONS
drNextOperations =
DS1.Tables("Operations").Rows(intRowNoOperationsNext)
' Test if first record OPERNAME is the same as second record
OPERNAME...if so start the consolidation process
If drCurrentOperations("opername") =
drNextOperations("opername") Then
' Insert the text "DELETE OPERATION" in OPERNAME of
second record
drNextOperations("opername") = "DELETE OPERATION"
Do While intRecordNoCountJobdetl <> intRecordNoJobdetl
' Declare and initialise the JOBDETL datarows
'Dim drCurrentJobdetl As DataRow ' Current datarow
for JOBDETL
drCurrentJobdetl =
DS2.Tables("Jobdetl").Rows(intRowNoJobdetlCurrent)
'Test
Dim dblOperuniid As Double
dblOperuniid = drCurrentJobdetl("operuniid")
' Test id records in JOBDETL match flUniid...if so
set to first record UNIID
* If drCurrentJobdetl("operuniid") =
drNextOperations("uniid") Then
* ' Assign to OPERUNIID the value of UNIID in the
current record of the OPERATIONS dataset
* drCurrentJobdetl("operuniid") =
drCurrentOperations("uniid")
* End If
' Increment the JOBDETL record counter
intRecordNoCountJobdetl = intRecordNoCountJobdetl + 1
' Reassign the JOBDETL datarow
drCurrentJobdetl =
DS2.Tables("Jobdetl").Rows(intRowNoJobdetlCurrent)
Loop
' Insert ... UPDATE operations set opername = "DELETE
OPERATION" where uniid = @uniid
End If
intRowNoOperationsNext = intRowNoOperationsNext + 1 '
Increment the OPERATIONS NEXT record
Loop
intRowNoOperationsCurrent = intRowNoOperationsCurrent + 1 '
Increment the OPERATIONS CURRENR record
intRowNoOperationsNext = intRowNoOperationsCurrent + 1 '
Reset the OPERATIONS NEXT record to 1 + Current
' ??? intRecordNoCountOperations = intRecordNoCountOperations +
1 ' Increment the OPERATIONS record counter
Loop
'The UPDATE query...still to complete
'Dim UpdateCmd As String
'UpdateCmd = "UPDATE Operations SET Operations.squences = @squences
" & _
' "WHERE Operations.uniid = @uniid"
'mycommand = New FbCommand(UpdateCmd, myConnection, myTxn)
'The parameters for the UPDATE query
'mycommand.Parameters.Add("@squences", FbDbType.Integer)
'mycommand.Parameters("@squences").Value = drNext("squences")
'mycommand.Parameters.Add("@uniid", FbDbType.Double)
'mycommand.Parameters("@uniid").Value = fldUniid
'mycommand.ExecuteNonQuery()
myTxn.Commit()
myTxn.Dispose()
mycommand.Dispose()
myConnection.Close()
BindGrid()
MsgBox(selectedJobNo, MsgBoxStyle.DefaultButton2, "Finished the JOB
CONSOL function")
End
End Function
End Class
