Ralph Ralph - 1 month ago 5
Vb.net Question

How can I read a specific column in a database?

I hava a Table named DTR_Table it has 8 columns in it namely:
EmployeeID,Date,MorningTime-In,MorningTime-Out,AfternoonTime-In,AfternoonTime-Out,UnderTime,Time-Rendered.I want to read the column "AfternoonTime-In".
Following is my code. It reads my "AfternoonTime-In" field, but it keeps on displaying "Has Rows" even if there is nothing in that column.

How can I fix this?

Connect = New SqlConnection(ConnectionString)
Connect.Open()

Dim Query1 As String = "Select [AfternoonTime-Out] From Table_DTR Where Date = @Date and EmployeeID = @EmpID "
Dim cmd1 As SqlCommand = New SqlCommand(Query1, Connect)
cmd1.Parameters.AddWithValue("@Date", DTRform.datetoday.Text)
cmd1.Parameters.AddWithValue("@EmpID", DTRform.DTRempID.Text)

Using Reader As SqlDataReader = cmd1.ExecuteReader()
If Reader.HasRows Then
MsgBox("Has rows")
Reader.Close()

Else
MsgBox("empty")
End If
End Using`

Answer

After returning the DataReader you need to start reading from it if you want to extract values from your query.

Dim dt = Convert.ToDateTime(DTRform.datetoday.Text)
Dim id = Convert.ToInt32(DTRform.DTRempID.Text)

Using Connect = New SqlConnection(ConnectionString)
    Connect.Open()
    Dim Query1 As String = "Select  [AfternoonTime-Out] From Table_DTR 
                           Where Date = @Date and EmployeeID = @EmpID"
    Dim cmd1 As SqlCommand = New SqlCommand(Query1, Connect)
    cmd1.Parameters.Add("@Date", SqlDbType.DateTime).Value = dt
    cmd1.Parameters.Add("@EmpID", SqlDbType.Int).Value = id
    Using Reader As SqlDataReader = cmd1.ExecuteReader()
       While Reader.Read()
          MessageBox.Show(Reader("AfternoonTime-Out").ToString())
       Loop
    End Using
End Using

Note that I have changed the AddWithValue with a more precise Add specifying the parameter type. Otherwise, your code will be in the hand of whatever conversion rules the database engine decides to use to transform the string passed to AddWithValue to a DateTime.
It is quite common for this conversion to produce invalid values especially with dates

Comments