asked on

updating database (oracle) through a dataadapter

I have a SQL Server stored procedure that retrieves abt. 10 rows of data for 3 tables
I have retrieved this inside a dataset (there will be totally 3 tables)
I pass each of the table to a function in which I build a InsertCommand for a Dataadapter.
And I add parameters:(one of the functions below)

parameters-obj_conn, objdata_tbl, objTrans

Try
obj_adap = New OleDb.OleDbDataAdapter()

obj_adap.InsertCommand = New OleDb.OleDbCommand()
obj_adap.InsertCommand.CommandText = _
"Insert into MARKET_HIST " & _
"values (?, ?, ?)"

obj_adap.InsertCommand.Connection = objConn

obj_adap.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", OleDb.OleDbType.Integer))
obj_adap.InsertCommand.Parameters.Item(0).SourceColumn = "ID"

obj_adap.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@HIST_ID", OleDb.OleDbType.Integer))
obj_adap.InsertCommand.Parameters.Item(1).SourceColumn = "HIST_ID"

obj_adap.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@DT_BOUGHT", OleDb.OleDbType.Date))
obj_adap.InsertCommand.Parameters.Item(2).SourceColumn = "DT_BOUGHT"

obj_adap.InsertCommand.Transaction = objTrans

i = obj_adap.Update(objdata_tbl)

For some reason, the parameters are not populated...

ie., If I print obj_adap.InsertCommand.commandtext, I only get
Insert into MARKET_HIST values(?, ?, ?)

I do not get the actual values..
Can someone help me with this?

rajaamirapu

There is a .net data provider for oracle available for download
see the link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp
The .net data provider for oracle is more faster than oledb data provider
Try to use this

vikasaagrawal

Why dont you use command builder rather than creating manual insert command.

Vikas

meers1974

ASKER

Vikas,
Can you help me use the command builder?
Where can I find information abt. it?

Thanks.

meers1974

ASKER

I tried the .net data provider.
Still I have the above problem. The Insert command has to build properly inorder for me to go to performance issues.. The Insert command is not building properly. As I said, the parameters do not get populated.. Any ideas?

rajaamirapu

See the example of oracle .net data provider

Dim da As OracleDataAdapter = New OracleDataAdapter()
Dim cmd As OracleCommand
Dim parm As OracleParameter

' Create the SelectCommand.

cmd = New OracleCommand("SELECT * FROM Dept " & _
"WHERE DName = :pDName AND Loc = :pLoc", conn)

cmd.Parameters.Add("pDName", OracleDbType.NVarChar, 14)
cmd.Parameters.Add("pLoc", OracleDbType.NVarChar, 13)

da.SelectCommand = cmd

' Create the InsertCommand.

cmd = New OracleCommand("INSERT INTO Dept (DeptNo, DName) " & _
"VALUES (pDeptNo, pDName)", conn)

cmd.Parameters.Add("pDeptNo", OracleDbType.Number, 2, "DeptNo")
cmd.Parameters.Add("pDName", OracleDbType.NVarChar, 14, "DName")

da.InsertCommand = cmd

Return da
End Function

rajaamirapu

before this you need to create a connection

meers1974

ASKER

I do have a connection. I have only posted a part of the code.
I have fixed the problem. I had to add an "AcceptChangesDuringFill = False" statement to the dataadapter.
It works fine now.
Thanks for your time.

rajaamirapu

Okay what about by points

rajaamirapu

Okay what about my points

meers1974

ASKER

Hi
Your answers did not help me.
The real problem was to add the line:
AcceptChangesDuringFill = False

I did not see that in your code. So, I did not give the points.
If you insist I'll definitely award the points. No problems.
Thanks

Bob Learned

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer

ASKER CERTIFIED SOLUTION

SpazMODic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial