473,233 Members | 2,836 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

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

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

Nov 23 '05 #1
5 4040
Rob
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

"ro******@gmail.com" wrote:
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

Nov 23 '05 #2
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

Rob wrote:
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

"ro******@gmail.com" wrote:
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


Nov 23 '05 #3
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
Nov 23 '05 #4
Rob
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

"Cor Ligthert [MVP]" wrote:
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

Nov 23 '05 #5
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!!

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: lea | last post by:
Does anyone know how to write the SELECT * FROM tblename WHERE fldname BETWEEN combobox1.text AND combobox2.text i can't find much source about this query on net,i need to let user select...
0
by: jtocci | last post by:
I'm having a big problem with CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50) records based on a single INSERT to a view. Either I get a 'too...
3
by: William Wisnieski | last post by:
Hello Again, I'm really stuck on this one.....so I'm going to try a different approach to this problem. I have a query by form that returns a record set in a datasheet. The user double...
3
by: google | last post by:
Hello, I am trying to create a listbox that users can select multiple entries on. I want Access to put each on of those selections in a different row on a particular table. This table will also...
4
by: Ed L. | last post by:
I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
13
by: PinkBishop | last post by:
I am using VS 2005 with a formview control trying to insert a record to my access db. The data is submitted to the main table no problem, but I need to carry the catID to the bridge table...
7
by: bcap | last post by:
hi, I am trying to create a form where you may have more than one person at a meeting, but want to have them be related to the same meeting. I have a mulitple select text area and if you...
1
by: Sunray | last post by:
I have a form called the sales form and i have 2 sets of listboxes So what happens is. i add items form the bottom set of list boxes which are bound to a data base to the top set of list boxes which...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.