Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

updating database (oracle) through a dataadapter

Posted on 2003-11-04
12
Medium Priority
?
551 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

609 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