Click here to Skip to main content
Click here to Skip to main content

How to retrieve, add, modify, and delete Oracle Data with a DataGrid

, 26 Jul 2006
Rate this:
Please Sign up or sign in to vote.
a few lines of code to do many tasks on the Oracle Data
  • <A href="DataGridEditor/DataGridEditor_src.zip"></A><A href="DataGridEditor/DataGridEditor_src.zip">Download source files - 2 Kb</A> 
  • Download demo project - 16 Kb

Sample Image - DatagridImg.jpg

Introduction

            We all know that a good application is that one which the user can use easily, simply and have his jobs done quickly, I thought it will be great to have all this done by one tool and small code using a DataGrid.

This sample application shows how to Retrieve, Add, Modify, Delete Data from an Oracle Data Base.

 

Requirments:

 

First:

 

    You have to run this sql statement on the SQL plus to create the required table:

Create table testTbl (id number(3),name varchar2(10), job varchar2(10));

 

Second:

If you don't have oracle tools installed on your system then install it and then add “Oracle.DataAccess.DLL” from Project => Add Reference => Brows

Code:

 

imports

Imports</FONT> Oracle.DataAccess.Client

Declarations

Dim conn As New OracleConnection("Data Source=ServiceName;User ID=UserName;Password=Password")

Dim da As OracleDataAdapter = New OracleDataAdapter

Dim ds As New DataSet

Retrieving Code

da.SelectCommand = New OracleCommand("select * from testTbl", conn)

Try

If conn.State = ConnectionState.Closed Then conn.Open()

da.Fill(ds, "test")

myDataGrid.DataSource = ds.Tables("test")

conn.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try


 

Insert, Update, Delete Code

Dim param As OracleParameter

If conn.State = ConnectionState.Closed Then conn.Open()

Dim command_builder As New OracleCommandBuilder(da) 'create new command builder and passing the Dataadapter to it

Try

da.UpdateCommand = New OracleCommand("UPDATE testTbl SET name=:name, job=:job where id=:OLDid", conn) 'building Update command

da.DeleteCommand = New OracleCommand("DELETE FROM testTbl where id=:OLDid", conn) ' building Delete command

da.UpdateCommand.Parameters.Add(":name", OracleDbType.Varchar2, 10, "name") 'Passing parameters to the command builder

da.UpdateCommand.Parameters.Add(":job", OracleDbType.Varchar2, 10, "job")

param = da.UpdateCommand.Parameters.Add(":OLDid", OracleDbType.Int32, 3, "id")

param = da.DeleteCommand.Parameters.Add(":OLDid", OracleDbType.Int32, 3, "id")

param.SourceVersion = DataRowVersion.Original

da.Update(ds, "test") 'Update Data in the Table

conn.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Najeeb Al-Majidy
Web Developer
Yemen Yemen
No Biography provided

Comments and Discussions

 
QuestionThanks Najeeb! PinmemberMember 90915697-Jun-12 23:08 
GeneralPerfect! PinmemberStan Zieg16-Feb-10 4:48 
QuestionORA-12154 :TNS:Could not resolve the connect identifier specified". Pinmembersushantsavant10-Feb-10 1:58 
GeneralNice and easy Pinmembervbsquire16-Jul-07 19:27 
Thanks for this great post! It was a good start for me and it actually worked once I added my connection info!
 

GeneralGreat code Pinmemberjammoral31-May-07 8:46 
QuestionCommand Builder?? PinmemberMuammar©12-Dec-06 22:56 
Generalgood post Pinmembersal889-Dec-06 16:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411019.1 | Last Updated 27 Jul 2006
Article Copyright 2006 by Najeeb Al-Majidy
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid