Using Visual Basic to add, delete, and update records in Oracle...

sarahmiller · #1 (**permalink**) 12-08-09, 18:25

I'm trying to create buttons on a visual basic database application that will add, delete, and update a record in Oracle using info typed into textboxes by the user. I can't get the buttons working. It seems to have a problem with the "cmd.ExecuteNonQuery()" and the error reads: ORA-00933: SQL command not properly ended. Can anyone tell me what I'm doing wrong?

Code:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim XE As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=" + txtHost.Text + ")(PORT=1521)))" _
                    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _
                    + "User Id=system;Password=********;"
        Dim conn As New OracleConnection(XE)

        conn.Open()

        Dim cmd As New OracleCommand

        cmd.Connection = conn
        cmd.CommandText = "Insert Employee_ID, First_Name, Last_Name, EMail, Phone_Number, Hire_Date, Job_ID, Salary, Commission_Pct, Manager_ID, Department_ID INTO hr.Employees WHERE Employee_ID = " + txtEmployeeID.Text
        cmd.CommandType = CommandType.Text

        cmd.ExecuteNonQuery()

        cmd.Dispose()
        conn.Dispose()
    End Sub




Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim XE As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=" + txtHost.Text + ")(PORT=1521)))" _
                    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _
                    + "User Id=system;Password=********;"
        Dim conn As New OracleConnection(XE)

        conn.Open()

        Dim cmd As New OracleCommand

        cmd.Connection = conn
        cmd.CommandText = "Delete Employee_ID, First_Name, Last_Name, EMail, Phone_Number, Hire_Date, Job_ID, Salary, Commission_Pct, Manager_ID, Department_ID FROM hr.Employees WHERE Employee_ID = " + txtEmployeeID.Text
        cmd.CommandType = CommandType.Text

        cmd.ExecuteNonQuery()

        cmd.Dispose()
        conn.Dispose()
    End Sub




Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim XE As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=" + txtHost.Text + ")(PORT=1521)))" _
                    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));" _
                    + "User Id=system;Password=********;"
        Dim conn As New OracleConnection(XE)

        conn.Open()

        Dim cmd As New OracleCommand

        cmd.Connection = conn
        cmd.CommandText = "Update Employee_ID, First_Name, Last_Name, EMail, Phone_Number, Hire_Date, Job_ID, Salary, Commission_Pct, Manager_ID, Department_ID FROM hr.Employees WHERE Employee_ID = " + txtEmployeeID.Text
        cmd.CommandType = CommandType.Text

        cmd.ExecuteNonQuery()

        cmd.Dispose()
        conn.Dispose()
    End Sub

healdem · #2 (**permalink**) 12-09-09, 01:36

and the values you are attempting to update come from where
normally I'd expect an update statement to be similar to

Code:

UPDATE MyTable
SET aNumericColumn=999, aTextColumn="aTextValue"
WHERE SomeOtherTextColumn= "whoops" OR SomeOtherNumericColumn=1234;

I did wonder at first if you were missing a semi colon at the end of the SQL statement.

cianadalton · #3 (**permalink**) 12-09-09, 02:04

Thanks for the reply. This code format will not work in Visual Basic because I'm trying to update an existing record where the updated data has been entered into textboxes.

healdem · #4 (**permalink**) 12-09-09, 02:51

?
the code you are trying will not work as its not valid SQL, as far as I can decypher it you don't actaully supply any values to update.

so its a Mexican standoff
Im just curious to know however why you believe

Quote:

...This code format will not work in Visual Basic because I'm trying to update an existing record where the updated data has been entered into textboxes.

after all the sample given was an indication of how to use the update statement. if you want to use values from a text box then you replace the hard coded values with the control name

eg

Code:

strSQL = 'UPDATE MyTable  ' & _
SET ThisTextColumn = "' & ThisControl & '", ThatNumericColumn = ' & ThatControl.value & _ 
'" where SomeTextColumn = "' SomeOtherControl.text & '";'

loquin · #5 (**permalink**) 01-04-10, 12:38

You also don't mention which VERSION of VB you are using. At first glance, it appears to be VB6.

With ADO, there IS no ExecuteNonQuery method. This method is only available with ADO.Net. You would use the cmd.Execute method, or, if you're not using parameterized queries, drop the command object and use the connection object's Execute method directly.

If you wish to use parameterized queries (to avoid SQL Injection issues,) then, you need to create one or more parameters for the command object, and assign their values prior to calling the command's execute method.

I can recommend Nick Snowdon's Oracle Programming with Visual Basic (1999), Sybex, ISBN 0-7821-2322-8

Also, note that with VB5/6, you should use the string concatenation operator (&) rather than the addition operator(+,) as the addition operator sometimes has unintended effects when used to concatenate strings...

Internet Services

Web Development

Digital Marketing

Consumer Tech