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
Hello Sebastian,
Try adding slashes to your date string like this:
no = "2/15/2006"
Even better would be to use a variable of DateTime type like this:
Dim no As DateTime
no = "2/15/2006"
This should solve your problem.
Regards, Maurits
|||
If you used no="20060215" then your query would be correct, but it still would not return any records, because the datetime "2006-15-02 11:40:46" <> "2006-15-02 00:00:00".
no = "20060215" -- assume string is in YYYYMMDD format so SQL Server can implicitly convert it to a datetime for comparision (OR any string recognized by SQL Server's current language/culture setting)
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
I'm going to assume you know how to make no a datetime variable instead of a string, but assuming it is a datetime with a time portion being midnight, this should do what you want:
Dim myConn As new SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
myConn.open
Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)
myCmd.parameters.add("@.no",sqldbtype.datetime).value=no
lblShow.Text = myCmd.executeScalar
myConn.Close()
Actually, the above code will work even if no is string that contains a datetime in a format that is understood by the culture the ASP.NET thread is running too. (For example "2/15/2006" if the current culture is US-en, or "15-02-2006" if the current culture is FR-fr", etc.)
|||Two things to keep in mind when working with datetime column types.1. Datetime type contains two components: date and time. So, when specifying WHERE clause, you have to consider the time component as well. By default, datetime's time component is "12:00:00 AM". So, if you're trying to filter for date of "02-20-2006', the something similar to following would be used:
... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'
(notice the less-than sign and the date of one more than the one intended in query)
or
... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'
2. As in the example above, you need to provide a specific format of date string. Your value of "2152006" is confusing to the translation engine, so transform it to something like "mm-dd-yyyy", "mm/dd/yyyy" or even "yyyymmdd" which is the ISO version.|||
WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'
is really what you should use, the statement
WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'
actually has two issues. First, if you use that with a datetime column, since the datetime field is accurate to approximately 1/300th of a second, there is really a 1-second gap that the query will not find. You could conceivably do something like:
WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59.997 PM'
but that assumes you know the exact highest time value that is storable in a datetime field, which I prefer not to use (assume). It's inviting problems on upgrades, conversion, or database migration to another platform. Secondly, if you tried to do that with a smalldatetime field, SQL Server will convert the strings to a smalldatetime, and since that is only accurate to 2 seconds, it will round your string to the nearest 2-second interval (Since it is half way between, it will round UP), giving you 02-21-2006 00:00:00, which will include any smalldatetimes that are part of midnight the next day, also very yucky.
|||
jcasp wrote:
Two things to keep in mind when working with datetime column types.
... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'
or... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'
I'd definitely stick with that first WHERE clause. The second one is not taking milliseconds into consideration. And I prefer using the ISO version of the date '20060215' as it is unanmbiguous.
Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)
You want to stay away from this approach Don't use a function against a column like that; it really increases the overhead in running the query as the fuction has to be performed against every row. Stick with the approach suggested by jcasp.|||Correction noted. One should keep in mind that datetime column type does have higher precision than the second component (goes to milliseconds). Very easy to overlook, but can definitely come into play. Good catch.|||
Two things to keep in mind when working with datetime column types.
... WHERE DATE_COL >= '02-20-2006' AND DATE_COL< '02-21-2006'
or... WHERE DATE_COL >= '02-20-2006 12:00:00 AM' AND DATE_COL<= '02-20-2006 11:59:59 PM'
I'd definitely stick with that first WHERE clause. The second one is not taking milliseconds into consideration. And I prefer using the ISO version of the date '20060215' as it is unanmbiguous.
Dim myCmd As SqlCommand = New SqlCommand("SELECT Author FROM Booktbl WHERE cast(Date as datetime) >=@.no AND cast(Date as datetime)<dateadd(d,1,@.no)",myConn)
You want to stay away from this approach Don't use a function against a column like that; it really increases the overhead in running the query as the fuction has to be performed against every row. Stick with the approach suggested by jcasp.
Actually, what I wrote is the only method mentioned here that will actually work, assuming that the field "Date" is actually a varchar field. Since the original poster said his "PostDate" field had a format of "MM/DD/YYYY HH:MM:SS PM", and datetimes don't have a format, this was my assumption. Of course, if the original poster was incorrect, and the field is actually a datetime, then just drop the cast portion of my command. Since the DateAdd function is a deterministic function, and it's input is based on the parameters, SQL Server will only need to execute this once instead of every row.
|||ok, before i select the author record, i have insert a date record into the table. this is how i do. Dim Date As Date = Now()
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
Dim myCommand As New SqlCommand("INSERT INTO Booktbl (Author, Date) VALUES('" & strAuthor & "', '" & Date & "')", myConnection)
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As SqlException
lblMsg.Text = " Error"
End Try
End Sub
End insert section. the data type used to store date is datetime. the record look like this 2/12/2006 11:40:40 AM
when i try to retrive back the record base on the date it give me error. i also declare the no as date, datetime or string and put it "2/12/2006" or some other format it still not work. i have try one example to put the no = "2/12/2006 11:40:40 AM" then it work, but what i need is the date not the time.
anymore figure out what happen??|||Dim MyDate As Datetime = Now()
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",sqdbtype.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|||Motley, i have used your code to insert data to the table. now i want to select the info and display it. This is how i do it!
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 give me this error message "Invalid attempt to read when no data is present."why? why i can't retrive the data? i been work on this few days ago, hope u can help me. thanks
|||
Because the datetimes aren't exactly the same.
SELECT Author FROM Booktbl WHERE dateadd(d,0,datediff(d,0,Date)) = dateadd(d,0,datediff(d,0,@.no))
is the select statement you want if you just want the date portions to match.
SELECT Author FROM Booktbl WHERE Date>=dateadd(d,0,datediff(d,0,@.no)) AND Date<dateadd(d,1,datediff(d,0,@.no))
is the same idea, but will execute much faster, it's looking for a datetime >= to the beginning of the day in @.no through (but not including) the beginning of the day after @.no. (Between 2/22/2006 @. midnight through 2/23/2006 @. midnight)
sql
No comments:
Post a Comment