Solved

updating database (oracle) through a dataadapter

Posted on 2003-11-04
12
522 Views
Last Modified: 2008-03-03
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?
0
Comment
Question by:meers1974
12 Comments
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9684230
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
0
 
LVL 3

Expert Comment

by:vikasaagrawal
ID: 9685179
Why dont you use command builder rather than creating manual insert command.

Vikas
0
 

Author Comment

by:meers1974
ID: 9686718
Vikas,
Can you help me use the command builder?
Where can I find information abt. it?

Thanks.
0
 

Author Comment

by:meers1974
ID: 9689834
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?
0
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9692131
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
0
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9692140
before this you need to create a connection
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:meers1974
ID: 9693951
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.
0
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9694231
Okay what about by points
0
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9694240
Okay what about my points
0
 

Author Comment

by:meers1974
ID: 9702637
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 10255817
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
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 10314750
PAQed, with points refunded (30)

SpazMODic
EE Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now