Solved

Bulkcopy from SQL Server to Oracle from vb.net

Posted on 2010-09-02
5
867 Views
Last Modified: 2012-06-27
Hi,
I'm trying to move date from SQL Server database into Oracle db using windows form as GUI.
I have made this works ok in reversed way (from oracle to SQL server) but with this direction I'm facing with the problem in line 28 (copy is underlined) and it says

Overload resolution failed because no accessible 'New' can be called with these arguments:
    'Public Sub New(connectionString As String)': Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'String'.
    'Public Sub New(connection As System.Data.SqlClient.SqlConnection)': Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'System.Data.SqlClient.SqlConnection'.      


Please if someone can help me to solve this problem

Thanks in advanced....

Public Sub Retrieve_Records()

        Dim conn1 As New SqlConnection(My.Settings.MyCon)
        conn1.Open()

        Dim cmd1 As New System.Data.SqlClient.SqlCommand()
        cmd1.Connection = conn1
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select * from MySqlServerTable where date= @date_val"
        cmd1.Parameters.Add("@date_val", SqlDbType.NVarChar)
        cmd1.Parameters("@date_val").Value = Me.DateTimePicker1.Value.ToString("yyyy.mm.dd")
        cmd1.CommandTimeout = 0
        Dim rdr As SqlDataReader = cmd1.ExecuteReader
        Dim dt As New DataTable("IFRS")
        dt.Load(rdr)
        rdr.Close()
        conn1.Close()

        Using cn As New System.Data.OleDb.OleDbConnection(m_sConn2)
            cn.Open()

            Dim cmd As New System.Data.OleDb.OleDbCommand
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM  myoracletable"
            cmd.ExecuteNonQuery()

            Using copy As New SqlBulkCopy(cn)
                copy.DestinationTableName = "myoracletable"
                copy.WriteToServer(dt)
            End Using
        End Using
        MessageBox.Show("Successfully inserted on Oracle!", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

    End Sub

Open in new window

0
Comment
Question by:alsam
[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
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:appari
ID: 33594064
SqlBulkCopy is part of System.Data.SQLClient and can only be used with SQLConnection. here you are trying to create SqlBulkCopy instance passing OleDBConnection. its not possible.
0
 
LVL 39

Expert Comment

by:appari
ID: 33594101
system.data.oledb has no bulkcopy option. If you want to do bulk copy to oracle db try using ODP.net (oracle data provider .net). ODP provides bulk copy option simillar to sqlclient.

http://download.oracle.com/docs/pdf/E15167_01.pdf
0
 
LVL 1

Author Comment

by:alsam
ID: 33594486
Appari,
thank you for your reply...
I have changed from oledb to ODP (as in VB below)...
Could you please help me with some instructions/workarround how to finish my vb  code with bulk copy option provided by ODP (from line "Using copy As New ..."
I would appreciate a lot your time and effort...
thank you....

Public Sub Retrieve_Records()

        Dim conn1 As New SqlConnection(My.Settings.MyCon)
        conn1.Open()

        Dim cmd1 As New System.Data.SqlClient.SqlCommand()
        cmd1.Connection = conn1
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select col1, col2, col3 from mysqlservertable"
        cmd1.Parameters.Add("@datum_val", SqlDbType.NVarChar)
        cmd1.Parameters("@datum_val").Value = Me.DateTimePicker1.Value.ToString("yyyy.mm.dd")
        cmd1.CommandTimeout = 0
        Dim rdr As SqlDataReader = cmd1.ExecuteReader
        Dim dt As New DataTable("IFRS")
        dt.Load(rdr)
        rdr.Close()
        conn1.Close()

        Using cn As New OracleConnection(connectStr)
            cn.Open()
            Dim cmd As New OracleCommand
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM myoracletable"
            cmd.ExecuteNonQuery()

                Using copy As New 
                copy.DestinationTableName = "myoracletable"
                copy.WriteToServer(dt)
            End Using
        End Using
        MessageBox.Show("Successfully inserted on Oracle!", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:alsam
ID: 33594982
Hello,
unfortunatelly I have installed Oracle Client on my machine ver. 10.2.0.100
It seem that the same does not support sqlbulk.
Is there another way to workarround this problem....
Thank you in advance...
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 33603392
bulk copy is supported from odp .net Release 11.1.0.6.20, you can update the oracle client to this version to use this bulkcopy class.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with exporting to excel 4 44
VB.Net - MemoryMappedFiles - Confirm receipt 2 35
Optimize the query 5 50
Retrieve Active Directory Groups a User belongs to in VB.NET 3 42
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

735 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