i get error ORA-01747-invalid user.table.column,table.column, or column specific

Question

i get error ORA-01747-invalid user.table.column,table.column, or column specification.

i got all other forms working. This is a Edit form using Visual basic on visual studio 2015. program retrieving information from Oracle SQL developter
. here is code from form. erro when using btnSaveEdit form. The form retrieves the info first but after i edit when i want to save i get the error.

Imports Oracle.DataAccess.Client

Public Class frmEdit

    Dim oradb As String = "Data Source=(DESCRIPTION=" _
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=csnet.southtexascollege.edu)(PORT=1521))" _
                    + "(CONNECT_DATA=(SERVICE_NAME=orclcitp)));" _
                    + "User Id=maldj;Password=R8mald;"

    Dim conn As New OracleConnection(oradb)
    Dim strEditDIAMOND As String

    Private Sub btnMainMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMainMenu.Click
        frmMainMenu.Show()
        Me.Close()

    End Sub

    Private Sub btnSearchBracelets_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchBracelets.Click

        strEditDIAMOND = InputBox("Please enter Bracelet's DIAMOND Description", "Search P_BRACELETS to edit")

        conn.Open()
        Dim sql1 As String = "SELECT Diamond, Gold, Silver, Price FROM P_BRACELETS WHERE Diamond = '" & strEditDIAMOND & "' "
        Dim cmd1 As New OracleCommand(sql1, conn)
        cmd1.CommandText = sql1
        cmd1.CommandType = CommandType.Text
        Dim dr As OracleDataReader = cmd1.ExecuteReader()

        While dr.Read()
            If Not dr.GetValue(0) Is DBNull.Value Then _
                txtDiamond.Text = dr.GetString(0)
            txtGold.Text = dr.GetString(1)
            txtSilver.Text = dr.GetString(2)
            txtPrice.Text = CStr(dr.GetDecimal(3))
        End While

        txtDiamond.Enabled = True
        txtGold.Enabled = True
        txtSilver.Enabled = True
        txtPrice.Enabled = True

        conn.Close()
        txtDiamond.Focus()
    End Sub

    Private Sub btnSaveEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveEdit.Click
        Dim strDiamond As String = ""
        Dim strGold As String = ""
        Dim strSilver As String = ""
        Dim decPrice As Decimal = 0.0

        Try
            strDiamond = CStr(txtDiamond.Text)
            strGold = CStr(txtGold.Text)
            strSilver = CStr(txtSilver.Text)
            decPrice = CDec(txtPrice.Text)

            Dim sql As String = "UPDATE P_BRACELETS SET Diamond = :DIAMOND, Gold = :GOLD, Silver = :SILVER, " &
                                                                   "Price = :PRICE, " &
                                                                 "WHERE Diamond = :EditDIAMOND"

            Dim cmd2 As New OracleCommand(sql, conn)
            conn.Open()
            cmd2.Parameters.Add("DIAMOND", strDiamond)
            cmd2.Parameters.Add("GOLD", strGold)
            cmd2.Parameters.Add("PRICE", decPrice)
            cmd2.CommandType = CommandType.Text
            cmd2.ExecuteNonQuery()

            txtDiamond.Text = strEditDIAMOND
            txtGold.Text = ""
            txtSilver.Text = ""
            txtPrice.Text = ""

            txtDiamond.Enabled = False
            txtGold.Enabled = False
            txtSilver.Enabled = False
            txtPrice.Enabled = False

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
            txtDiamond.Focus()
        End Try
    End Sub

    Private Sub frmEdit_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class

Explanation / Answer

The Oracle generate an ORA-0147 error when you have specified a column name improperly in the SQL query. So inorder to rectify this, you have to confirm the columns used by you in the query, ie, the columns with the names Diamond, Gold, and Silver are present in the table called P_BRACELETS.

Note: Double check for spelling in the table names in DB against what you used in the query.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.