Select next item in datareader, then insert a new record and update the rest

[robecflo]

Hi Forum, i have a problem, hope somebody can give me ideas. I'm
developing with windows forms and vb.net, and oracle as a database. At
this moment i have a table called amortizaciones, this table has a
field called id_pasivo, which is foreign key to another table called
pasivo, a consecutive field called no_cupon and a third field called
date. So when i make a query (i.e select * from amortizaciones where
id_pasivo = 522 order by no_cupon), it returs something like this:

id_pasivo no_cupon date
----------------------------------------------
522 1 03/12/2004
522 2 03/01/2005
522 3 03/02/2005
522 4 06/03/2005
522 5 05/04/2005
522 6 06/05/2005
522 7 07/06/2005
522 8 08/07/2005
522 9 08/08/2005
522 10 08/09/2005
522 11 09/10/2005
522 12 09/11/2005
522 13 30/11/2005


but when i open a form, insert a value, i have push a button and then i
have to read this query and compare the date, for example i capture a
value on 11/11/2005, it means that the date is between the 9/11/2005
and 30/11/2005, or between the no_cupon value 12 and 13. Well
firstable, how can i compare the next value in the while
datareader.read?? i want to compare the field date of the current
record and the same field of the next record against the date entered
by user (11/11/2005), so if thr date entered is between then insert a
new record that replaces the
current and the next record or records needs to update and increment
the value of the field no_cupon. So it will be like:

id_pasivo no_cupon date
----------------------------------------------
522 1 03/12/2004
522 2 03/01/2005
522 3 03/02/2005
522 4 06/03/2005
522 5 05/04/2005
522 6 06/05/2005
522 7 07/06/2005
522 8 08/07/2005
522 9 08/08/2005
522 10 08/09/2005
522 11 09/10/2005
522 12 09/11/2005
522 13 11/11/2005
522 14 30/11/2005

The date entered was inserted after the next lower date and before the
next bigger date, with the consecutive no_cupon, and the rest of them
was incremented on this field, thats what i need to do.

Somebody can help me? i was trying to do with an arraylist, a
datatable, but not successfull

Thanks in advanced

 

[Guest]

You may want to copy your data from your data source into an ADO.NET data
table. Then you can make your changes in that table and then run the Update
method of your Data Adapter to update you source table.

Hope this helps

Rob

 

[robecflo]

Thanks for your help Rob, but only have the next problem. How i do that
:S. Do you have any example or documentation. I copy here the code of
how im doing:

Dim strSQL As String = "SELECT * FROM amortizaciones " & _
"WHERE id_pasivo = " & Me.idPasivo &
" ORDER BY no_cupon"
datareader = m_database.GetReader(strSQL)


Dim TablaOrigen As New DataTable("Amortizacion")
Dim TablaDestino As New DataTable("Amortizacion")
TablaOrigen.Columns.Add(New DataColumn("id_pasivo",
System.Type.GetType("System.Int32")))
TablaOrigen.Columns.Add(New DataColumn("no_cupon",
System.Type.GetType("System.Int32")))
TablaOrigen.Columns.Add(New DataColumn("fecha",
System.Type.GetType("System.DateTime")))

TablaDestino = TablaOrigen.Clone

While datareader.Read()
Dim Linea As DataRow = TablaOrigen.NewRow()
Linea("id_pasivo") = datareader("id_pasivo")
Linea("no_cupon") = datareader("no_cupon")
Linea("fecha") = datareader("fecha")
TablaOrigen.Rows.Add(Linea)
End While
datareader = Nothing

****HERE IS WHERE I'M LOST, I DON'T HAVE EXPERIENCE WITH DATATABLES AND
THEN RETURN TO DATAADAPTER****

For elem = 0 To TablaOrigen.Rows.Count - 1
If prepago.Fecha.Date >=
TablaOrigen.Rows(elem)("fecha") _
And prepago.Fecha.Date <= TablaOrigen.Rows(elem +
1)("fecha") _
And Not bolInsertaPrepago Then
Dim row As DataRow = TablaDestino.NewRow()
row("id_pasivo") =
TablaOrigen.Rows(elem)("id_pasivo")
row("no_cupon") =
TablaOrigen.Rows(elem)("no_cupon")
row("fecha") = prepago.Fecha.Date
TablaDestino.Rows.Add(row)
bolInsertaPrepago = True
'TablaOrigen.Rows.InsertAt(row, elem)
'TablaOrigen.AcceptChanges()
Else
If IsDBNull(TablaOrigen.Rows(elem + 1)) Then
Exit For
End If
Dim row As DataRow = TablaDestino.NewRow()
row("id_pasivo") =
TablaOrigen.Rows(elem)("id_pasivo")
row("no_cupon") =
TablaOrigen.Rows(elem)("no_cupon")
row("fecha") = TablaOrigen.Rows(elem)("fecha")
TablaDestino.Rows.Add(row)
End If
Next
Me.Close()

THANKS

 

[Cor Ligthert [MVP]]

Hi,

I assume that Rob means to use a dataset/datatable for that.

There are so many samples on msdn.microsoft.com for that, that I cannot give
you even one, have a look yourself there.

I hope this helps,

Cor

 

[Guest]

Yes that is what I meant. Cor is correct in that there are many examples.
You may wish to start by looking at the ReadXML or WriteXML methods of the
dataset object.

Once you "read" the XML into the ADO.NET dataset, then you can change each
row in that table to whatever you want. Then simply "save" it back to your
XML file using the WriteXML method of the dataset.

Rob

 

[robecflo]

Thanks a lot, i will to check msdn to learn more about dataset and
datatable. I don't undertand it at all.

I solve my problem just 30 minutes ago, i was playijng with queries,
like max(date) and min(date), then a datatble and thats was my
solution.

Thanks for your help!!