Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    5

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

    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
    Last edited by loquin; 01-04-10 at 14:05. Reason: using [code] [/code] tags is highly recommended.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2009
    Posts
    20
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    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
    ...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 & '";'
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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...
    Last edited by loquin; 01-04-10 at 14:06.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •