Rob Rudloff
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" :
(Plus, any suggestions on current naming conventions and syntax is appreciated too)
Thanks.
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER