?
Solved

updating database (oracle) through a dataadapter

Posted on 2003-11-04
12
Medium Priority
?
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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 …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

800 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