Oracle INSERT on VB.net Null 引用异常

Question

请帮忙，我是使用MySQL的oracle数据库的新手，我真的不知道如何调试我的代码。这是我的代码。

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

当我单击我的add按钮时，它说对象引用未设置为对象的实例。我知道我可能只是一个愚蠢的语法错误。请帮助我。

我

确定我所有的变量都有一个值。 我也在使用甲骨文 10g

Answer 1

我从未见过有人使用OracleDataAdapter将任何东西插入数据库，通常OracleDataAdapter用于从数据库中检索数据，即获取 RefCursor 的SELECT ...命令或函数调用。

也许InsertCommand，或者。 UpdateCommand和DeleteCommand用于在从数据库中填充OracleDataAdapter数据后对其进行修改 - 我不知道，我从未使用过它。这些命令使用方法 OracleDataAdapter.Update(da( 调用

无论如何，通常你会这样做：

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()

请注意列的顺序，请参阅您的代码：

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

Answer 2

我假设da是DataAdapter。首先需要通过调用其构造函数来实例化数据适配器。

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)

此外，您在 SQL 语法中的 VALUES 语句后缺少"(("。

其他可能有帮助的事情是您可以将解析语句移动到 try-catch 元素中以避免错误。

希望这有帮助。