simple application

Question

Dear all,

I'm learning VB.Net 2010 for supporting an internal client/server base application. I very soon need support application with operate will Oracle 11g R2 database. We help from BonnieB, I can create a very simple form for with use of BindSource that I can navigate between record of users. I have few questions about the changes

The user table have columns : User_id, user_name, user_title, user_dept, acct_status, mod_user, mod_date

can be refer the thread: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/eef9dc09-0c72-4c23-9b51-23ee03a27e77

Now I add function to add/update record

1) When I add a new row with BindSource bs with bs.AddNew(), I want to initialize the value for acct_status, mod_user, Mod_date. How can refer to the rows added, or I should put value int binding control, for example, me.txtModUser.text = environment.user?

2) for update the dataset to oracle database

Dim connection As New OracleConnection(connection)
   Dim dataAdapter As New OracleDataAdapter()
   Dim dataSet As DataSet = New DataSet

   dataAdapter.SelectCommand = New OracleCommand(queryString, connection)

   Dim cmdBuilder As OracleCommandBuilder = New OracleCommandBuilder(dataAdapter)

   connection.Open()
   dataAdapter.Fill(dataSet, tableName)
      ' Code to modify data in DataSet here 
      ' Without the OracleCommandBuilder this line would fail.
   dataAdapter.Update(dataSet, tableName)
   connection.Close()
Questions:
1) In my previous testing, I dispose the dataAdapter after the DataTable in DataSet ds filled and close the connection. 
In practice, should kept the connection the connection open before I close the form? Do the DataAdapter use for update must be the same dataAdapter I use for fill the dataset?
2) In case DataTable in DataSet is not exactly the same the Tables in Database (For example, I show acct_status as "Active" for A, "Terminated" for T.How update should be done
3) How should I control the concurrent update.
Thanks for all your help!

Answer 1

Hi Netizen!

1) When I add a new row with BindSource bs with bs.AddNew(), I want to initialize the value for acct_status, mod_user, Mod_date. How can refer to the rows added, or I should put value int binding control, for example, me.txtModUser.text = environment.user?

You already know what DataTable your BindingSource is using and you could simply use that DataTable, or to be more generic,  you could cast the BindingSource to a DataTable. Then you'd use the BindingSource.Postion to access the current DataTable row (in this case, the newly added row). I'm not sure of the VB syntax, but here's the C# syntax:

bs.AddNew();
DataTable dt = bs.DataSource as DataTable;
dt.Rows[bs.Position]["acct_status"] = "whatever";

1) In my previous testing, I dispose the dataAdapter after the DataTable in DataSet ds filled and close the connection.

In practice, should kept the connection the connection open before I close the form? Do the DataAdapter use for update must be the same dataAdapter I use for fill the dataset?

No, in practice one should always keep the connection open for as short a time as possible. The DataAdapter does not have to be the same one.

2) In case DataTable in DataSet is not exactly the same the Tables in Database (For example, I show acct_status as "Active" for A, "Terminated" for T.How update should be done

Sorry, I'm not sure what your question is here.

3) How should I control the concurrent update

How to handle concurrent updates often depends on your application. Every application that I've ever written has always worked such that the last one to update the record "wins". That may not always be a viable scenario for some applications. Yes, that means that you might overwrite someone else's changes ... it's a trade-off that didn't matter in the applications I've worked on. So if that's not ok for your app, then someone else might need to take a crack at an answer ... since I've never had to deal with it in the past, I haven't given it any thought in a long time.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 2

Dear all,

Not so successful for my 1st application:

I fill data in Load event in VB form. da and ds is defined as instance properity of the form.

        Try
            cmd = glConnect.gConn.CreateCommand()
            cmd.CommandText = "select * from abc.abcsys_user order by user_id"
            cmd.CommandType = CommandType.Text

            ds = New DataSet("Security")
            daUser = New OracleDataAdapter(cmd)
            daUser.Fill(ds, "User")

            cmd.Dispose()
            daUser.Dispose()

        Catch ex As OracleException
            MsgBox(ex.Code & vbCrLf & "Message: " & ex.Message, _
                   MsgBoxStyle.Critical, "Get Security User")
            Me.Close()
        End Try

I save the record when save button click with below function, the function run but change is not saved. Do I miss something?

   Private Function saveUserRec() As Boolean

        glConnect.gConn.Open()

        glConnect.gSP = glConnect.gConn.BeginTransaction()
        Dim da As New OracleDataAdapter()
        Dim strSQL As String
        Dim lvalid

        strSQL = "Select * from abc.abcsys_user ORDER BY user_id"
        da.SelectCommand = New OracleCommand(strSQL, glConnect.gConn)
        da.SelectCommand.Transaction = glConnect.gSP
        Dim cmdBuilder As OracleCommandBuilder = New OracleCommandBuilder(da)


        lvalid = True
        Try
            da.Update(ds, "user")
            glConnect.gSP.Commit()

        Catch ex As OracleException
            MsgBox(ex.Code & vbCrLf & "Message: " & ex.Message, _
                   MsgBoxStyle.Critical, "Update Security Users")
            glConnect.gSP.Rollback()
            lvalid = False
        Finally
            glConnect.gConn.Close()
        End Try
        Return lvalid
    End Function

Answer 3

Are you certain that there are changes in your DataSet? You might need to do an EndEdit() on your BindingSource ... bs.EndEdit(). 

And be sure that you do NOT AcceptChanges() on your DataSet before you execute the da.Update(). The ds.AcceptChanges() marks all changed rows as unchanged and therefore the da.Update() has no records to update. This is a common mistake that lots of people make.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 4

Dear Bonnie,

It look works now. I added the EndEdit() and AcceptChange() in save record procedure, is it correct place to add it?

       bs.EndEdit()
        glConnect.gConn.Open()

        glConnect.gSP = glConnect.gConn.BeginTransaction()
        Dim da As New OracleDataAdapter()
        Dim strSQL As String
        Dim lvalid

        strSQL = "Select * from abc.abc_user ORDER BY user_id"
        da.SelectCommand = New OracleCommand(strSQL, glConnect.gConn)
        da.SelectCommand.Transaction = glConnect.gSP
        Dim cmdBuilder As OracleCommandBuilder = New OracleCommandBuilder(da)

        lvalid = True
        Try
            da.Update(ds, "user")
            glConnect.gSP.Commit()
            ds.AcceptChanges()

        Catch ex As OracleException
            MsgBox(ex.Code & vbCrLf & "Message: " & ex.Message, _
                   MsgBoxStyle.Critical, "Update Security Users")
            glConnect.gSP.Rollback()
            lvalid = False
        Finally
            glConnect.gConn.Close()
        End Try
        Return lvalid

Answer 5

Yes, the right place for the .EndEdit().

You don't have to bother with the .AcceptChanges() ... I believe that the .Update() takes care of that for you. It doesn't hurt to leave it there where you put it, but I'm pretty sure that it's redundant.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 6

Thanks Bonnie, I just check documentation for Update method, AcceptChanges() is redundant.