Datarows storing incorrect data

[Brad]

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

[Cor Ligthert]

Re: Datarows storing incorrect data

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" <Brad@discussio ns.microsoft.co m> schreef in bericht
news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=blue]
>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[/color]

[Cor Ligthert]

Re: Datarows storing incorrect data

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" <Brad@discussio ns.microsoft.co m> schreef in bericht
news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=blue]
>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[/color]

[Cor Ligthert]

Re: Datarows storing incorrect data

to show some code

[Cor Ligthert]

Re: Datarows storing incorrect data

to show some code

[Brad]

Re: Datarows storing incorrect data

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:
[color=blue]
> 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" <Brad@discussio ns.microsoft.co m> schreef in bericht
> news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=green]
> >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[/color]
>
>
>[/color]

[Brad]

Re: Datarows storing incorrect data

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:
[color=blue]
> 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" <Brad@discussio ns.microsoft.co m> schreef in bericht
> news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=green]
> >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[/color]
>
>
>[/color]

[Cor Ligthert]

Re: Datarows storing incorrect data

Brad,

As you said, you use in my opinion as well to much code and tries in my
opinion a classic way of coding to use with VBNet, what will in my opinion
not give the result you want to get and make it everytime more difficult.

Did you ever use the resource kit.

There are samples from master detail grids and a lot more. When I was you I
would have a look to it. It is mostly good code while on MSDN old not
complete translated VB6 or bad translated C# what you have to be able to
know before you see it.

VB.net Resource kit

http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing the resource kit

http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir

I myself have at this moment the problem that the samples do not open after
that I updated my system. I do not know it that is with everybody.

You can than open the samples by the way, by copying the shortcut and to
open that using copy and paste.

When you have that open problem as well, will you than tell me that?

Cor

"Brad" <Brad@discussio ns.microsoft.co m>
[color=blue]
> 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:
>[color=green]
>> 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" <Brad@discussio ns.microsoft.co m> schreef in bericht
>> news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=darkred]
>> >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[/color]
>>
>>
>>[/color][/color]

[Cor Ligthert]

Re: Datarows storing incorrect data

Brad,

As you said, you use in my opinion as well to much code and tries in my
opinion a classic way of coding to use with VBNet, what will in my opinion
not give the result you want to get and make it everytime more difficult.

Did you ever use the resource kit.

There are samples from master detail grids and a lot more. When I was you I
would have a look to it. It is mostly good code while on MSDN old not
complete translated VB6 or bad translated C# what you have to be able to
know before you see it.

VB.net Resource kit

http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing the resource kit

http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir

I myself have at this moment the problem that the samples do not open after
that I updated my system. I do not know it that is with everybody.

You can than open the samples by the way, by copying the shortcut and to
open that using copy and paste.

When you have that open problem as well, will you than tell me that?

Cor

"Brad" <Brad@discussio ns.microsoft.co m>
[color=blue]
> 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:
>[color=green]
>> 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" <Brad@discussio ns.microsoft.co m> schreef in bericht
>> news:00E8F26D-BEB9-4688-BC7E-313922B77E9B@mi crosoft.com...[color=darkred]
>> >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[/color]
>>
>>
>>[/color][/color]

[Brad]

Re: Datarows storing incorrect data

Cor,
Hi. Thanks for the info. They are good references. I cannot make the VB.NEt
resrouce kit work as I have not enable the IIS.

I have rewritten the code and it now works except that I am struggling to do
the UPDATE.
Thanks
Brad