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...Fortra n 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 drCurrentJobdet l("operuniid" ) is always set to the
OPERATIONS.UNII D value. Very weird.
Imports System.Data
Imports FirebirdSql.Dat a.Firebird
Public Class Form1
Inherits System.Windows. Forms.Form
#Region " Windows Form Designer generated code "
Public Shared myConnectionStr ing 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.EventArg s) Handles MenuItem2.Click
End
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnExit.Click
End
End Sub
Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
BindGrid()
End Sub
Sub Connect()
'Connection string
myConnectionStr ing = "Database=c:\Pr ogram Files\Ezijobz
SME\Database\De mo\EJDB.FDB;Use r=SYSDBA;
Password=master key;Dialect=3;S erver=localhost ;Pooling=False"
myConnection = New FbConnection(my ConnectionStrin g)
myConnection.Op en()
myTxn = myConnection.Be ginTransaction( )
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.sque nces, operations.oper name, operations.desc ript,
operations.unii d " & _
"FROM jobs INNER JOIN operations " & _
"ON jobs.jobno = operations.jobn o "
' "WHERE jobs.jobstats = ""QUOTED"" OR jobs.jobstats = ""ORDERED""
OR jobs.jobstats = ""RELEASED" ""
' "ORDER BY jobs.jobno"
mycommand = New FbCommand(selec tCmd, myConnection, myTxn)
Try
dataAdapter.Sel ectCommand = mycommand
dataAdapter.Fil l(DS, "JOBS") 'filldataset
dgJobList.DataS ource = DS.Tables("JOBS ")
dgJobList.SetDa taBinding(DS, "JOBS")
dgJobList.Refre sh()
'DataGrid1.Data Bind()
dataAdapter.Dis pose()
myTxn.Dispose()
mycommand.Dispo se()
myConnection.Cl ose()
Catch Exp As FbException
myTxn.Dispose()
mycommand.Dispo se()
myConnection.Cl ose()
Exit Sub
End Try
End Sub
Private Sub Button1_Click_1 (ByVal sender As System.Object, ByVal e As
System.EventArg s) 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.DataGridC ell
selectedCell = dgJobList.Curre ntCell
selectedJobNo = dgJobList.Item( selectedCell.Ro wNumber,
selectedCell.Co lumnNumber)
msg = "You are about to consolidate job number " & selectedJobNo & "
WARNING: Do you want to continue? There is no undo facility!!!"
style = MsgBoxStyle.Def aultButton2 Or MsgBoxStyle.Cri tical Or
MsgBoxStyle.Yes No
title = "Confirm the Job to Consolidate" ' Define title.
' Display message.
response = MsgBox(msg, style, title)
If response = MsgBoxResult.Ye s 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(selectedJo bNo)
Else
' Perform some other action.
End If
End Sub
Function JobConsol()
MsgBox(selected JobNo, MsgBoxStyle.Def aultButton2, "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(Conso lCmd1, myConnection, myTxn) 'Set
the SQL
mycommand1.Para meters.Add("@JO BNO", FbDbType.Char)
mycommand1.Para meters("@JOBNO" ).Value = selectedJobNo
dataAdapter.Sel ectCommand = mycommand1 'Set the Data Adapter
dataAdapter.Fil l(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(Conso lCmd2, myConnection, myTxn) 'Set
the SQL
mycommand2.Para meters.Add("@JO BNO", FbDbType.Char)
mycommand2.Para meters("@JOBNO" ).Value = selectedJobNo
dataAdapter.Sel ectCommand = mycommand2 'Set the Data Adapter
dataAdapter.Fil l(DS2, "Jobdetl") 'Fill the Dataset with JOBDETL
records
'Define the data tables
Dim tblOperations As DataTable ' Define the OPERATIONS data table
tblOperations = DS1.Tables("Ope rations") ' Assign the data table
Dim tblJobdetl As DataTable ' Define the JOBDETL data table
tblJobdetl = DS2.Tables("Job detl") ' Assign the data table
'Declare and initialise the row counters for OPERATIONS
Dim intRowNoOperati onsCurrent As Integer 'Declare and set the first
row to "0"
intRowNoOperati onsCurrent = 0
Dim intRowNoOperati onsNext As Integer 'Declare and set the second
row to "1"
intRowNoOperati onsNext = 1
'Declare and initialise the row counters for JOBDETL
Dim intRowNoJobdetl Current As Integer 'Declare and set the first row
to "0"
intRowNoJobdetl Current = 0
'Declare and determine the number of records in the datasets
Dim intRecordNoOper ations As Integer 'OPERATIONS
intRecordNoOper ations = tblOperations.R ows.Count
Dim intRecordNoJobd etl As Integer ' JOBDETL
intRecordNoJobd etl = tblJobdetl.Rows .Count
' Declare and initialise a record counter for
Dim intRecordNoCoun tOperations As Integer ' OPERATIONS
intRecordNoCoun tOperations = 0
Dim intRecordNoCoun tJobdetl As Integer ' JOBDETL
intRecordNoCoun tJobdetl = 0
'Testing the decleration of datarows at this point
Dim drCurrentOperat ions As DataRow ' Current datarow for OPERATIONS
Dim drNextOperation s As DataRow ' Next datarow for OPERATIONS
Dim drCurrentJobdet l As DataRow ' Current datarow for JOBDETL
' Loop for each CURRENT OPERATION record
Do While intRowNoOperati onsNext <> intRecordNoOper ations
' Loop for each NEXT OPERATION record
Do While intRowNoOperati onsNext <> intRecordNoOper ations
' Declare and initialise the OPERATIONS datarows
'Dim drCurrentOperat ions As DataRow ' Current datarow for
OPERATIONS
drCurrentOperat ions =
DS1.Tables("Ope rations").Rows( intRowNoOperati onsCurrent)
'Dim drNextOperation s As DataRow ' Next datarow for OPERATIONS
drNextOperation s =
DS1.Tables("Ope rations").Rows( intRowNoOperati onsNext)
' Test if first record OPERNAME is the same as second record
OPERNAME...if so start the consolidation process
If drCurrentOperat ions("opername" ) =
drNextOperation s("opername") Then
' Insert the text "DELETE OPERATION" in OPERNAME of
second record
drNextOperation s("opername") = "DELETE OPERATION"
Do While intRecordNoCoun tJobdetl <> intRecordNoJobd etl
' Declare and initialise the JOBDETL datarows
'Dim drCurrentJobdet l As DataRow ' Current datarow
for JOBDETL
drCurrentJobdet l =
DS2.Tables("Job detl").Rows(int RowNoJobdetlCur rent)
'Test
Dim dblOperuniid As Double
dblOperuniid = drCurrentJobdet l("operuniid" )
' Test id records in JOBDETL match flUniid...if so
set to first record UNIID
* If drCurrentJobdet l("operuniid" ) =
drNextOperation s("uniid") Then
* ' Assign to OPERUNIID the value of UNIID in the
current record of the OPERATIONS dataset
* drCurrentJobdet l("operuniid" ) =
drCurrentOperat ions("uniid")
* End If
' Increment the JOBDETL record counter
intRecordNoCoun tJobdetl = intRecordNoCoun tJobdetl + 1
' Reassign the JOBDETL datarow
drCurrentJobdet l =
DS2.Tables("Job detl").Rows(int RowNoJobdetlCur rent)
Loop
' Insert ... UPDATE operations set opername = "DELETE
OPERATION" where uniid = @uniid
End If
intRowNoOperati onsNext = intRowNoOperati onsNext + 1 '
Increment the OPERATIONS NEXT record
Loop
intRowNoOperati onsCurrent = intRowNoOperati onsCurrent + 1 '
Increment the OPERATIONS CURRENR record
intRowNoOperati onsNext = intRowNoOperati onsCurrent + 1 '
Reset the OPERATIONS NEXT record to 1 + Current
' ??? intRecordNoCoun tOperations = intRecordNoCoun tOperations +
1 ' Increment the OPERATIONS record counter
Loop
'The UPDATE query...still to complete
'Dim UpdateCmd As String
'UpdateCmd = "UPDATE Operations SET Operations.sque nces = @squences
" & _
' "WHERE Operations.unii d = @uniid"
'mycommand = New FbCommand(Updat eCmd, myConnection, myTxn)
'The parameters for the UPDATE query
'mycommand.Para meters.Add("@sq uences", FbDbType.Intege r)
'mycommand.Para meters("@squenc es").Value = drNext("squence s")
'mycommand.Para meters.Add("@un iid", FbDbType.Double )
'mycommand.Para meters("@uniid" ).Value = fldUniid
'mycommand.Exec uteNonQuery()
myTxn.Commit()
myTxn.Dispose()
mycommand.Dispo se()
myConnection.Cl ose()
BindGrid()
MsgBox(selected JobNo, MsgBoxStyle.Def aultButton2, "Finished the JOB
CONSOL function")
End
End Function
End Class
"Cor Ligthert" wrote:
Brad,
I think you need some code, when I write in what you tells here in code I
get something as
\\\
If ds.tables("mast er").rows(0)( 0) = ds.tables("mast er").rows(1)( 0) then
dim lr as integer = 0
do until lr = ds.tables("deta ils).rows.count - 1
if ds.tables("mast er").rows(1)( 1) =
ds.tables("deta ils").rows(lr)( 1) then
ds.tables("deta ils").rows(lr). delete
else
lr +=1
end if
loop
end if
///
So it can not be that difficult to show your code.
Cor
"Brad" <Br**@discussio ns.microsoft.co m> schreef in bericht
news:00******** *************** ***********@mic rosoft.com...I have written some code to manipulate data/records in a MASTER (order
header) and DETAIL (order details) tables.
What I have written is too extensive to post but essentially trying to:
1. Assign to a datarow (dr1) the first record of the MASTER table
2. Assign to another datarow (dr2) the second record of the MASTER table
3. If dr1.field1 = dr2.field1, then proceed, otherwise do stop
4. Assign to a third datarow (dr3) the first record of the DETAIL table
5. If dr2.field2 = dr3.field2, then delete record, otherwise repeat this
step till last record incrementing the datarow (dr3) for the DETAIL table
.
What is happening is that dr3.field2 contains the value of dr1.field2!
Some
how the values in the first datarow are ending up in the third datarow.
How could this be possible?
I have not set any relations between the tables as I do not believe it is
necessary
-
Brad
Sydney, Australia
