Link to home
Start Free TrialLog in
Avatar of running32
running32

asked on

Retrieve values from a sql statement

How can it get the values in the select statement and assign them to variables?   Thank you

Dim program, boxnum, dtmdestroydate
                Dim cmdins As New SqlClient.SqlCommand("select program, boxnum, dtmdestroydate from tblrecordsmanagment where lngPatientid = '" & lngpatientid & "'", Connection1)
                Try
                    program = cmdins.ExecuteScalar(0).ToString
                    boxnum = cmdins.ExecuteScalar(1).ToString
                    dtmdestroydate = cmdins.ExecuteScalar(2).ToString
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
ASKER CERTIFIED SOLUTION
Avatar of razorback041
razorback041
Flag of United States of America 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
Avatar of maidinhtai
maidinhtai
Because the select statement return 1 row or 0 row. So that here is my code:
                Dim cmdins As New SqlClient.SqlCommand("select program, boxnum, dtmdestroydate from tblrecordsmanagment where lngPatientid = '" & lngpatientid & "'", Connection1)
                If Connection1.State = ConnectionState.Closed then Connection1.Open()
                Dim sqlReader As SqlClient.SqlDataReader = cmdins.ExecuteReader()
                If sqlReader.Read then
                    program = sqlReader("program")
                    boxnum= sqlReader("boxnum")
                    dtmdestroydate = sqlReader("dtmdestroydate")
                End If
                sqlreader.Close()
                Connection1.close()