3 Replies Latest reply: Nov 15, 2012 11:43 AM by Mark Williams-Oracle RSS

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

    974468
      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
          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
            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
              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.