RE: Multiple database(datasource) updation using single datagridvi

hi,
in my above 2 questions, i need to get the following things to be done in a
single process

when the user click on save, it should save oracle database as well as
foxpro database at a time, but here both database structures are different,
but only one datagridview, having the information for both databases, as i
did solved one in the above scenario, how can i add, the foxpro part, is it
possible to solve through some relations between Oracle DATATABLE and VFP
Datatable, ( but oracle datatable is bound to the datagridview), now how can
i map VFP datatable or TO SAVE VFP part in the same time, please advice in
detail.. waiting to get your reply
regards,
Anis

"Komandur Kannan" wrote:

hi helo,
for the query, i tried these methods also their after..

1. i did mapped ( bind) wiht oracle datatable into datagridview
2. their after in the same procedure AGAIN, i bind with the same
datagridview, this time with vfp struture ( but in the same procedure)

in this process, i found the following drawbacks

1. the binding process is very slow, since both databases affect the same
datagridview
2. updation gives error in vfp part

so i dont want to bind vfp database into datagridview, since my primary
oracle database is bound with it, is it possible to solve through the
relation between 2 datatables, since the process shouldnt affect the speed of
the entire process, i beleive filling ( binding) the datagridview, from both
database is not advicable at a time, since its taking somuch of time, plz
advice in detail, awaiting your reply

regards,
Anis

"Komandur Kannan" wrote:

hi,
Operating System: Xp
Language :Vb.Net 2005
Connetion :Oledb
Provider :OraOLEDB.Oracle
Oracle Version :9i
vfp connectionstring : gstrPVMDBPath_I = "Provider=vfpoledb.1;Data
Source=\\it-development\IT
Development\Development\Chandlers\Sales\PV\Data;Collating Sequence=Machine"

im binding and storing the oracle database, its working fine with number of
rows getting inserted/updated/deleted in a single process as corresponding
datatable is bound to that datagridview..

here im giving u the way wht im doing with it..

process 1 : binding..
----------------------=

Dim strTempQuery As String = "Select slno as
dgod_Sl,clinnumber as dgod_CLIN,SUBSTR(itemcode,1,11) as
dgod_ItemCode,subslno as
dgod_Subs,substr(ord_nsn,1,4)||'-'||substr(ord_nsn,5,2)||'-'||substr(ord_nsn,7,3)||'-'||substr(ord_nsn,11,4)
as dgod_NSN,ficcode as dgod_FIC,descript AS dgod_Description,class as
dgod_Clas,unit AS dgod_Unit,qty_sup as dgod_Qty,in_price as dgod_UnitRate,
qty_sup * in_price as dgod_UnitValue, subs_reason as dgod_Reason,qty_req as
dgod_OrderQty,storeind as dgod_OrderLocation,DECODE(LAST_UPD_BY,'EDI',3,4) as
dgod_DECIDER,company_code as dgod_CompCode,ordnumber as
dgod_OrdNo,last_upd_by as dgod_execode FROM SLS_TRNPV_ORDdet where
ordnumber=" & strOrdNumber & " ORDER BY slno"

ldatadapterOrdDetl = New OleDbDataAdapter

ldatadapterOrdDetl.SelectCommand = New
OleDbCommand(strTempQuery, OleDbConn)

ldatadapterOrdDetl.Fill(ldatatableOrdDetl)
lbindingSource.DataSource = ldatatableOrdDetl

dgOrderDetail.AutoGenerateColumns = False
dgOrderDetail.DataSource = lbindingSource

dgOrderDetail.Columns("dgod_Sl").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Sl").ToString
dgOrderDetail.Columns("dgod_CLIN").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Clin").ToString
dgOrderDetail.Columns("dgod_ItemCode").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_ItemCode").ToString
dgOrderDetail.Columns("dgod_Substitute").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Subs").ToString
dgOrderDetail.Columns("dgod_NSN").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_NSN").ToString
dgOrderDetail.Columns("dgod_FIC").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_FIC").ToString
dgOrderDetail.Columns("dgod_Description").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Description").ToString
dgOrderDetail.Columns("dgod_Clas").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Clas").ToString
dgOrderDetail.Columns("dgod_Unit").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Unit").ToString
dgOrderDetail.Columns("dgod_Quantity").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Qty").ToString
dgOrderDetail.Columns("dgod_UnitRate").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_UnitRate").ToString
dgOrderDetail.Columns("dgod_Value").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_UnitValue").ToString
dgOrderDetail.Columns("dgod_Reason").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_Reason").ToString
dgOrderDetail.Columns("dgod_OrderQty").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_OrderQty").ToString
dgOrderDetail.Columns("dgod_Location").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_OrderLocation").ToString
dgOrderDetail.Columns("dgod_DECIDER").DataPropertyName =
ldatatableOrdDetl.Columns("dgod_DECIDER").ToString

process 2 - storing
----------------------

'Infer the schema using database metadata through a
DataAdapter class. Using DataAdapter.Fill creates tables and columns matching
the metadata from DataAdapter's SelectCommand. For this to work,
DataAdapter's MissingSchemaAction property must be set to Add or AddWithKey
' this ONLY MADE FOR BEST RESULT,without this also, the
transaction will work
ldatadapterOrdDetl.MissingSchemaAction =
MissingSchemaAction.AddWithKey

' oracle insertion
ldatadapterOrdDetl.InsertCommand = New OleDbCommand("INSERT
INTO sls_trnpv_orddet (company_code, Ordnumber, slno,
clinnumber,SUBSLNO,ord_nsn,our_nsn,STOREIND,itemcode,ficcode,descript,ORD_UNIT,unit,in_price,sellprice,class,origpo_qty,qty_req,qty_sup,subs_reason,last_upd_by)
VALUES (?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?,?, ?, ?)",
LoraConnection, LoraTranXlu1)
ldatadapterOrdDetl.UpdateCommand = New OleDbCommand("UPDATE
sls_trnpv_orddet SET slno =?,STOREIND =?,itemcode =?,qty_sup=?,SUBS_REASON=?
WHERE Company_Code=? and Ordnumber =? and slno=?", LoraConnection,
LoraTranXlu1)


ldatadapterOrdDetl.InsertCommand.Parameters.Add("@company_code",
OleDbType.Char, 10, "dgod_CompCode")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@Ordnumber",
OleDbType.Numeric, 7, "dgod_OrdNo")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@slno",
OleDbType.VarChar, 5, "dgod_sl")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@clinnumber",
OleDbType.VarChar, 6, "dgod_Clin")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@SUBSLNO",
OleDbType.VarChar, 5, "dgod_Subs")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@ord_nsn",
OleDbType.VarChar, 13, "dgod_NSN")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@our_nsn",
OleDbType.Char, 13, "dgod_NSN")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@STOREIND",
OleDbType.VarChar, 10, "dgod_OrderLocation")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@itemcode",
OleDbType.VarChar, 30, "dgod_ItemCode")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@ficcode",
OleDbType.Char, 3, "dgod_FIC")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@descript",
OleDbType.VarChar, 80, "dgod_Description")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@ORD_UNIT",
OleDbType.VarChar, 2, "dgod_Unit")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@unit",
OleDbType.Char, 10, "dgod_Unit")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@in_price",
OleDbType.Decimal, 10, "dgod_UnitRate")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@sellprice",
OleDbType.Decimal, 10, "dgod_UnitRate")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@class",
OleDbType.Char, 1, "dgod_Clas")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@origpo_qty",
OleDbType.Decimal, 10, "dgod_Qty")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@qty_req",
OleDbType.Decimal, 10, "dgod_Qty")
ldatadapterOrdDetl.InsertCommand.Parameters.Add("@qty_sup",
OleDbType.Decimal, 10, "dgod_Qty")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@subs_reason",
OleDbType.VarChar, 2, "dgod_Reason")

ldatadapterOrdDetl.InsertCommand.Parameters.Add("@last_upd_by",
OleDbType.VarChar, 3, "dgod_execode")

ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@slno",
OleDbType.Char, 5, "dgod_sl")
ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@STOREIND",
OleDbType.VarChar, 10, "dgod_OrderLocation")
ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@itemcode",
OleDbType.VarChar, 30, "dgod_Itemcode")
ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@qty_sup",
OleDbType.Decimal, 10, "dgod_Qty")

ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@subs_reason",
OleDbType.VarChar, 2, "dgod_Reason")


ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@oldcompcode",
OleDbType.Char, 10, "dgod_CompCode").SourceVersion = DataRowVersion.Original

ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@oldOrdnumber",
OleDbType.Numeric, 7, "dgod_OrdNo").SourceVersion = DataRowVersion.Original
ldatadapterOrdDetl.UpdateCommand.Parameters.Add("@oldslno",
OleDbType.VarChar, 5, "dgod_sl").SourceVersion = DataRowVersion.Original

ldatadapterOrdDetl.Update(ldatatableOrdDetl)

' Oracle Transaction commit
LoraTranXlu1.Commit()

' commit only iff, the Case belongs VFP also
If flgRequired2SavenVFP = True Then
' vfp part commit as well demolish its db
Lvfptran1.Commit()
lvfpConnection.Dispose()
End If

' ora db smash
LoraConnection.Dispose()

their after committed the transaction and its all working fine,

NOW MY QUESTION Is...

in a single datagridview, how can i use, multiple databases, one is in
oracle ( the above scenario), the another one is in foxpro ( still not able
to do so), here, my point is, foxpro table is slightly structure changed from
oracle table

so how can i solve this in single place, since i tried, passing different
way of saving and binding with vfp database, its either failed or very slow
in process ( but both ways couldnt succeeded), so can u advice me,

1). HOW CAN I USE SINGLE DATAGRIDVIEW FOR MULTIPLE DATASOURCES ( few columns
are slightly change from eachother datasources), for eg, subs_reason is the
field in oracle, and subsreason is the field in vfp, but i dont want to keep
both datasources structure as same, since its already connected with many
process)

2). HOW TO BIND ( in vfp table, does it requires to bind again with the same
datagridview, since its very slow, when i tried this)

3). AND UPDATE PART OF THE BOTH DATASOURCES ( THE ONE I ALREADY SOLVED, I
NEED THE SOLUTION FOR ANOTHER DATASOURCE, ie, VFP)

awaiting to get your valuable reply
regards,
Anis



.

Relevant Pages