if statements, compare date, datagrid

You are using a datareader, and using it for the DataBind(). The problem with that is that you can only use datareader as a single-pass forward-only cursor through the resultset. That is, once it is used once, you can't get anymore results out of it - you can't go back to the first row - not unless you execute it again!

So, your options are to go into the datagrid and get the values as text values, then cast them back to datetime etc., or use a dataset using ExecuteDataset and bind that to the datagrid. Then you can use the Dataset to get the values.

So does your sqlcommand only return a single row?

Andy

Could you tell what pageId is??

You could try something like this:

string pageID;
    pageID = Request.QueryString["pageID"];
    //Response.Write(pageID);

    //query database  
    objCmd = new SqlCommand("SELECT txtPageID, txtFileID, txtExpireTime, txtFileName FROM myLinks WHERE txtPageID=@pageID ORDER BY txtFileName", objConn);
    objCmd.Parameters.Add("@pageID", pageID);
   
    objConn.Open();
    objRdr = objCmd.ExecuteReader();

/*modified code*/
if ((pageID!=null)&&(expireTime>DateTime.Now))
{
    myDataGrid.DataSource = objRdr;
    myDataGrid.DataBind();
    myDataGrid.Visible=true;
}
else
    myDataGrid.Visible=false;
/*end modified code*/


    objRdr.Close();
    objConn.Close();

Hello,

The query returns about 5 rows on average.  I need to check to make sure it returns at least one row and that the date stored in the txtExpireTime field has not past yet.  I don't really need to use datagrid, datalist or repeater would be fine too.

Annab0242000,

if ((pageID!=null)&&(expireTime>DateTime.Now)) won't work because it's checking my variable, instead of making sure rows were returned and that the txtExpireTiime has not past.

basically, need somthing like this.  If the query doesn't find anything with the pageID from the querystring the do this.  If it does find it then it needs to check the expire time of each record.  If the expire time has past then do this else do this for each record.

Thanks,
kurt

In PHP I had this but I'm trying to convert it to ASP.NET C#


<?php
      $pageID = $_GET['pageID'];

      // Execute Query
      $msSqlQry = mssql_query("SELECT * FROM myLinks WHERE txtPageID = '$pageID'")
            or die ("Unable to run query");
            
      // Iteration loop, for each row in rowset
      $lineNumber = 1;
    while ($row = mssql_fetch_assoc($msSqlQry))

    {
        // Assigning variables from cell values
        $txtFileID             = $row["txtFileID"];
        $txtPageID             = $row["txtPageID"];
        $expireTime       = $row["txtExpireTime"];
        $txtFileName       = $row["txtFileName"];
        $txtToName             = $row["txtToName"];
        $txtToEmail       = $row["txtToEmail"];
        $txtFromName      = $row["txtFromName"];
        $txtFromEmail       = $row["txtFromEmail"];
        $numDownloads       = $row["numDownloads"];
            //get current time
            $currentTime       = date("YmdHis");
            
            //get basename
            $txtBasename = basename($txtFileName);
            
            //make sure link has not expired
            if ($currentTime > $expireTime)
            {
                  echo "<tr><td align=\"left\">";
                  echo $lineNumber . ") <font color=\"#FF0000\">Link Expired - </font>" . $txtBasename;
                  echo "</td></tr>";
            }
            else
            {
              // Outputting data to browser
                  echo "<tr><td align=\"left\">";
                  echo($lineNumber . ') <A HREF=downloadAction.php?fileID=' . $txtFileID . '>');
                     echo('Click to download:  ' . $txtBasename .'</A>');
                  echo "</td></tr>";
            }//end if
            $lineNumber++;
            $pageExists = 1;
    }//end while
      
?>
</table>

OK, well, your need to get at the data that has been used for the databind, that much is clear. You can either do that by returning a dataset and using that for the datasource, or you can return the datareader, use that for the databind and then go through the cells in the data object. Because you are talking about using DataGrid, or Repeater, or DataList, or maybe a GridView in .NET 2.0, I would get a DataSet, that way you don't have to change your code if you change your presentation object.

So, maybe do:
    bool blnExpired = true;
    objConn.Open();
    Dataset objSet = objCmd.ExecuteDataset();
    objConn.Close();

    // now check the datagrid
    if (objSet.Tables[0].Rows == 0)
        myDataGrid.Visible = false;
    for (int i = 0; i < objSet.Tables[0].Rows.Count)
    {
        //  Check the datacell that holds the expired value
        if (<cellhasexpired>)
            myDataGrid.Visible = false;
            break;
    }
    if (myDataGrid.Visible == true)
    {
        myDataGrid.DataSource = objSet...;
        myDataGrid.DataBind();
    }

Alternatively, do the expired checks in the SELECT statement (maybe change it to a stored procedure), return an output parameter that indicates if the rows have expired/there are no rows, and use that to decide on the visible state of the datagrid.

Andy

Yup, that makes sense. You just need to make a bit of a conceptual leap from PHP to .NET: In .NET the datareader is passed to the presentation object. You then call DataBind which goes through the datareader from start to end. The problem is that as it is effectively a forward-only cursor, you can't go through it again without executing it all over again.

What is your database software?

Andy

datareader would be fine.  I'm using ASP.net 2.0 with C# and the database is sql server express 2005.  I would rather use asp.net to compare the date and time.  I'm looking for the simplest solution sense I am new to this.

Thanks,
Kurt

Hmmm, if you absolutely HAVE to do this with a datareader, and not a dataset, then you are going to have to get the number of rows and the values out of the cells of the DataGrid after the DataBind().

I recommend using a dataset, and try the code posted above (forget the bool blnExpired = true; line, it got left in there after a bit of experimentation).

Andy

CS0117: 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteDataset'

Line 50:     DataSet objSet = objCmd.ExecuteDataset();

Sorry, I wasn't talking literally, I was talking in psuedo-code. I am very sorry. You need to use a data adapter to fill the dataset.

The source of that ExecuteDataset command is the Data Access Application block. If you are doing a lot of queries against a datbase, I would recommend looking it up on MSDN.

Seems like working with data before displaying it would be common practice.  I guess I need to find a simple tutorial on DataSets.

Kurt

Some summaries for you:
http://quickstart.developerfusion.co.uk/QuickStart/howto/doc/adoplus/GetDataFromDB.aspx
http://msdn.microsoft.com/msdnmag/issues/05/11/DataPoints/

And the inimitable 4guys; if they don't say it simply, it isn't worth saying
http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

What am I missing here.

CS0165: Use of unassigned local variable 'objSet'

Line 83:     adapter.Fill(objSet);

Ok, fixed that error.

Now it works but when there are no records returned the datagrid is still visible.  I removed the 0 and replaced it with null because I kept getting an error but it still always displays the datagrid.

    if (objSet.Tables[0].Rows == null)
    {
        myDataGrid.Visible = false;
    }
    else
    {
        myDataGrid.DataSource = objSet;
        myDataGrid.DataBind();
    }

This is the error I get

CS0019: Operator '==' cannot be applied to operands of type 'System.Data.DataRowCollection' and 'int'
Line 89:     if (objSet.Tables[0].Rows == 0)

Try:

objSet.Tables[0].Rows.Count == 0

Sorry

Cool, that part works now.  Thanks.

Now, how could I complete this part.  I'm not sure how to check a certain cell.

    for (int i = 0; i < objSet.Tables[0].Rows.Count)
    {
        //  Check the datacell that holds the expired value
        if (<cellhasexpired>)
            myDataGrid.Visible = false;
            break;
    }

Either of these loops will give you a starting point. The second is more complicated, but the first has to get the enumerator out of the Rows property of the DataTable, so I'm guessing it will take a couple of microseconds longer (if you are worried about performance). I have assumed that the txtExpireTime column is in datetime or smalldatetime format, if not you should use something like DateTime.ParseExact to convert it to DateTime. I have also assumed you are comparing the expiry time to the current time, if not then substitute your own time variable.

      foreach (DataRow r in objSet.Tables[0].Rows)
      {
            if ((DateTime)r["txtExpireTime"] < System.DateTime.Now)
                  //      expired
            else
                  //      not expired
      }

      for (int i = 0; i < objSet.Tables[0].Rows.Count; i++)
      {
            if ((DateTime)objSet.Tables[0].Rows[i]["txtExpireTime"] < System.DateTime.Now)
                  //      expired
            else
                  //      not expired
      }

hm I get this error

 Exception Details: System.InvalidCastException: Specified cast is not valid.

Line 102:        if ((DateTime)r["txtExpireTime"] < System.DateTime.Now)

I stored it with  DateTime.Now in a varchar(50) field.  Should I of used datetime field?

Yup. But given that you haven't, use DateTime.ParseExact to get back to a datetime.

I decided to change the database field to type datetime.  I'm still getting the same error.  Any ideas why?

Thanks

Never mind it works now.  The field in the database was null for the record I was using to test.  Thanks for your help.

No problem, glad you got there in the end; especially as this was quite a conceptual leap from the php code.

That raises an interesting point. When moving code from one language to another ... should you try and do a straight translation? Or should you try a straight translation, stop as soon as it doesn't work, and then redesign from the original requirements for that piece of code?

In this case a redesign may have come up with a better solution than we have worked to finally. I don't know.

Anyway, I'm glad it worked.

Andy

Thanks for your help.  I'll give you the points for this one and start a new thread.  I have a few more things to figure out.  

I have a hyperlink column , I only want the link to display if the record has not expired.

Also I have a file name and path field.  I need to trim the field to only show the file name for display purposes.