Problem with query and datareader [modified]
-
Dim SelectCmd1 As New SqlCommand("SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s1'", SQLconn) SelectCmd1.CommandType = CommandType.Text Dim result1 As Integer Dim myDataReader1 As SqlDataReader myDataReader1 = SelectCmd1.ExecuteReader() myDataReader1.Read() result1 = myDataReader1.GetInt32(0) SQLconn.Close() SQLconn.Open() Dim SelectCmd2 As New SqlCommand("SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s2'", SQLconn) SelectCmd2.CommandType = CommandType.Text Dim result2 As Integer Dim myDataReader2 As SqlDataReader myDataReader2 = SelectCmd2.ExecuteReader() myDataReader2.Read() result2 = myDataReader2.GetInt32(0) SQLconn.Close() I have a table with records: name rawvalue quality timestamp s1______-1_____good____12-6-2007 9:45:00 s3______-1_____bad____13-5-2007 11:50:51 s1_____0_____good____14-7-2007 8:40:54 s2_____0____good____15-5-2007 6:00:45 . . . I read the most recent inserted "rawvalue" in the table to initialize 2 asp controls. I get an error for in the second query. And the error is "Invalid attempt to read when no data is present." I get the same error then I run the query in Sql Server Management Studio. The problem is with "timestamp". The "Max(timestamp)=14-7-2007 8:40:54"(3rd record) so it diplays he rawvalue="0" for s1. But for s2 I don't get any values....because the timestamp = 15-5-2007 6:00:45 < 14-7-2007 8:40:54 What modification do I have to do in query to display the ravalues for both s1 and s2? When I use this: SELECT [rawvalue] FROM [datastore] WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) AND name='s1') I get incorrect syntax near AND. -- modified at 13:12 Monday 14th May, 2007
-
Dim SelectCmd1 As New SqlCommand("SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s1'", SQLconn) SelectCmd1.CommandType = CommandType.Text Dim result1 As Integer Dim myDataReader1 As SqlDataReader myDataReader1 = SelectCmd1.ExecuteReader() myDataReader1.Read() result1 = myDataReader1.GetInt32(0) SQLconn.Close() SQLconn.Open() Dim SelectCmd2 As New SqlCommand("SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore]) AND name='s2'", SQLconn) SelectCmd2.CommandType = CommandType.Text Dim result2 As Integer Dim myDataReader2 As SqlDataReader myDataReader2 = SelectCmd2.ExecuteReader() myDataReader2.Read() result2 = myDataReader2.GetInt32(0) SQLconn.Close() I have a table with records: name rawvalue quality timestamp s1______-1_____good____12-6-2007 9:45:00 s3______-1_____bad____13-5-2007 11:50:51 s1_____0_____good____14-7-2007 8:40:54 s2_____0____good____15-5-2007 6:00:45 . . . I read the most recent inserted "rawvalue" in the table to initialize 2 asp controls. I get an error for in the second query. And the error is "Invalid attempt to read when no data is present." I get the same error then I run the query in Sql Server Management Studio. The problem is with "timestamp". The "Max(timestamp)=14-7-2007 8:40:54"(3rd record) so it diplays he rawvalue="0" for s1. But for s2 I don't get any values....because the timestamp = 15-5-2007 6:00:45 < 14-7-2007 8:40:54 What modification do I have to do in query to display the ravalues for both s1 and s2? When I use this: SELECT [rawvalue] FROM [datastore] WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) AND name='s1') I get incorrect syntax near AND. -- modified at 13:12 Monday 14th May, 2007
Have you thought about using
ExecuteScalarinstead ofExecuteReader?ExecuteScalaris used when you only want to retrieve the first column of the first row of the result set (typically when yours select only retrieves one row with one column in in) Also, I suspect that your second query is returning zero rows. You don't check for the condition of no data being returned, hence the error. UsingExecuteScalarwill remove the error and return null if no data is being returned from the query instead of throwing an error. In order that the query returns data, I'm guessing that you want the condition of the MAX timestap AND the name. You need to specify the name twice, once inside the subquery and once outside it.kallileo wrote:
SELECT MAX(timestamp) FROM [datastore]) AND name='s1'
This is just the subquery on its own. It should be easier to spot
WHEREthe error is. The full query should be:SELECT [rawvalue]
FROM [datastore]
WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) WHERE name='s1')
AND name='s1'
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Have you thought about using
ExecuteScalarinstead ofExecuteReader?ExecuteScalaris used when you only want to retrieve the first column of the first row of the result set (typically when yours select only retrieves one row with one column in in) Also, I suspect that your second query is returning zero rows. You don't check for the condition of no data being returned, hence the error. UsingExecuteScalarwill remove the error and return null if no data is being returned from the query instead of throwing an error. In order that the query returns data, I'm guessing that you want the condition of the MAX timestap AND the name. You need to specify the name twice, once inside the subquery and once outside it.kallileo wrote:
SELECT MAX(timestamp) FROM [datastore]) AND name='s1'
This is just the subquery on its own. It should be easier to spot
WHEREthe error is. The full query should be:SELECT [rawvalue]
FROM [datastore]
WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) WHERE name='s1')
AND name='s1'
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Thank you... I will check what you said about ExecuteScalar... I found the solution for the query. It should be like this: SELECT [rawvalue] FROM [datastore] WHERE timestamp=(SELECT MAX(timestamp) FROM [datastore] WHERE name='s1')
Your query is may have some issues. What if two items with different names share the same timestamp?
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Your query is may have some issues. What if two items with different names share the same timestamp?
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Yes you are absolutely right.... but this query doesn't work. SELECT [rawvalue] FROM [datastore] WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) WHERE name='s1') AND name='s1' ******* Error in FROM clause: near 'WHERE'. Unable to parse query text. ******
-
Yes you are absolutely right.... but this query doesn't work. SELECT [rawvalue] FROM [datastore] WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) WHERE name='s1') AND name='s1' ******* Error in FROM clause: near 'WHERE'. Unable to parse query text. ******
Too many brackets, remove one before the SELECT and the one after [datastore]
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Too many brackets, remove one before the SELECT and the one after [datastore]
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website