如何使用VB.NET中的数据读取器从SQL服务器表中获取列内的数据 [英] How do I get the data inside a column from a SQL server table using data reader in VB.NET

问题描述

我正在尝试从Table_DTR表中的[MorningTime-In]，[AfternoonTime-In]列中获取数据。它的数据类型是时间（7），但我继续收到错误消息：System.IndexOutOfRangeExcemption：[MorningTime-In]。



我尝试过：

I am Trying to Get the Data from the column [MorningTime-In],[AfternoonTime-In] in the table Table_DTR . its Datatype is time(7) but I keep on getting an Error message: System.IndexOutOfRangeExcemption:[MorningTime-In] .

What I have tried:

Dim timeInAm As DateTime
Dim timeOutAm As Date
Dim timeInPm As Date
Dim timeOutPm As Date


Dim xAm As Date = Date.Parse("08:00")
Dim yAm As Date = Date.Parse("12:00")
Dim xPm As Date = Date.Parse("01:00")
Dim yPm As Date = Date.Parse("05:00")
Try
    Connect = New SqlConnection(ConnectionString)
    Connect.Open()

    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 * 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()
            Do While Reader.Read()
                timeInAm = Reader("MorningTime-In").ToString()
                timeOutAm = Reader("MorningTime-Out").ToString()
                timeInPm = Reader("AfternoonTime-In").ToString()
                timeOutPm = Reader("AfternoonTime-Out").ToString()



            Loop


        End Using
    End Using

推荐答案

尖括号[]在SQL Server中用于包含SQL Server中包含特殊字符或保留字的名称。但是当列返回到客户端时，将使用列的实际名称。



请尝试以下操作：

The angle brackets, [], are used in SQL Server to surround names in SQL Server that contains special characters or are reserved words. However when the column is returned to the client, the actual name of the column is used.

So try the following:

timeInAm = Reader("MorningTime-In").ToString()

此外，它看起来像是在字符串变量中存储时间。如果是这种情况，我建议使用系统。日期时间 [ ^ ]使格式化，计算，转换等变得更加容易

Also it looks like you're storing a time in a string variable. If that's the case, I would recommend using System.Datetime[^] which makes formatting, calculations, conversions, and so on much easier

这篇关于如何使用VB.NET中的数据读取器从SQL服务器表中获取列内的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！