Inserting a row into Oracle via ODBC - very simple example

[kriswork]

Hi All,

Just for a change I thought I post an answer not a question.

All I wanted to do was insert a record into an Oracle database. I'd already got the ODBC connection string which took several days to sort a while back.
All the examples I found to do this seemed really complex with big chunks of code setting up parameters and having to specify the type of each table column.
Here's my code to insert a single row into an Oracle 10g database:

        Dim Connection As New Odbc.OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=oracle_server.diamond.ac.uk/ORACLE;Uid=username;Pwd=password;")
        Dim SQL_Command As New Odbc.OdbcCommand("insert into audit_log(log_message) values('Hello from CD Apps')", Connection)

        Try
            Connection.Open()
            SQL_Command.ExecuteNonQuery()
            Connection.Close()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        End Try

I could not find an example that did not set up all the parameters as objects.
I'm not good with Oracle, but note there is no ';' at the end of the insert statement - if you put one in, you get an ORA-00911 error, but in Oracle SQL Developer you get a different error if you miss it out!

Hopefully this code will be useful to someone.

Kristian

[Joacim Andersson]

Moved to the CodeBank forum.