Convert simple ADODB routine from VBA to VB.net

[UsenetUser]

This is so easy in VBA, but every source I research to do it in vb.net
seems to have a more difficult and convoluted method involving
datasets, tableadapters, etc. etc. What's the easiest way to open a
recordset and pull over one record like this with vb.net?

Public Function fnGetName(IDS As Integer) As String
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strName As String
'On Error GoTo procError

strSQL = "SELECT * FROM tblPatient WHERE PatientIDS = " & IDS
Set rs = New ADODB.Recordset
With rs
.Open strSQL, CurrentProject.Connection, adOpenForwardOnly
If .EOF And .BOF Then
'
Else
strName = !Lname
If Not IsNull(!fname) Then strName = strName & ", " & !fname
End If
.Close
End With
fnGetName = strName
End Function

 

[John Bundy]

That is not 'easy', you might be used to it, but is is a poor and clunky way
to do things. I don't intend to give a plug, but I had similar issues finding
a GOOD source for using tableadapters so i made one, once you see the method
in action you will never again go back to the 'easy' way. Look at the section
on Datasets and tableadapters.
http://www.jmbundy.blogspot.com/

 

[Stephany Young]

Public Function fnGetName(IDS As Integer) As String

Using _commmand = New OleDbCommand("SELECT * FROM tblPatient WHERE
PatientIDS = ?", CurrentProject.Connection)
_commmand.Parameters.AddWithValue("p1", IDS)
Using _reader = _com.ExecuteReader()
If _reader.HasRows
_reader.Read()
Dim _fname = _reader.GetOrdinal("fname")
Return _reader.GetString(_reader.GetOrdinal("Lname")) &
If(_reader.IsDbNull(_fname), String.Empty, ", " & _reader.GetString(_fname))
End If
End Using
End Using

Return Nothing

End Function

 

[UsenetUser]

Thanks, Stephany. Saved me a lot of time.

....reader.GetOrdinal()... ? Who woulda guessed? ;-)

 

[UsenetUser]

Not easy?? You don't find that easy? And why do you say it is poor and
clunky? And why is easy in quotes? Please explain exactly how a
tableadapter is easier, better, and less (or not at all) clunky than
the bit of VBA code.