New topic: Looping nil values into array help.
<http://forums.realsoftware.com/viewtopic.php?t=34404> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message waveuponwave Post subject: Looping nil values into array help.Posted: Fri Jun 25, 2010 7:47 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. I have an array that I need to populate with empty values as well as the matched results from the query. The following code works the way I want it to as it results a 0 wherever there is not a match from the query. However if I don't do a (*) search, a more refined search, it only returns the exact matches for the array, which causes a nil object exception. How can I add a 0 into the array for each loop that does not return a match from the database? Thanks. Code: for ii = 0 to 36 Now.Day = Now.Day +1 CalendarDayPos = str(replace(Now.SQLDate, right(Now.SQLDate, 0), "")) Dim adodbConnection3 as new OLEObject( "ADODB.Connection" ) Dim adodbCommand3 as new OLEObject( "ADODB.Command" ) Dim adodbRecordSet3 as OLEObject Dim adodbFields3 as OLEObject Dim adodbField3 as OLEObject Dim fieldCount3 as integer Dim sql3 as string Dim Results As String 'open the connection adodbConnection3.Open(gstrConnect ) sql3 = "SELECT Count(*) As EventCount From tPlanner Where PlannerDate = '" + CalendarDayPos + "' and Active <> '0'" adodbCommand3.ActiveConnection = adodbConnection3 adodbCommand3.CommandText = sql3 adodbRecordSet3 = adodbCommand3.Execute adodbFields3 = adodbRecordSet3.Fields fieldCount3 = adodbFields3.Count 'Loop through getting values while not adodbRecordSet3.EOF adodbFields3 = adodbRecordSet3.Fields Results = adodbRecordset3.Fields("EventCount").Value PlannerCount.Append (Results) adodbRecordSet3.MoveNext wend adodbConnection3.Close next _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top brisance Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 8:47 pm Joined: Tue Oct 06, 2009 2:38 am Posts: 276 You didn't mention what database you're using so I'll assume SQL Server. Code:SELECT COALESCE( Count(*), 0 ) As EventCount FROM tPlanner WHERE PlannerDate = '" + CalendarDayPos + "' AND Active <> '0'" Alternatively, you can also catch a NilObjectException by putting the code in a try-catch block. _________________ Mike Ash: Getting Answers Top waveuponwave Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 8:48 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. Sorry, yeah it's MSSQL. I'll try this out. Thanks. _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top waveuponwave Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 8:53 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. You misunderstand my question. I have a loop that runs 36 times that I need to put into an array. The loop contains a database query. I am trying to replace an empty result with a 0 or just anything when the result is nil. _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top waveuponwave Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 9:25 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. No post editing... Anyways the short of it is this. How do I add a value to an array when a query results nothing or nil in a for loop? _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top brisance Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 9:29 pm Joined: Tue Oct 06, 2009 2:38 am Posts: 276 Some problems: 1) Your loop is going to run 37 times, not 36 (zero to thirty-six inclusive is 37). 2) Since you are on SQL Server, you can get rid of the loop by explicitly calling the DateAdd() and GetDate() functions on SQL Server. Code:SELECT COALESCE( Count(*), 0 ) AS EventCount FROM tPlanner WHERE PlannerDate BETWEEN GetDate() AND DateAdd( Day, 36, GetDate() ) AND Active <> '0'This SQL statement retrieves all Counts from the tPlanner table where the dates are between today and 36 days from today. If it encounters a NULL, it will return 0 so you can put 0 in the array. _________________ Mike Ash: Getting Answers Top waveuponwave Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 9:31 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. Thanks for the reply, but the query you are referencing is not the one I am tying to run. Yes, I know that works. That is not what I am trying to do. I posted that so you could see what I had working. This is the query I am trying to work with. Code:SELECT DISTINCT LEFT(PlannerDescription, 15) As EventCount, PlannerDate From tPlanner Where PlannerDate = '" + CalendarDayPos + "' and Active <> '0' Order By PlannerDate _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top brisance Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 9:48 pm Joined: Tue Oct 06, 2009 2:38 am Posts: 276 It seems that you are trying to return DISTINCT EventCount values but there may be NULLs are in the PlannerDate column. As stated earlier, you should get rid of the loop and thus the array as it is causing impedance mismatch. If you insist on having the loop, then you should just apply a COALESCE() function on the PlannerDate column, like so: Code:SELECT DISTINCT LEFT(PlannerDescription, 15) As EventCount, COALESCE( PlannerDate, 0) From tPlanner Where PlannerDate = '" + CalendarDayPos + "' and Active <> '0' Order By PlannerDatePersonally, I would not do it this way, and try to do everything on SQL Server as it is way more efficient than running a loop of 36 SQL queries from an ADODB connection. _________________ Mike Ash: Getting Answers Top waveuponwave Post subject: Re: Looping nil values into array help.Posted: Fri Jun 25, 2010 9:50 pm Joined: Fri Jan 29, 2010 12:39 pm Posts: 319 Location: Virginia U.S.A. Thanks. I appreciate your help. _________________ RB2010r2 Pro on Win 7 I promise to help if I ever learn how to help myself. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
