Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am writing a VB.NET function to update a table with four columns as per below. It is failing every time with the exception ORA-01008: not all variables bound

Can someone please help?


Public Sub UpdateLog(ByVal intUserID As Integer, ByVal strDesc As String, ByVal intLogID As Integer)

Dim oraConn As OracleConnection

    Dim cmd As OracleCommand

    Try
        cmd = New OracleCommand
        cmd.Parameters.Clear()
        cmd.CommandText = "Insert Into Tbl_Trans_Log(UserID,TransID,TransDate,ActionDone) values (:userId,:logId,:transdate,:action)"
        cmd.CommandType = CommandType.Text
        cmd.BindByName = True

        cmd.Parameters.Add(":userId", OracleDbType.Int64).Value = intUserID
        cmd.Parameters.Add(":logId", OracleDbType.Int64).Value = intLogID
        cmd.Parameters.Add(":transdate", OracleDbType.Date).Value = DateTime.Now
        cmd.Parameters.Add(":action", OracleDbType.Varchar2).Value = strDesc

        oraConn = New OracleConnection(ConfigurationManager.ConnectionStrings("connString").ConnectionString)
        oraConn.Open()

        cmd.Connection = oraConn
        cmd.ExecuteNonQuery()



    Catch ex As Exception
        sWriteErrorLog("C:\", "log - sUpdateTransLog -> " & ex.Message)
    Finally
        oraConn.close()
    End Try

End Sub


What I have tried:

Adding semicolons at the end of command text
Posted
Updated 20-Mar-18 20:16pm
Comments
Maciej Los 19-Mar-18 13:02pm    
Try to add space between [,] and [:].

Try to add spaces between field names and parameter names.
VB.NET
cmd.CommandText = "Insert Into Tbl_Trans_Log(UserID, TransID, TransDate, ActionDone) values(:userId, :logId, :transdate, :action)"
 
Share this answer
 
Comments
Member 13734676 21-Mar-18 10:35am    
Hi Maciej will the below code work? because it as sysdate which gives the current date while updating the row.

cmd.CommandText = "Update Tbl_Active_User set LoginDate=sysdate where Username=:username"

or should i add a parameter for the logindate as Datetime.Now
The below code wroked for me.

cmd = New OracleCommand


            cmd.Parameters.Clear()
            cmd.CommandText = "Insert Into Tbl_Trans_Log(UserID,TransID,TransDate,ActionDone) values (:userId,:logId,:transdate,:action)"
            cmd.CommandType = CommandType.Text
            cmd.BindByName = True

            cmd.Parameters.Add(New OracleParameter(":userId", OracleDbType.Int64)).Value = intUserID
            cmd.Parameters.Add(New OracleParameter(":logId", OracleDbType.Int64)).Value = intLogID
            cmd.Parameters.Add(New OracleParameter(":transdate", OracleDbType.Date)).Value = DateTime.Now
            cmd.Parameters.Add(New OracleParameter(":action", OracleDbType.Varchar2)).Value = strDesc

            oraConn = New OracleConnection(ConfigurationManager.ConnectionStrings("xxxxxxxxx").ConnectionString)
            oraConn.Open()

            cmd.Connection = oraConn
            cmd.ExecuteNonQuery()
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900