Insert textbox values to Oracle DB

Question

I've never done INSERT statement to Oracle in VB.NET, so I need a little help. My code doesn't work, after executing nothing happens - no errors and no Insert. What is wrong or what am I missing here ? (Field1 and Field2 are just table fields, not a primary key).

Imports System.Data
Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
Imports Oracle.DataAccess.Types

Public Class Save_Records 

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

     Const conn As String = "Data Source=MyDB;User Id=Lucky;Password=MyPassword;"

        Using cn As New OracleConnection(conn)

            cn.Open()

            Using cmd As New OracleCommand()
                cmd.CommandText = "INSERT into MyTable (Field1,Field2) values('" & TxtField1.Text & "','" & TxtField2.Text & "')"
                cmd.ExecuteNonQuery()

            End Using

        End Using

      End Sub

End Class

commit ? where should I add this ? I added cmd.CommandType = CommandType.Text before cmd.CommandText and now I receive error: "External component has thrown exception". — LuckyLuke82
– LuckyLuke82, Commented Aug 8, 2016 at 7:29
You should be using database parameters to avoid sql injection issues. Are you getting any errors? What is the result of the ExecuteNonQuery statement? — Andrew Mortimer
– Andrew Mortimer, Commented Aug 8, 2016 at 7:32
@AndrewMortimer, read my previous post, this error is result of ExecuteNonQuery statement. — LuckyLuke82
– LuckyLuke82, Commented Aug 8, 2016 at 7:34

Andrew Mortimer · Accepted Answer · 2016-08-08 08:07:23Z

2

This method is a starting place for how to insert into Oracle

Private Function insertRow(connectionString As String) As Boolean

    Using cn As OracleConnection = New OracleConnection(connectionString)

        cn.Open()

        Using cmd As OracleCommand = New OracleCommand()

            Const sql As String = "Insert into test_table (val1, val2) values (:var1, :var2)"
            cmd.Connection = cn
            cmd.Parameters.Add(New OracleParameter("var1", TxtField1.Text))
            cmd.Parameters.Add(New OracleParameter("var2", TxtField2.Text))
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()

        End Using

    End Using

End Function

edited Aug 8, 2016 at 8:07

answered Aug 8, 2016 at 7:59

Andrew Mortimer

2,3707 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

LuckyLuke82 Over a year ago

I tried this, what are those parameters "test1" & "test2", should I add TxtField1 & TxtField2 there ?? I tried running your code, but again error: "External component has thrown an exception."...Am I missing some references ? see my decalaration above public class, in my edited question...

Andrew Mortimer Over a year ago

I have similar Imports to you and a project reference to Oracle.DataAccess. Can you inspect all the information in the Exception and see if it contains some more details on the failure?

LuckyLuke82 Over a year ago

"An unhandled exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll"....And under detail : "{"ORA-01036: illegal variable name/number"}". This is all I can see under this error :""External component has thrown an exception." - for ExecuteNonQuery statement.

Andrew Mortimer Over a year ago

Does your sql run when you run it against the database via your normal sql client (Toad / Oracle SQL Developer / sql+ worksheet / whatever)? Are you using parameters?

LuckyLuke82 Over a year ago

I'm not following you right now, but while I'm executing this code in VB.NET I have opened Toad Oracle. Reading from Oracle goes fine doing same, just not Insert...When I run Insert in Toad, my SQL does Error :"ORA-01400: cannot insert NULL into ("MySchema"."MyTable"."PrimaryKey")....Is primary key a problem ? How do I automatically add it, Its a number field ?

|

Collectives™ on Stack Overflow

Insert textbox values to Oracle DB

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related