Solved

updating database (oracle) through a dataadapter

Posted on 2003-11-04
12
531 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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
 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 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