Oracle INSERT on VB.net Null reference exception

Question

Please help i am a newbie on oracle database i am used on using mysql and i really don't have any idea how can i debug my code. Here is my code.

Imports System.Diagnostics
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Public Class mainMenu

Dim conn As New OracleConnection
Private cmd As OracleCommand
Private da As OracleDataAdapter
Private cb As OracleCommandBuilder
Private ds As DataSet
Private Sub mainMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    AddHandler txtProductName.TextChanged, AddressOf ValidateInputs
    AddHandler txtQty.TextChanged, AddressOf ValidateInputs
    AddHandler txtPrice.TextChanged, AddressOf ValidateInputs
    conn.ConnectionString = "User Id=antonina" & _
    ";Password=antonina" & _
    ";Data Source=xe"
    Try
        conn.Open()
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString())
    Finally
        conn.Dispose()
    End Try
End Sub
Private Sub ValidateInputs(ByVal Sender As Object, ByVal e As EventArgs)
    Button1.Enabled = Not (txtProductName.Text = String.Empty OrElse txtQty.Text = String.Empty OrElse txtPrice.Text = String.Empty)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
    Dim productName As String
    Dim qty As Integer
    Dim price As Integer
    Dim cat As String
    productName = txtProductName.Text
    qty = Integer.Parse(txtQty.Text)
    price = Integer.Parse(txtPrice.Text)
    cat = cmbCat.Text
    conn = New OracleConnection("User Id=antonina;Password=antonina;Data Source=xe")
    da = New OracleDataAdapter()
    Try
        da.InsertCommand = New OracleCommand("INSERT INTO INVENTORY(INVENTORY_PRODUCTNAME,INVENTORY_CAT,INVENTORY_QTY,INVENTORY_PRICE) VALUES ('" & productName & "'," & qty & "," & price & ",'" & cat & "')", conn)
        Debug.WriteLine("Success")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub
End Class

When i click my add button it says object reference not set to an instance of an object. I know i might just have a stupid syntax error. Please help me.

I am sure that all my variables have a value. also i am using oracle 10g

please post the full code..where is the declaration for da, conn etc — cableload
– cableload, Commented Apr 8, 2016 at 4:26
Add some breakpoints inside the method Button1_Click and check which lines is raising the issue. It is difficult to read and find out. — Som Bhattacharyya
– Som Bhattacharyya, Commented Apr 8, 2016 at 4:36
@SomBhattacharyya i update my code it was the da.InsertCommand = New Oraclecommand(etc) was raising the issue i added the da = New OracleDataAdapter() like afrial_fe said and it successfully remove the error now my problem is even if there is no error on the command my database still empty — Lloyd
– Lloyd, Commented Apr 8, 2016 at 5:20

Wernfried Domscheit · Accepted Answer · 2016-04-08 06:12:07Z

1

I have never seen that somebody uses a OracleDataAdapter in order to insert anything to database, usually OracleDataAdapter is used to retrieve data from database, i.e. a SELECT ... command or a function call which gets a RefCursor.

Perhaps InsertCommand, resp. UpdateCommand and DeleteCommand are used to modify data of OracleDataAdapter after it has been filled from your database - I do not know, I never used it. These command are called with method OracleDataAdapter.Update(da)

Anyway, usually you would do it like this:

Dim cmd As OracleCommand
cmd = New OracleCommand("INSERT INTO INVENTORY (INVENTORY_PRODUCTNAME,INVENTORY_QTY,INVENTORY_PRICE,INVENTORY_CAT) VALUES (:productName, :qty, :price, :cat)", conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("productName", OracleDbType.Varchar2, ParameterDirection.Input).Value = productName 
cmd.Parameters.Add("qty", OracleDbType.Int32, ParameterDirection.Input).Value = qty 
cmd.Parameters.Add("price", OracleDbType.Int32, ParameterDirection.Input).Value = price
cmd.Parameters.Add("cat", OracleDbType.Varchar2, ParameterDirection.Input).Value = cat
cmd.ExecuteNonQuery()

Note the order of columns, see your code:

      INVENTORY_PRODUCTNAME,   INVENTORY_CAT,   INVENTORY_QTY,    INVENTORY_PRICE
('" & productName & "'," &     qty & "," &      price & ",'" &    cat & "')"

edited Apr 8, 2016 at 6:12

answered Apr 8, 2016 at 5:50

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

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

3 Comments

Lloyd Over a year ago

Wow thank you! You're right I use OracleDataAdapter because that's what my teacher used when he teach us how to retrieve data from database and he stop there i thought i can do INSERT by modifying his code. Anyway i still have an error when i click the button it says ORA-01722: Invalid number. Thank you for your help.

Wernfried Domscheit Over a year ago

You mixed the column, see my updated code. Column names and values have to be in the same order.

Lloyd Over a year ago

Oh yea. Sorry stupid mistake. Thank you very much for solving my problem :)

afrial_fe · Accepted Answer · 2016-04-08 04:34:02Z

0

I assume da is for DataAdapter. You need first instantiate your dataadapter by call its constructor.

conn = new OracleConnection(yourConnectionString)
da = New OracleDataAdapter()
da.InsertCommand = New OracleCommand("INSERT INTO INVENTORY(INVENTORY_PRODUCTNAME,INVENTORY_CAT,INVENTORY_QTY,INVENTORY_PRICE) VALUES ('" & productName & "'," & qty & "," & price & ",'" & cat & "')", conn)

also, you missing '()' after VALUES statement in sql syntax.

Other things may help is you can move your parsing statement into try-catch element to avoid error.

hope this help.

edited Apr 8, 2016 at 4:34

answered Apr 8, 2016 at 4:27

afrial_fe

12 bronze badges

1 Comment

Lloyd Over a year ago

Thank you for reply it removes the error but still it doesn't insert the value on the database here is my new code. conn = New OracleConnection("User Id=antonina;Password=antonina;Data Source=xe") da = New OracleDataAdapter() da.InsertCommand = New OracleCommand("INSERT INTO INVENTORY(INVENTORY_PRODUCTNAME,INVENTORY_CAT,INVENTORY_QTY,INVENTORY_PRICE) VALUES ('" & productName & "'," & qty & "," & price & ",'" & cat & "')", conn)

Collectives™ on Stack Overflow

Oracle INSERT on VB.net Null reference exception

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related