Multiple datasource insertion from Single vb.net Datagridview

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)

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

1. first, i did mapped ( bind) wiht oracle datatable into datagridview
2. secondly, i bind vfp 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

in the above scenario, 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



.

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)
  • RE: Multiple database(datasource) updation using single datagridview
    ... their after in the same procedure AGAIN, i bind with the same ... datagridview, this time with vfp struture ... oracle database is bound with it, is it possible to solve through the ...
    (microsoft.public.windows.group_policy)
  • 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)
  • Multiple database(datasource) updation using single datagridview
    ... Oracle Version:9i ... 'Infer the schema using database metadata through a ... the Case belongs VFP also ... HOW CAN I USE SINGLE DATAGRIDVIEW FOR MULTIPLE DATASOURCES (few columns ...
    (microsoft.public.windows.group_policy)
  • Re: newbie- Views and session
    ... OK, if the database is Oracle, why have VFP in the mix at all? ... When you say session do you mean an ASP Session or a VFP Session class (or ...
    (microsoft.public.fox.programmer.exchange)