Datasets vs Recordsets.

[Greg]

I have a function I am currently using in a MS Access ADP Project connected
to SQL Server 2005. I would like to get some ideas on how I can create
routines that function in much the same manner, except in VB.Net using
ADO.Net and DataSets.

The function I would like assistance with is a function named fSave. This
function takes the following parameters.

- frm as Form (Current Form)
- strStoredProcedure as String (Stored Procedure Name to use)
- lngRecordID as Long (Primary Key for the current recordset)

When the fSave routine is called the user either passes a valid lngRecordID
to update an existing record or a ZERO to add a new record from the frm
(form) passed. I’ve included a copy of my procedure (cut down quite a bit) to
show what I am doing. You can see I am looping through all the controls on
the given form and then looping through all the field names in the recordset.
When the two match, the Recordset.FieldName.Value is updated with the
frm.Control.Value.

I’d like to create similar logic by looping through all the controls for the
passed form and then looping through all the fields in a DataSet or DataTable
and when there is a match, update the DataSet’s field name value with what is
represented on the form. When all fields have been processed I would like to
save the DataSet. Something to keep in mind here and that is when saving a
record, only ONE record is present in the DataSet or DataTable at a given
time.

When all fields have been processed the record is saved and the RecordID
(PrimaryKey) is returned as a result.

1. How can I step through each control on a passed form (frm as Form)?
2. How can I step through each field name in a DataSet?
3. How can I access some of the specific properties for each field within
thee DataSet, such as field type, size, and other related information to a
field?
4. As each DataSet field name and Form control name are matched together, I
would like to apply the update to the DataSet to be saved later when all the
fields have been processed.

If it would be of assistance for me to post my entire module which includes
the entire fSave module, I can certainly do so if need be. But, if anyone can
get me going by showing me what’ve I’ve requested above, that should be
enough for me to replicate my existing function.

Actual Function Below

Function fSave(frm As Form, _
strStoredProc As String, _
strPrimaryKey As String, _
lngRecordID As Long) As Long

Set rst = New ADODB.Recordset

If (lngRecordID > 0) Then
strAddOrEditBefore = "Update"
Else
strAddOrEditBefore = "Add"
End If

If (blnMessage) Then
intReturnVal = MsgBox("You are about to " & strAddOrEditBefore & " " &
strCaption & "." & vbCrLf & vbCrLf & _
"Do you want to continue?", _
vbYesNo + vbDefaultButton1 + vbQuestion, "PFCWin "
& strAddOrEditBefore & " " & strCaption)
Else
intReturnVal = vbYes ' Save record by default when no message is
displayed.
End If

If (intReturnVal = vbYes) Then
If (fCheckForNulls(frm)) Then ' Displays a message box
if any REQUIRED fields on the form are not
' filled in. I use the
TAG property to identify a field as REQUIRED.

rst.Open strStoredProc & " " & Nz(lngRecordID),
CurrentProject.Connection, adOpenStatic, adLockOptimistic
strMessage = "You have entered too many characters into the following
field(s). Check the maximum number of " & vbCrLf & _
"characters allowed in the (x) and enter your information
again. No data entered has been lost." & vbCrLf
If (rst.BOF And rst.EOF) Then '
No records returned, so "Add" a new record.
rst.AddNew ' Create a new record.
intEditMode = ADDMODE
frm.sysCreateDate = Now()
Else
intEditMode = EDITMODE
End If
' Loop through all recordset fields and form controls.
For Each rstField In rst.Fields
For Each ctl In frm.Controls
If (ctl.name = rstField.name And _
strPrimaryKey <> rstField.name) Then '
Control matches field in table. (Skipping the Primary Key)
If (rstField.Type = adNumeric) Or (rstField.Type = adDecimal) Then
If (Not IsNull(ctl.Value) And ctl.Value <> "") Then
strNow = CDbl(Nz(ctl.Value)) ' Changed from
CCur to CDbl JMM 07-31-2007
Else
strNow = 0
End If
strWas = CDbl(Nz(rstField.Value))
Else
strNow = Nz(ctl.Value)
If (intEditMode = ADDMODE) Then
strWas = vbNullString
Else
strWas = Nz(rstField.Value)
End If
End If
' SQL Update: strWas and strNow
If ((Nz(strWas) <> Nz(strNow))) Then
fSave = True
' Record was actually updated.
ctl.Value = fChangeCase(Nz(ctl.Value))
' Update to Case Settings from tblSystemDefault
' If a NEW Record is being ADDED and No Value has been
added to a Control, there
' is no need to record that "blank" entry in the Edit Log.
Therefore, skip it.
If (intEditMode = ADDMODE And Not IsNull(strNow)) Or _
(Trim(strNow) <> Trim(strWas)) Then
' Do not Record transactions for "sys" fields. Include
changes for "frmSystemDefault" form though.
If (Left(ctl.name, 3) <> "sys" Or _
frm.name = "frmSystemDefault") Then
' Writes an edit log of the field transaction.
If (intEditMode = EDITMODE) Then
Call sWriteRecordLog(frm.name, _
rstField.Properties(1), _
rstField.name, _
"'" & rstField.Value & "'", _
"'" & ctl.Value & "'", _
lngRecordID, _
intEditMode)
' Write Edit to tblRecordLog
Else
' The following procedure cannot pass the
rstField.Value when a NEW record is being added
' as it results in an error. Therefore, when NEW
records are being ADDED, a null value is
' just passed since that will be the value anyways.
' GJS 09-01-2006
Call sWriteRecordLog(frm.name, _
rstField.Properties(1), _
rstField.name, _
"""", _
"'" & ctl.Value & "'", _
lngRecordID, _
intEditMode)
' Write Edit to tblRecordLog
End If
End If
If (IsNull(ctl.Value) Or ctl.Value = "") Then
rstField.Value = Null
Else
Select Case rstField.Type
' JMM 10-05-2007
Case adNumeric, adDecimal, adDouble
rstField.Value = CDbl(ctl.Value)
Case Else
rstField.Value = ctl.Value
' Write value to field in recordset/table.
End Select
End If
End If
End If
End If
Next
Next

If (fSave) Then
rst.Update ' Save the current record.
fSave = rst.Fields(0) ' Primary Key for the table is
ALWAYS the first field.
End If
If (fSave > 0) Then ' A Record has been either Added, or Saved, so
inform the user of that.
If (blnConfirm) Then ' Should a confirmation message be displayed?
If (IsNull(strCaption) Or strCaption = "") Then
MsgBox "The [" & frm.Tag & "] has been successfully " &
strAddOrEditAfter & ".", vbOKOnly + vbInformation, "PFCWin [" & frm.Tag & "]
Saved"
Else
MsgBox strCaption & " has been successfully " &
strAddOrEditAfter & ".", vbOKOnly + vbInformation, "PFCWin " &
strAddOrEditAfter & " " & strCaption
End If
End If
Else
' No Action from a bad update is required at this point.
End If
Else
' The following messages will appear if there was an error while
saving the record.
If (IsNull(strCaption) Or strCaption = "") Then
MsgBox "The [" & frm.Tag & "] was not " & strAddOrEditAfter & ".",
vbOKOnly + vbInformation, "PFCWin [" & frm.Tag & "] Not " & strAddOrEditAfter
Else
MsgBox strCaption & " was not " & strAddOrEditAfter & ".", vbOKOnly
+ vbInformation, "PFCWin " & strCaption & " " & strAddOrEditAfter
End If
fSave = 0
End If

fSave_Exit:
If (rst.State = adStateOpen) Then
rst.Close
End If
Set rst = Nothing

DoCmd.Hourglass False
Exit Function
End Function

Thanks in advance to giving me feedback and or a solution to my problem of
replicating this same logic into Vb.Net 200.

Thanks a ton.

 

[Cor Ligthert [MVP]]

Greg,

First start with not comparing a RecordSet to a DataSet

A DataSet is a wrapper around DataTables. They did not do it, but in the
same case they could have made a wrapper around a Recordset.

Start comparing your recordSet with a DataTable, then sudenly all becomes
much more clear.

Cor