We help IT Professionals succeed at work.

what am i doing wrong here - date from database asp.net

here is my vb code
Imports System.Data.SqlClient

Partial Class main
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim ConnectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("dbconn").ToString()
        Dim dateval As DateTime = Now.Date.ToString
        Dim MyConn As New System.Data.SqlClient.SqlConnection(ConnectionString)
        Dim MyComm As New System.Data.SqlClient.SqlCommand("SELECT * FROM [dbo].[doctime] WHERE date= " + "'" + dateval + "'", MyConn)

        MyConn.Open()
        Dim DataReader As System.Data.SqlClient.SqlDataReader = MyComm.ExecuteReader()
        'Dim format As String = "MM/DD/YY"
        While (DataReader.Read())

            date_label.Text = DataReader("Date")
            'box1_lbdate_1.Text = dateval.ToString
            Doc1name.Text = DataReader("Doc1_Name").ToString
            Doc1time.Text = DataReader("doc1_time").ToString
            Doc2name.Text = DataReader("doc2_name").ToString
            Doc2time.Text = DataReader("doc2_time").ToString

            If String.IsNullOrWhiteSpace(Doc2name.Text) Then
                Doc2name.Visible = False
                Doc2time.Visible = False
            End If


        End While
        DataReader.Close()
        MyConn.Close()


    End Sub
End Class

I keep getting this error
[embed=file 900763]

Open in new window


my database has the following fields
table name is doctime

then it has a primary key of id
then date as type text
then doc1name as type text
doc1time as type text
doc2name as type text
doc2time as type text.

I had it working before but made some changes and for the life of me no idea what broke it. thanks!
date-select-sql-error.PNG
Comment
Watch Question

Fixer of Problems
Most Valuable Expert 2014
Commented:
'date' is sometimes a reserved word and I would not use it for a column name.  https://technet.microsoft.com/en-us/library/ms189822%28v=sql.105%29.aspx  In theory you could use [date] but I still wouldn't.
Paul JacksonSoftware Engineer
Top Expert 2011
Commented:
Why on  earth are you using the text data type in your database it is considered to be Large Object Data, you should be using varchar from what I can see and this should fix your problem.
The text datatype has been deprecated and varchar(max) should be used instead but I don't think you need varchar(max) in your case. You should just use varchar and specify a length.

Author

Commented:
thanks i'm obviously learning so thanks for the help