Moving a row from one dataset to another.

[EStallworth]

I am currently developing an application that queries an informix database. After I have loaded the dataset with the information I need, I am attempting to pick out a certain row and move it to another dataset in order to set up a binding for a textbox. Only problem is that I receive an error message saying the row is already contained in another dataset. I am moving the particular row from one to the other because I do not write queries that well and cannot pick out the individual row that I need the first time around. I load up all of the transactions of a certain account and I sort it according to the most recent transaction(based on date). Why is it that you cannot bind an object to a dataset that contains a copy of another dataset's row?

[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]InformixDataSet11.Clear()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ODBCAdapt [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.Odbc.OdbcDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connection [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcConnection([/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] selectcommand [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcCommand([/SIZE][SIZE=2][COLOR=#800000]"Select trans_no, trans_date, check_no, trans_amt, vend_no FROM informix.transact WHERE vend_no = ? ORDER BY trans_date DESC"[/COLOR][/SIZE][SIZE=2], connection)[/SIZE]
 
[SIZE=2]selectcommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"?"[/COLOR][/SIZE][SIZE=2], Odbc.OdbcType.NVarChar, 10, [/SIZE][SIZE=2][COLOR=#800000]"vendor_no"[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] param [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ComboBox1.Text.Remove(2, 1)[/SIZE]
 
[SIZE=2]selectcommand.Parameters([/SIZE][SIZE=2][COLOR=#800000]"?"[/COLOR][/SIZE][SIZE=2]).Value = param.Remove(5, 1)[/SIZE]
 
[SIZE=2]ODBCAdapt.SelectCommand = selectcommand[/SIZE]
[SIZE=2]ODBCAdapt.Fill(InformixDataSet11)[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] foundrow [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.DataTable[/SIZE]
[SIZE=2]foundrow.Rows.Add(InformixDataSet11.Table.Rows.Item(1))[/SIZE]
 
[SIZE=2]checkamttxt.DataBindings.Add([/SIZE][SIZE=2][COLOR=#800000]"Text"[/COLOR][/SIZE][SIZE=2], foundrow, [/SIZE][SIZE=2][COLOR=#800000]"foundrow.trans_amt"[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]   MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]

 

[lingsn]

I think you have to use

foundrow.ImportRow(InformixDataSet11.Table.Rows.Item(1))

 

[cjard]

Would you like some assistance writing a query that picks out the row you want? Kinda treat the disease, not the symptoms sort of thing?

 

[EStallworth]

Would you like some assistance writing a query that picks out the row you want? Kinda treat the disease, not the symptoms sort of thing?

Yes I would definitely like some help! The select command I have posted previously only orders the info by date. I have been told that I do not need to pull transactions that are > 3 years old. So I would like it to pull the same info posted but from 2004 and up. I was going to narrow it down to the most recent but was told that it was actually a good thing that I pull up some of the history.

Also how do you manipulate dates within queries? I need something along the lines of the DateDiff function. I need to compare the two most recent transactions, based on date, to be able to determine delinquency. This is for monthly account types so I believe just comparing the two months should suffice. I am working on it right now.

 

[cjard]

I have no direct experience with the database you are using, but using logic within queries is not too difficult. If the numbers you want to compare are on the same row, then its very easy. If they are on different rows you must either pivot the data, or use lag/lead analytical functions. Are you able to check if your DB supports these?