Example of most efficient VB.Net connection method for inserting rows into Oracle 8 or 9 using Visual Studio 2005 or 2008

I'm testing a simple VB.Net (Visual Studio 2008) Windows Application, i.e. a form with one command button.
My machine is running Windows XP Professional with SP2 and has the Oracle9i R2 Client installed.
I'm trying to connect to a remote Oracle9i database hosted on the LAN.
I eventually need to insert rows into Oracle tables using VB.Net web services methods called synchronously.
However, I need immediate assistance in setting up a successful VB.Net Oracle9i connection string and retrieve rows from a table. Then display the retrieved rows in a window.
How can I display the retrieved rows?
 After I overcome this hurdle, I'll be closer to my real objective of inserting rows into the Oracle table using the same connection.
Of course, I searched the archives on this site and tested several Oracle connection strings with
sample code to retrieve rows from an Oracle table. Of course, I haven't had any success so far.
Hopefully, someone can help me get over the hump.

The following Button code built successfully but the Oracle connection failed after checking the
sqlnet.log file generated by Oracle.:

Imports System.Data.OleDb
Imports System.Windows.Forms.Form

Public Class Form1
    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim dr As OleDbDataReader

    'declaration

    Private Sub Test_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Test_Button.Click

        myConnection = New OleDbConnection

        myConnection.ConnectionString = "Provider=MSDAORA.1;UserID=REPTUSER;password=r3p0rts;database=SIG9I.CITODNS.GOV.BZ"

        'MSDORA is the provider when working with Oracle

        Try
            myConnection.Open()
            'opening the connection
            myCommand = New OleDbCommand("SELECT * FROM tax_centre", myConnection)
            'executing the command and assigning it to connection
            dr = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader
                Windows.Forms.MessageBox.Show("TAX_CENTRE_NO" & dr(0))
                Windows.Forms.MessageBox.Show("TAX_CENTRE_DESC" & dr(1))
                Windows.Forms.MessageBox.Show("CONTACT_INFO" & dr(2))
                Windows.Forms.MessageBox.Show("REV_DEPT_NO" & dr(3))
                'displaying data from the table
            End While
            dr.Close()
            myConnection.Close()
        Catch ex As Exception
        End Try
    End Sub

End Class

======================================

The sqlnet.log file has the following error:

Fatal NI connect error 12560, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\DW\Projects\VBnetWinApp\WindowsApplication1\bin\Debug\WindowsApplication1.exe)(HOST=WS08061X01)(USER=aweatherburnedb))))

I observe that the connection is not being made to the Oracle database that I specified in the
connection string. Also Oracle is trying to connect to a local database (BEQ)  instead of using the
SQLNET,ora file containing the connection information for the database  specified in the
connection string.

Thanks in advance for your help. Knowledge is power, but it is only useful if it is shared.
desmondgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PockyMasterCommented:
you can find connectionstrings on this website:
http://www.connectionstrings.com/?carrier=oracle

there's a section about connection via tnsnames.ora
0
desmondgAuthor Commented:
Thanks for your input PockyMaster.
I had been to that site already, but did not try all the connection strings.
The OraOleDB provider works for myCommand.ExecuteReader()
but no rows get inserted after modifying the SQL statament and
using myCommand.ExecuteNonQuery().

I'll give you the points.

Please advise.



Imports System.Data.OleDb
Imports System.Windows.Forms.Form

Public Class Form1
    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim SelCommand As OleDbCommand
    Dim CommitCommand As OleDbCommand
    Dim dr As OleDbDataReader

    'declaration

    Private Sub Test_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Test_Button.Click

        myConnection = New OleDbConnection

        myConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=SiG9i;User Id=REPTUSER;Password=r3p0rts"


        Try
            myConnection.Open()

            myCommand = New OleDbCommand("INSERT INTO tax_center (tax_centre_no,tax_centre_desc) VALUES (21,'GST - BZ')", myConnection)
            myCommand.ExecuteNonQuery()
            CommitCommand = New OleDbCommand("COMMIT", myConnection)
            CommitCommand.ExecuteNonQuery()

            myConnection.Close()

        Catch ex As Exception

        End Try

    End Sub

End Class
0
desmondgAuthor Commented:
I found my error PockyMaster and other Gurus.
I successfully inserted a row into the Oracle table.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

desmondgAuthor Commented:
Explain how to put this button code in a VB.Net Web Service method that will be called synchronously.
Explain how,  what and where to publish the VB.Net Web Service application files.
Also explain how to Call,  test or execute this Web Service method after I create it.
Remember Knowledge is power, but it is only useful if it is shared.
 
0
desmondgAuthor Commented:
By the way, I'm using Visual Studio 2008.
Regards to all.
0
desmondgAuthor Commented:
Below is the code I successfully tested in a Visual Studio 2008 VB.Net Widows App:
 
Imports System.Data.OleDb
Imports System.Windows.Forms.Form
 
Public Class Form1
    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim SelCommand As OleDbCommand
    Dim CommitCommand As OleDbCommand
    Dim dr As OleDbDataReader
    Dim ins As Integer
    Dim com As Integer
 
    'declaration
 
    Private Sub Test_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Test_Button.Click
 
        myConnection = New OleDbConnection
 
                myConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=SiG9i;User Id=REPTUSER;Password=r3p0rts"
 
        Try
            myConnection.Open()
           myCommand = New OleDbCommand("INSERT INTO tax_center (tax_centre_no,tax_centre_desc) VALUES (22,'GST - CZL')", myConnection)
            ins = myCommand.ExecuteNonQuery()
            CommitCommand = New OleDbCommand("COMMIT", myConnection)
            com = CommitCommand.ExecuteNonQuery()
            SelCommand = New OleDbCommand("SELECT * FROM tax_center WHERE tax_centre_no = 22", myConnection)
            dr = SelCommand.ExecuteReader()

            While dr.Read()

                Windows.Forms.MessageBox.Show("TAX_CENTRE_NO: " & dr(0) & "  ;   TAX_CENTRE_DESC: " & dr(1))
                             
            End While
            dr.Close()
            myConnection.Close()
        Catch ex As Exception
        End Try
    End Sub
 
End Class
 
 
This is the code in Service1.asmx.vb ;  the Test WebService App that I created:
 
Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.ComponentModel

Imports System.Data.OleDb

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

 '<System.Web.Script.Services.ScriptService()> _

<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _

<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<ToolboxItem(False)> _

Public Class Service1

Inherits System.Web.Services.WebService

<WebMethod()> _

Public Function HelloWorld() As String

1.) 'Can I just insert the above code here, changing the function name of course and

'converting the procedure into a function?

converting

Return "Hello World"

End Function


End Class

 

2.) Do I need to change the following namespace reference:

<System.Web.Services.WebService(Namespace:="http://tempuri.org/")>

 

3.) Using IIS on my local Windows XP machine, how can I publish and call

this method to test the insert code?

 

Thanks in advance for your help.

Knowledge is power, but it is only useful if it is shared.

0
PockyMasterCommented:
1)

I would prefer to put the data access code in a seperate layer (DAL)
and call that via a BLL or if you please, from your WebService.
If you want to pass the data via a webservice, you can use a dataadapter first, to fill a dataset, return that from the call to the DAL, and pass it on from your webservice method.

2)
Yeah.. it's better to change it to your own namespace.

3)
you can right click the folder in which you have setup your webservice code.
share -> webshare -> set the virtual folder name
OR
go to project properties -> web -> create virtual folder

if you want to use it in your form -> form project -> add web reference -> from project -> select your webservice project

(Sorry have no development environment on this machine here, have to do from memory)..
0
desmondgAuthor Commented:
Actually, the web service methods will be hosted by a web server (IIS). One application will make calls
(synchronously) to these web services (web methods) as a result of user events, passing data values to web services (web methods).
I need to develop the code using VB.Net inside these called web methods to get the values passed from
one client/server application (SQL Server 2005) and insert them into another parallel client/server application data source (Oracle80). The objective is to synchronize a legacy application running Oracle80 with transactions generated in a newer client/server application with  SQL Server 2005 backend.

Please clarify your recommendations in 1) based on my clarification of the solution requirements.

Thanks in advance for your time.
0
PockyMasterCommented:
in the following thread I've put a code sample of how I would implement something similar

http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_Studio_2008/Q_23096489.html#discussion

In that solution there are samples of calling the DAL via a webservice or calling them directly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.