RE: Multiple database(datasource) updation using single datagridview

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

  • RE: Multiple database(datasource) updation using single datagridvi
    ... foxpro database at a time, but here both database structures are different, ... possible to solve through some relations between Oracle DATATABLE and VFP ... Datatable, (but oracle datatable is bound to the datagridview), now how can ...
    (microsoft.public.windows.group_policy)
  • Multiple datasource insertion from Single vb.net Datagridview
    ... Oracle Version:9i ... datatable is bound to that datagridview.. ... 'Infer the schema using database metadata through a ... the Case belongs VFP also ...
    (microsoft.public.vsnet.general)
  • Re: Multiple database(datasource) updation using single datagridvi
    ... when the user click on save, it should save oracle database as well as ... possible to solve through some relations between Oracle DATATABLE and VFP ... Datatable, (but oracle datatable is bound to the datagridview), now how ...
    (microsoft.public.windows.group_policy)
  • Re: Future of VFP (yet again)
    ... It is an Oracle product, so all the microsofties can leave ... So the cost factor for "Mom & Pop" shops to run Oracle applications just ... >that VFP is the best tool for a lot of db apps? ...
    (microsoft.public.fox.programmer.exchange)
Loading