February 19, 2006 at 9:37 am
how to select date record from the database. my DatePost field in the database formated as 2/15/2006 11:40:46 AM and data type as datetime. i try to manually give a date but it give me error. the error come from myReader!. Do i need to declare the "no" as datetime, date or string? help me to correct, thanks
the code is below
Dim no as string
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("C onnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE Date ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()
Author = myReader("Author")
myReader.Close()
myConn.Close()
lblShow.Text = Subject
February 19, 2006 at 11:52 am
20060215 or 2/15/06 or 2-15-2006.
February 22, 2006 at 7:09 am
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
February 22, 2006 at 11:01 am
*Using dates in the format mm/dd/yyyy is generally not recommended.
What is 01/10/2006 the first of october or the tenth of january?
With 20060110 (yyyymmdd) there is no doubt and also is excellent for sorting.
*If you trace your query with the sql profiler, is your date passed correctly?
*Have a look at for using stored procedures
February 22, 2006 at 3:38 pm
Since the value in the database is "2/22/2006 11:40:40 AM" and you are passing in "2/22/2006" when doing the retrieval they are not the same because if no time is provided for a datatime value, 12:00 midnight is assumed. "2/22/2006 11:40:40 AM" is not equal to "2/22/2006 12:00:00 AM"
If you want all data entered on "2/22/2006", then your WHERE clause should be something like "WHERE Date >= @No and Date < dateadd(dd, 1, @No)"
February 24, 2006 at 4:08 am
I think JeffB hit the problem, but also remember that a datetime data type stores the data one way but will display it depending on the users regional settings. So if my date format in dd/mm/yy, that's how the database will display the date to me. Using the ISO yyyy/mm/dd format like Jo P suggested, might help if your users are from different countries. You might also want to read up on DATEPART functions, if you want to retrieve rows from a particular week, month, year.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply