This discussion is archived
3 Replies Latest reply: Nov 15, 2012 9:43 AM by Mark Williams-Oracle RSS

help with vb.net  in an Oracle Database 11g Express Edition Release 11.2.0.

974468 Newbie
Currently Being Moderated
I am having trouble updating a signal row in an Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit

I am using VB.net. I can add a row easily so I know the connection is set properly

Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
Dim da As OracleDataAdapter = New OracleDataAdapter
Dim ds As New DataSet
Dim conn As New OracleConnection("Data Source=<machine name>")
Dim inc, maxrows As Integer



da.SelectCommand = New OracleCommand("Select * from projects Where Projects.ProjectName = " & "'" & ListBox1.Text & "'", conn)
Try
If conn.State = ConnectionState.Closed Then conn.Open()
da.Fill(ds, "UpdateProject")
maxrows = ds.Tables("UpdateProject").Rows.Count
inc = 0
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

maxrows returns 1 row - which is the row I want to update.

I am assuming the "pointer is now sitting at the proper row"
I allow the user to update the description via a text box and the want to simply run a SQL update statement

SQLCmd = "Update projects SET ProjectDescription = " & "'" & TextBox5.Text & "'"
a.SelectCommand = New OracleCommand(SQLCmd, conn)
da.Fill(ds, "Updateproject")
conn.Close()


This code updates EVERY row in the DB.

Any help would be greatly appreciated
  • 1. Re: help with vb.net  in an Oracle Database 11g Express Edition Release 11.2.0.
    636190 Explorer
    Currently Being Moderated
    Nope...no pointer(cursor).
    How about a WHERE clause in that update statement :-)
    r,
    dennis
  • 2. Re: help with vb.net  in an Oracle Database 11g Express Edition Release 11.2.0.
    974468 Newbie
    Currently Being Moderated
    WOW !!!
    I knew it would be simple...
    this is the first time I have worked with Oracle. I just ported my code from SQLServer to Oracle.
    I SQLServer the "cursor... seemed to sit on the right record.."

    Simple addition of the Where clause to reference the projectID worked!!!

    thanks
  • 3. Re: help with vb.net  in an Oracle Database 11g Express Edition Release 11.2.0.
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    971465 wrote:
    I am having trouble updating a signal row in an Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit

    I am using VB.net. I can add a row easily so I know the connection is set properly

    Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider
    Dim da As OracleDataAdapter = New OracleDataAdapter
    Dim ds As New DataSet
    Dim conn As New OracleConnection("Data Source=<machine name>")
    Dim inc, maxrows As Integer



    da.SelectCommand = New OracleCommand("Select * from projects Where Projects.ProjectName = " & "'" & ListBox1.Text & "'", conn)
    Try
    If conn.State = ConnectionState.Closed Then conn.Open()
    da.Fill(ds, "UpdateProject")
    maxrows = ds.Tables("UpdateProject").Rows.Count
    inc = 0
    conn.Close()
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try

    maxrows returns 1 row - which is the row I want to update.

    I am assuming the "pointer is now sitting at the proper row"
    I allow the user to update the description via a text box and the want to simply run a SQL update statement

    SQLCmd = "Update projects SET ProjectDescription = " & "'" & TextBox5.Text & "'"
    a.SelectCommand = New OracleCommand(SQLCmd, conn)
    da.Fill(ds, "Updateproject")
    conn.Close()


    This code updates EVERY row in the DB.

    Any help would be greatly appreciated
    In addition to what Dennis has posted, it might also be worthwhile to do some research on SQL Injection.

    As it stands now the code you posted is open to SQL Injection attacks since you are just gluing text from the textbox into the SQL.

    For example, given your sample code, what if a user types the following into the textbox:
    owned!' --
    What do you think will happen with the code as it currently is and is that what you really want to happen?

    I'm also not quite sure why you are using a Select to perform an Update operation.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points