ORA-00936: missing expression

Jynx

Active member
Joined
Oct 20, 2007
Messages
44
Programming Experience
Beginner
So below is the code I wrote yesterday. I don't often work in VB and I haven't had to insert a BLOB before. However, the error at least at this moment isn't BLOB specific but syntax. I'm getting the error from Oracle - 'Missing expression' I can't see what's missing but honestly I've been looking at it too long and need some fresh, better skilled eyes.
I tried getting some help on other forums, but no one seems to know what's wrong here.

VB.NET:
Dim fs As New System.IO.FileStream(OpenFileDialog1.FileName, IO.FileMode.Open, IO.FileAccess.Read)
Dim b(fs.Length() - 1) As Byte
fs.Read(b, 0, b.Length)
fs.Close()

Dim query As String = "INSERT INTO mpcs.image_resource (IMAGE, IMAGE_TYPE, IMAGE_NAME, SIZE_BYTES, IR_ID) VALUES (@IMAGE, @TYPE, @NAME, @SIZE, @IRID)"

Using Conn As New OleDbConnection(Private)
    Using CMD As New OleDbCommand()
        With CMD
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = query
            .Parameters.AddWithValue("@IMAGE", OleDbType.Binary).Value = b
            .Parameters.AddWithValue("@TYPE", OleDbType.VarChar).Value = "image/jpg"
            .Parameters.AddWithValue("@NAME", OleDbType.VarChar).Value = Label40.Text
            .Parameters.AddWithValue("@SIZE", OleDbType.Numeric).Value = b.Length
            .Parameters.AddWithValue("@IRID", OleDbType.Numeric).Value = "MPCS.IMAGE_RESOURCE_PK_SEQUENCE.NEXTVAL"
        End With
        Try
            Conn.Open()
            CMD.ExecuteNonQuery()
        Catch ex As OleDbException
            MessageBox.Show(ex.Message.ToString(), "Error Message")
        End Try
    End Using
End Using
 
I don't really use Oracle much but I don't think it supports @ as a parameter prefix. I think that Oracle parameters are usually prefixed with a colon :)).

I had seen that somewhere before earlier, couldn't remember if I tried it or not so I changed everything and the error is - Message = "ORA-01745: invalid host/bind variable name" So I went through and changed all of my variable parameters to something not on the reserved oracle list. Then I received - Message = "ORA-01722: invalid number" which is a conversion type error.

So I figured maybe its one of the OleDbType so I tried mixing and matching those, Number, VarNumber, LongVarChar, just anything that was a number previous but I can't figure out which is causing the problem.
 
I fixed it. For anyone interested.

VB.NET:
            Dim fs As New System.IO.FileStream(OpenFileDialog1.FileName, IO.FileMode.Open, IO.FileAccess.Read)
            Dim b(fs.Length() - 1) As Byte
            fs.Read(b, 0, b.Length)
            fs.Close()

            Dim query As String = "INSERT INTO mpcs.image_resource (IMAGE, IMAGE_TYPE, IMAGE_NAME, SIZE_BYTES, APP_NAME, APP_NAME_TCAT, IR_ID) VALUES (?, ?, ?, ?, 'SHOP_INV', 'IR_APP_NAME', MPCS.IMAGE_RESOURCE_PK_SEQUENCE.NEXTVAL)"

            Using Conn As New OleDbConnection("Private)
                Using CMD As New OleDbCommand()
                    With CMD
                        .Connection = Conn
                        .CommandType = CommandType.Text
                        .CommandText = query
                        .Parameters.AddWithValue("IMAGE", b)
                        .Parameters.AddWithValue("IMAGETYPE", "image/jpg")
                        .Parameters.AddWithValue("IMAGENAME", Label40.Text)
                        .Parameters.AddWithValue("IMAGESIZE", b.Length)
                    End With
                    Try
                        Conn.Open()
                        CMD.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message.ToString(), "Error Message")
                    End Try
                End Using
            End Using
 
Back
Top