Date error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date error

Author

Topic

doublek
Starting Member

8 Posts

Posted - 2006-02-19 : 08:04:37

hi, my Date field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks

no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String

myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()

Author = myReader("Author")

myReader.Close()
myConn.Close()
lblShow.Text = Subject

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-19 : 08:57:11

what is the data type for column Date in table Booktbl ?

----------------------------------
'KH'
Time is always against us

doublek
Starting Member

8 Posts

Posted - 2006-02-19 : 09:16:38

datetime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-19 : 09:32:03

quote:
Originally posted by doublek

datetime

In this case you don't have to cast it to datetime

no = "20060215"
strSQL = "SELECT Author FROM Booktbl WHERE Date = '" & no & "'"

Specify no in YYYYMMDD format

----------------------------------
'KH'
Time is always against us

doublek
Starting Member

8 Posts

Posted - 2006-02-19 : 09:50:32

i have try want to give "20060215" but it still prompt error "Invalid attempt to read when no data is present". is't that the data field no work? i try to retrive the author without use the date, is ok. example "select Author Form Booktbl where ID = '" & id & "'"

doublek
Starting Member

8 Posts

Posted - 2006-02-19 : 10:35:04

the "no" do i need to declare as datetime, date or string

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-20 : 01:26:55

You should use DateTime variable

Dim no as DateTime (or date)
no = DateValue

Also are you using Julian date?
What does no = "2152006" mean?

Madhivanan

Failing to plan is Planning to fail

doublek
Starting Member

8 Posts

Posted - 2006-02-22 : 08:57:02

this is what is do to insert data to the Booktbl

Dim MyDate As Datetime = Now()
strAuthor = tbAuthor.Text.Trim
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("INSERT INTO Booktbl (Author, Date) VALUES(@author,@MyDate)", myConnection)
myCommand.parameters.add("@author",sqldbtype.varchar).value=strAuthor
myCommand.parameters.add("@MyDate",sqldbtype.datetime).value=MyDate
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Catch ex As SqlException
lblMsg.Text = " Error"
End Try
End Sub

After that the date record store in database look like this " 2/22/2006 11:40:40 AM"
the data type used to declare in the Date column is DateTime

this is what i do to retrive Author data depend on the Date

Dim Author As String
Dim no As DateTime
no = "2/22/2006"

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("SELECT Author FROM Booktbl WHERE Date = @no", myConnection)
myCommand.Parameters.Add("@no", SqlDbType.DateTime).Value = no
myConnection.Open()

Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
myReader.Read()

Author = myReader("Author")

myReader.Close()
myConnection.Close()
lblShow2.Text = Author

it show me the error message "Invalid attempt to read when no data is present" why? it seem like the date got problem. i been working on this since few days, yet i still can't get the result. anyone help appreaciate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources