Link to home
Start Free TrialLog in
Avatar of Rob Rudloff
Rob RudloffFlag for United States of America

asked on

VB.NET return row from SQL to DataSet and get column values ?

Hi.  
I am new to vb.net (but not programming) and have "inherited" an old system from an ex-employee.
I am trying to retrieve a row from a SQL table, and display values from columns in that row.  I was told using a "dataset" is the way to go ... maybe.

I have the following SQL Procedure "GETPK_TRANS_HEADER" :
SELECT * FROM TRANS_HEADER
WHERE TRANS_HEADER_ID = @TransID

Open in new window

I am trying to call the function "GetByID()" which returns a dataset (single record) from that SQL procedure, and display the values from the columns, using something like the following :
Private Function GetTransRecord(MyTransID as Integer)
    '--- GET info from the TRANS_HEADER record ...
    Dim ds = GetByID(MyTransID)

    If ds Is Nothing Then
        Exit Function
    End If

    Dim cust As String = ds.tables(0).Rows(1)("CustomerID").ToString()
    Dim addr as string = ds.tables(0).Rows(1)("Address").ToString()

    '-- show messagebox here

End Function

Open in new window

Public Function GetByID(ByVal liTransID As Integer) As DataSet
     Using sqlConn As New SqlConnection(lsConnectionString)
         Using cmd As New SqlCommand("GETPK_TRANS_HEADER", sqlConn)
             cmd.CommandType = CommandType.StoredProcedure
             cmd.Parameters.Clear()
             cmd.Parameters.AddWithValue("@TransID", liTransID)

             Dim sa As New SqlDataAdapter(cmd)
             Dim ds = New DataSet()
             GetByID = Nothing

             Try
                 sqlConn.Open()
                 sa.Fill(ds)
                 GetByID = ds

             Catch ex As Exception
                 If TypeOf ex Is SqlException Then
                     '-- show a message here
                 Else
                     '-- show a message here
                 End If
             End Try
        End Using
    End Using
End Function

Open in new window

Is that how you use datasets with SQL procedures?  Is there a more proper way to do the calls to SQL procedures?
(Plus, any suggestions on current naming conventions and syntax is appreciated too)

Thanks.
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob Rudloff

ASKER

Thanks.  I'm reading about and trying out all those suggestions now --  This is part of a big VB6 to .NET re-write that we are undertaking.  There are several hundred SQL tables and procedures that will remain untouched, and it looks like we will be using dataReaders, dataSets, and dataViews.