[2005] Need help with dataset concept

[Pasvorto]

OK, be nice. I am really having an issue with data updating in ADO.NET. I having been reading Visual Basic 2005 Programmer's Reference; Visual Basic 2005 Cookbook; and Visual Basic 2005 Jumpstart. I have been Googling my arse off. I have been searching this forum. But I still don't get it.

I have a handle on the INSERT and the DELETE in ADO.NET

In VB6 I would do this.

Connect to db at start of program, disconect on exit.

Here is a sample: It does about all the Add/Update code that I would run across.

'IF WE MADE IT HERE, ALL IS OK SO WE CAN APPEND
            '==============================
            rs1 = New ADODB.Recordset
            '===============================
            sql = "SELECT * FROM DATACOLLECTION WHERE [BOGUSDATE] = " & "'" & pdate.Date & "'"
            rs1.Open(sql, Cnxn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
            If Not rs1.BOF And Not rs1.EOF Then
                rs1.MoveFirst()
                Do
                    '=============================
                    rsTC = New ADODB.Recordset
                    '=============================
                    If rs1.Fields("shift").Value <> 3 Then
                        sql1 = "SELECT * FROM TIMECARDS WHERE [PAYDATE] = " & "'" & pdate.Date & "'"
                        sql1 = sql1 & " AND [EMPNUM] = " & rs1.Fields("EMPLOYEEID").Value
                    Else
                        sql1 = "SELECT * FROM TIMECARDS WHERE [PAYDATE] = " & "'" & System.DateTime.FromOADate(pdate.ToOADate + 1) & "'"
                        sql1 = sql1 & " AND [EMPNUM] = " & rs1.Fields("EMPLOYEEID").Value
                    End If
                    rsTC.Open(sql1, Cnxn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
                    If Not rsTC.BOF And Not rsTC.EOF Then
                        rsTC.MoveFirst()
                        rsTC.Fields("SHIFTDATE").Value = rs1.Fields("bogusdate").Value
                        rsTC.Fields("PAYDATE").Value = rs1.Fields("PAYDATE").Value
                        rsTC.Fields("PAYCODE").Value = "R"
                        rsTC.Fields("dept").Value = rs1.Fields("DEPT1").Value & ""
                        If IsDBNull(rs1.Fields("DTIME1").Value) Then
                            rsTC.Fields("hours").Value = 0
                        Else
                            rsTC.Fields("hours").Value = rs1.Fields("DTIME1").Value
                        End If
                        rsTC.Fields("dept2").Value = rs1.Fields("dept2").Value & ""
                        If IsDBNull(rs1.Fields("DTIME2").Value) Then
                            rsTC.Fields("HOURS2").Value = 0
                        Else
                            rsTC.Fields("HOURS2").Value = rs1.Fields("DTIME2").Value
                        End If
                        rsTC.Fields("DEPT3").Value = rs1.Fields("DEPT3").Value & ""
                        If IsDBNull(rs1.Fields("DTIME3").Value) Then
                            rsTC.Fields("HOURS3").Value = 0
                        Else
                            rsTC.Fields("HOURS3").Value = rs1.Fields("DTIME3").Value
                        End If
                    Else
                        rsTC.AddNew()
                        rsTC.Fields("empnum").Value = rs1.Fields("EMPLOYEEID").Value
                        rsTC.Fields("SHIFTDATE").Value = rs1.Fields("bogusdate").Value
                        rsTC.Fields("PAYDATE").Value = rs1.Fields("PAYDATE").Value
                        rsTC.Fields("PAYCODE").Value = "R"
                        rsTC.Fields("dept").Value = rs1.Fields("DEPT1").Value & ""
                        If IsDBNull(rs1.Fields("DTIME1").Value) Then
                            rsTC.Fields("hours").Value = 0
                        Else
                            rsTC.Fields("hours").Value = rs1.Fields("DTIME1").Value
                        End If
                        rsTC.Fields("dept2").Value = rs1.Fields("dept2").Value & ""
                        If IsDBNull(rs1.Fields("DTIME2").Value) Then
                            rsTC.Fields("HOURS2").Value = 0
                        Else
                            rsTC.Fields("HOURS2").Value = rs1.Fields("DTIME2").Value
                        End If
                        rsTC.Fields("DEPT3").Value = rs1.Fields("DEPT3").Value & ""
                        If IsDBNull(rs1.Fields("DTIME3").Value) Then
                            rsTC.Fields("HOURS3").Value = 0
                        Else
                            rsTC.Fields("HOURS3").Value = rs1.Fields("DTIME3").Value
                        End If
                        'GO TO EMPLOYEE FILE AND GET DATA
                        '===================================
                        rsEmployee = New ADODB.Recordset
                        '===================================
                        sql2 = "SELECT * FROM EMPFILE WHERE [EMPNUM] = " & rs1.Fields("EMPLOYEEID").Value
                        rsEmployee.Open(sql2, CnxnTechniSQL, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
                        If Not rsEmployee.BOF And Not rsEmployee.EOF Then
                            rsTC.Fields("shift").Value = rsEmployee.Fields("shift").Value
                            rsTC.Fields("WAGE").Value = rsEmployee.Fields("WAGE").Value
                            rsTC.Fields("JOBWAGE").Value = rsEmployee.Fields("JOBWAGE").Value
                        End If
                        rsEmployee.Close()
                        rsEmployee = Nothing
                        '=======================
                    End If
                    rsTC.Update()
                    rsTC.Close()
                    rsTC = Nothing
                    '=================
                    rs1.MoveNext()
                Loop While Not rs1.EOF

If someone can provide code that performs the same functionality, I should be make all the necessary coding changes to use it as a template. For some reason, it is just not getting past my thick skull.

[Pasvorto]

I think I may have stumbled across soemthing. I will try it tomorrow.

[jmcilhinney]

As tempting as it is to translate that code (NOT) updating data is VERY simple with ADO.NET. You need one Connection, one DataAdapter, one DataTable and four Commands.

The DataAdapter has four properties (SelectCommand, DeleteCommand, InsertCommnad and UpdateCommand) to which the four Commands are assigned. Note that you only need the ones you're going to use, e.g. if you're only going to retrieve and update data then you only need the SelectCommand and the UpdateCommand.

Each Command has a Connection property, to which you assign the Connection object.

When you call the Fill method of the DataAdapter it executes the SelectCommand and populates the specified DataTable.

When you call the Update method of the DataAdapter it checks the specified DataTable for changes. If it finds any deleted rows it executes the DeleteCommand, if it finds any new rows it executes the InsertCommand and if it finds any modified rows it executes the UpdateCommand.

Each Command has an SQL statement associated with it through its CommandText property. This could be SQL code or the name of a stored procedure. If that SQL code contains any parameters then you must add to the Command's Parameters collection for each one.

Now, VS 2005 has additional data access features including Data Sources and TableAdapters, but they are just a specialisation of what I've just described. I suggest that you play with code to begin with and use the appropriate types in a simple scenario. Once you understand the concepts, then I'd suggest moving on to the IDE features so that you know what's going on behind the scenes.