|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL reader issueno responses. Maybe I'll have better luck here. I'm using Visual Studio 2003 and inline VB.NET, not code-behind. ---------------------------------------------------------------------- I am confused by the behavior of the Reader object in my application. In my first draft of my routine, I wrote two different sql routines to filter out records: 1) The first to check if the table was empty (a common occurrence). 2) Then to see if a certain time interval had passed. I would use Reader.HasRows (avoiding the need to Reader.Read() the data) to evaluate the resulting data sets. This was very straightforward, and worked fine, but it required two connection opens & closes, two command Executes, etc. I got a little ambitious, and wanted to achieve both conditions with one sql statement, and came up with something like the following: sql = "select myColumn = " sql &= "case when not exists (select top 1 * from myTable) then " sql &= " 'Empty table' " sql &= "else " sql &= "(select top 1 myCol from myTable where " sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY myTime DESC), GetDate() ) > 10 ) " sql &= "end " Well, even though it worked flawlessly in Query Analyzer, the Reader gives unexpected results. If the table is not empty, and all records fail the second test, Reader.HasRows returns True. I would expect it to return False since the value of myColumn in this second test is NULL. Can somebody explain why this is so, or point to some documentation that explains it in some detail? I can only contribute that it's the "select myColumn = " part that causes this. btw, I use a simple statement ( "returnStatus = iif(IsDBNull(reader("myColumn")), False, True)" ) following a Reader.Read() to work with the code above, and it works predictably. So I don't need to know how to create an algorithm or write a workaround so much as I just want to know why the Reader object, particularly the Reader.HasRows property, behaves the way it does. Thanks very much in advance. Dave 1. This should be posted to the adonet newsgroup.
2. I don't really understand why you need 2 open/closes. You call HasRows. If that is true, you can call .Read, get your dad, and close the reader. Why do you need to re-execute the query? In any case, it is pretty difficult to answer your question, since you just posted the query, instead of the code you are running. Show quote "Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in message news:u5lb9nlqFHA.3424@TK2MSFTNGP14.phx.gbl... >I tried posting this on microsoft.public.dotnet.languages.vb.data, but got >no responses. Maybe I'll have better luck here. I'm using Visual Studio >2003 and inline VB.NET, not code-behind. > > ---------------------------------------------------------------------- > > I am confused by the behavior of the Reader object in my application. > > In my first draft of my routine, I wrote two different sql routines to > filter out records: > > 1) The first to check if the table was empty (a common occurrence). > 2) Then to see if a certain time interval had passed. > > I would use Reader.HasRows (avoiding the need to Reader.Read() the data) > to > evaluate the resulting data sets. > > This was very straightforward, and worked fine, but it required two > connection opens & closes, two command Executes, etc. > > I got a little ambitious, and wanted to achieve both conditions with one > sql > statement, and came up with something like the following: > > sql = "select myColumn = " > sql &= "case when not exists (select top 1 * from myTable) then " > sql &= " 'Empty table' " > sql &= "else " > sql &= "(select top 1 myCol from myTable where " > sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY myTime > DESC), GetDate() ) > 10 ) " > sql &= "end " > > Well, even though it worked flawlessly in Query Analyzer, the Reader gives > unexpected results. If the table is not empty, and all records fail the > second test, Reader.HasRows returns True. I would expect it to return > False since the value of myColumn in this second test is NULL. > > Can somebody explain why this is so, or point to some documentation that > explains it in some detail? I can only contribute that it's the "select > myColumn = " part that causes this. > > btw, I use a simple statement ( "returnStatus = > iif(IsDBNull(reader("myColumn")), False, True)" ) following a > Reader.Read() > to work with the code above, and it works predictably. So I don't need to > know how to create an algorithm or write a workaround so much as I just > want > to know why the Reader object, particularly the Reader.HasRows property, > behaves the way it does. > > Thanks very much in advance. > > Dave > > > Marina,
Thanks for your response. 1) I will post to the adonet newsgroup if I don't get the answer here. Thank you for that tip. 2) I'm not sure what you mean by "get your dad", but I'll lay out what is going on a different way: Step 1: Are there any records in the table? No: Return TRUE Yes: Go To Step 2 Step 2: Was the most recent record inserted more than 10 minutes ago? Yes: Return TRUE No: Return FALSE So, originally, I did this with two queries. Now I do it with one. Ideally, I would like to just use the .HasRows properties following a cmd.ExecuteReader() statement. But instead, since the .HasRows property is true whether there are rows or not, I have to do a Reader.Read(), evaulate an IsDBNull(...column...), and return a value depending on the result. Not the most serious problem I've ever had in life, but it's unsatisfying to have to go through all this. I didn't add more code because: 1) I have isolated the problem to the phrase "select myColumn = ". Apparently this causes Reader.HasRows to return TRUE even when the query returns no rows. You could distill my entire post to the previous two sentences. 2) I didn't really need the code critiqued; I only need the behavior of the Reader explained. It is the query that causes the unintuitive result. Thank you again for replying, Marina. Dave "Marina" <someone@nospam.com> wrote in message Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in messagenews:eZLlTImqFHA.3136@TK2MSFTNGP11.phx.gbl... > 1. This should be posted to the adonet newsgroup. > 2. I don't really understand why you need 2 open/closes. You call > HasRows. If that is true, you can call .Read, get your dad, and close the > reader. Why do you need to re-execute the query? > > In any case, it is pretty difficult to answer your question, since you > just posted the query, instead of the code you are running. > Show quote news:u5lb9nlqFHA.3424@TK2MSFTNGP14.phx.gbl... >I tried posting this on microsoft.public.dotnet.languages.vb.data, but got >no responses. Maybe I'll have better luck here. I'm using Visual Studio >2003 and inline VB.NET, not code-behind. > > ---------------------------------------------------------------------- > > I am confused by the behavior of the Reader object in my application. > > In my first draft of my routine, I wrote two different sql routines to > filter out records: > > 1) The first to check if the table was empty (a common occurrence). > 2) Then to see if a certain time interval had passed. > > I would use Reader.HasRows (avoiding the need to Reader.Read() the data) > to > evaluate the resulting data sets. > > This was very straightforward, and worked fine, but it required two > connection opens & closes, two command Executes, etc. > > I got a little ambitious, and wanted to achieve both conditions with one > sql > statement, and came up with something like the following: > > sql = "select myColumn = " > sql &= "case when not exists (select top 1 * from myTable) then " > sql &= " 'Empty table' " > sql &= "else " > sql &= "(select top 1 myCol from myTable where " > sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY myTime > DESC), GetDate() ) > 10 ) " > sql &= "end " > > Well, even though it worked flawlessly in Query Analyzer, the Reader gives > unexpected results. If the table is not empty, and all records fail the > second test, Reader.HasRows returns True. I would expect it to return > False since the value of myColumn in this second test is NULL. > > Can somebody explain why this is so, or point to some documentation that > explains it in some detail? I can only contribute that it's the "select > myColumn = " part that causes this. > > btw, I use a simple statement ( "returnStatus = > iif(IsDBNull(reader("myColumn")), False, True)" ) following a > Reader.Read() > to work with the code above, and it works predictably. So I don't need to > know how to create an algorithm or write a workaround so much as I just > want > to know why the Reader object, particularly the Reader.HasRows property, > behaves the way it does. > > Thanks very much in advance. > > Dave > > > that was a typo, i meant 'data'.
I'm not sure why you say that HasRows is true whether or not there are any rows. If this was so it would be a big ado.net bug, and I haven't heard of it before. But, since you are returning the string 'Empty table', I am not sure why you expect the result to be NULL. And btw, a row with one column, the value of which is NULL, means there is a row. HasRows *should* return true. If there are no rows at all - then it should return false. HasRows should equal True with this query: Select NULL HasRows should equal False with this query: Select 1 WHERE 1=2 If you run the first in QA, you will see one row with NULL in it. If you run the second in QA you will get no rows. Show quote "Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in message news:%23D4zM4mqFHA.2276@TK2MSFTNGP10.phx.gbl... > Marina, > > Thanks for your response. > > 1) I will post to the adonet newsgroup if I don't get the answer here. > Thank you for that tip. > > 2) I'm not sure what you mean by "get your dad", but I'll lay out what is > going on a different way: > > Step 1: > Are there any records in the table? > No: Return TRUE > Yes: Go To Step 2 > > Step 2: > Was the most recent record inserted more than 10 minutes ago? > Yes: Return TRUE > No: Return FALSE > > So, originally, I did this with two queries. Now I do it with one. > Ideally, I would like to just use the .HasRows properties following a > cmd.ExecuteReader() statement. But instead, since the .HasRows property > is true whether there are rows or not, I have to do a Reader.Read(), > evaulate an IsDBNull(...column...), and return a value depending on the > result. Not the most serious problem I've ever had in life, but it's > unsatisfying to have to go through all this. > > I didn't add more code because: > 1) I have isolated the problem to the phrase "select myColumn = ". > Apparently this causes Reader.HasRows to return TRUE even when the query > returns no rows. You could distill my entire post to the previous two > sentences. > 2) I didn't really need the code critiqued; I only need the behavior of > the Reader explained. It is the query that causes the unintuitive result. > > Thank you again for replying, Marina. > > Dave > > "Marina" <someone@nospam.com> wrote in message > news:eZLlTImqFHA.3136@TK2MSFTNGP11.phx.gbl... >> 1. This should be posted to the adonet newsgroup. >> 2. I don't really understand why you need 2 open/closes. You call >> HasRows. If that is true, you can call .Read, get your dad, and close the >> reader. Why do you need to re-execute the query? >> >> In any case, it is pretty difficult to answer your question, since you >> just posted the query, instead of the code you are running. >> > > > > Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in message > news:u5lb9nlqFHA.3424@TK2MSFTNGP14.phx.gbl... >>I tried posting this on microsoft.public.dotnet.languages.vb.data, but got >>no responses. Maybe I'll have better luck here. I'm using Visual Studio >>2003 and inline VB.NET, not code-behind. >> >> ---------------------------------------------------------------------- >> >> I am confused by the behavior of the Reader object in my application. >> >> In my first draft of my routine, I wrote two different sql routines to >> filter out records: >> >> 1) The first to check if the table was empty (a common occurrence). >> 2) Then to see if a certain time interval had passed. >> >> I would use Reader.HasRows (avoiding the need to Reader.Read() the data) >> to >> evaluate the resulting data sets. >> >> This was very straightforward, and worked fine, but it required two >> connection opens & closes, two command Executes, etc. >> >> I got a little ambitious, and wanted to achieve both conditions with one >> sql >> statement, and came up with something like the following: >> >> sql = "select myColumn = " >> sql &= "case when not exists (select top 1 * from myTable) then " >> sql &= " 'Empty table' " >> sql &= "else " >> sql &= "(select top 1 myCol from myTable where " >> sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY >> myTime >> DESC), GetDate() ) > 10 ) " >> sql &= "end " >> >> Well, even though it worked flawlessly in Query Analyzer, the Reader >> gives >> unexpected results. If the table is not empty, and all records fail the >> second test, Reader.HasRows returns True. I would expect it to return >> False since the value of myColumn in this second test is NULL. >> >> Can somebody explain why this is so, or point to some documentation that >> explains it in some detail? I can only contribute that it's the "select >> myColumn = " part that causes this. >> >> btw, I use a simple statement ( "returnStatus = >> iif(IsDBNull(reader("myColumn")), False, True)" ) following a >> Reader.Read() >> to work with the code above, and it works predictably. So I don't need >> to >> know how to create an algorithm or write a workaround so much as I just >> want >> to know why the Reader object, particularly the Reader.HasRows property, >> behaves the way it does. >> >> Thanks very much in advance. >> >> Dave >> >> >> > > > Hi Dave
It's seems that my 1et post was lost some were ;) > This was very straightforward, and worked fine, but it required two You can use one store procedure instead of using one or many queries> connection opens & closes, two command Executes, etc. The code of your SP could be something like this : if exists(select * from dbo.myTable) begin -- put your logic here end else begin -- put your logic here end One of the recomanded best practice is using stored procedure instead of ebedded (complex) queries in the code, and your case confirm this. Show quote "Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in message news:u5lb9nlqFHA.3424@TK2MSFTNGP14.phx.gbl... >I tried posting this on microsoft.public.dotnet.languages.vb.data, but got >no responses. Maybe I'll have better luck here. I'm using Visual Studio >2003 and inline VB.NET, not code-behind. > > ---------------------------------------------------------------------- > > I am confused by the behavior of the Reader object in my application. > > In my first draft of my routine, I wrote two different sql routines to > filter out records: > > 1) The first to check if the table was empty (a common occurrence). > 2) Then to see if a certain time interval had passed. > > I would use Reader.HasRows (avoiding the need to Reader.Read() the data) > to > evaluate the resulting data sets. > > This was very straightforward, and worked fine, but it required two > connection opens & closes, two command Executes, etc. > > I got a little ambitious, and wanted to achieve both conditions with one > sql > statement, and came up with something like the following: > > sql = "select myColumn = " > sql &= "case when not exists (select top 1 * from myTable) then " > sql &= " 'Empty table' " > sql &= "else " > sql &= "(select top 1 myCol from myTable where " > sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY myTime > DESC), GetDate() ) > 10 ) " > sql &= "end " > > Well, even though it worked flawlessly in Query Analyzer, the Reader gives > unexpected results. If the table is not empty, and all records fail the > second test, Reader.HasRows returns True. I would expect it to return > False since the value of myColumn in this second test is NULL. > > Can somebody explain why this is so, or point to some documentation that > explains it in some detail? I can only contribute that it's the "select > myColumn = " part that causes this. > > btw, I use a simple statement ( "returnStatus = > iif(IsDBNull(reader("myColumn")), False, True)" ) following a > Reader.Read() > to work with the code above, and it works predictably. So I don't need to > know how to create an algorithm or write a workaround so much as I just > want > to know why the Reader object, particularly the Reader.HasRows property, > behaves the way it does. > > Thanks very much in advance. > > Dave > > > I was wrong.
When I said this: > ...., I must have taken a shortcut in my SQL statement and left something > Well, even though it worked flawlessly in Query Analyzer, the Reader gives > unexpected results. If the table is not empty, and all records fail the important out. Because when I tried it again, Query Analyzer gives the same result as the Reader object, which is a row with a NULL value. (I had thought it had returned no rows.) A special thanks to Marina, who knew there couldn't have been a bug of that magnitude in ADO.NET and finally convinced me. In fact, I used her method, a simple SQL statement bordering on the trivial, to prove it to myself. Thanks also to B.M. for creating an algorithm and writing a workaround. Dave Show quote "Dave" <dave_Please_reply_to_group_o***@stic.net> wrote in message news:u5lb9nlqFHA.3424@TK2MSFTNGP14.phx.gbl... >I tried posting this on microsoft.public.dotnet.languages.vb.data, but got >no responses. Maybe I'll have better luck here. I'm using Visual Studio >2003 and inline VB.NET, not code-behind. > > ---------------------------------------------------------------------- > > I am confused by the behavior of the Reader object in my application. > > In my first draft of my routine, I wrote two different sql routines to > filter out records: > > 1) The first to check if the table was empty (a common occurrence). > 2) Then to see if a certain time interval had passed. > > I would use Reader.HasRows (avoiding the need to Reader.Read() the data) > to > evaluate the resulting data sets. > > This was very straightforward, and worked fine, but it required two > connection opens & closes, two command Executes, etc. > > I got a little ambitious, and wanted to achieve both conditions with one > sql > statement, and came up with something like the following: > > sql = "select myColumn = " > sql &= "case when not exists (select top 1 * from myTable) then " > sql &= " 'Empty table' " > sql &= "else " > sql &= "(select top 1 myCol from myTable where " > sql &= "DateDiff(minute, (select top 1 myTime from myTable ORDER BY myTime > DESC), GetDate() ) > 10 ) " > sql &= "end " > > Well, even though it worked flawlessly in Query Analyzer, the Reader gives > unexpected results. If the table is not empty, and all records fail the > second test, Reader.HasRows returns True. I would expect it to return > False since the value of myColumn in this second test is NULL. > > Can somebody explain why this is so, or point to some documentation that > explains it in some detail? I can only contribute that it's the "select > myColumn = " part that causes this. > > btw, I use a simple statement ( "returnStatus = > iif(IsDBNull(reader("myColumn")), False, True)" ) following a > Reader.Read() > to work with the code above, and it works predictably. So I don't need to > know how to create an algorithm or write a workaround so much as I just > want > to know why the Reader object, particularly the Reader.HasRows property, > behaves the way it does. > > Thanks very much in advance. > > Dave > > >  |
|||||||||||||||||||||||
Other interesting topics