if statements, compare date, datagrid

Hello,

I have a select statement that binds data to a datagrid.  It works fine but I also need an if statment to make sure the pageID exists, It needs to compare the current data to one in the expireTime field, and then if it has not expired and the page id exists then I need to set the datagrid visible.  How can I modify this code?  I tried but then the datagrid wasn't working.

    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();

    myDataGrid.DataSource = objRdr;
    myDataGrid.DataBind();

    objRdr.Close();
    objConn.Close();
LVL 7
Kurt4949Asked:
Who is Participating?
 
AGBrownCommented:
Datasets are so simple, you almost don't need to worry about it at first. Behind the scenes the datareader is actually used by the dataadapter to fill the dataset - all that you end up with is a simple generic collection object that you can use for any resultset, and manipulate however you want to ask questions about the data. Once filled it is disconnected from the database.

You are basically there. You need the following to fill your dataset. If there are any problems, double check it against the MSDN library documentation for the SqlAdapter, as that has example code for filling a dataset:

    objCmd = new SqlCommand("SELECT txtPageID, txtFileID, txtExpireTime, txtFileName FROM myLinks WHERE txtPageID=@pageID ORDER BY txtFileName", objConn);
    objCmd.Parameters.Add("@pageID", pageID);
    objCmd.Connection = objConn;
   
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = objCmd;
   
    objConn.Open();
    Dataset objSet;
    adapter.Fill(objSet);
    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();
    }

"Working with data" as you put it, is the essence of seperating your code into "layers". Whether these "layers" are simply classes, or physical computers is all dependent on the scale of your application. Generally you want to aim for:
  -Presentation code - deals only with putting information on the screen, not the generation of that information: calls myBusinessLogicClass.GetSomeDataSet()
  -Business Logic code - deals with doing logic, such as "are there rows, have they expired?": calls myDataAccessLayer.GetInfoFromTableAndReturnDataSet()
  -Data Access layer - deals with getting data from the database, at the core of this are classes that deal with SqlCommand, SqlDataAdapter and Dataset that are shared by all other BL classes accessing the database through this layer.

This isn't complex, so don't be worried. With only two or three different pages retrieving datasets from the database, you will quickly see the benefits once you stop having to keep writing the connection->command->adapter->dataset structure above. Also, people talk about using datareaders over datasets for performance, and that is fine in a 2-tier application; with this structure you still have the choice.

If I was looking at your problem w.r.t performanace I would:
-Write a stored procedure to do the SQL logic. This will declare an output parameter which will indicate if the dataset is valid, having checked for rows, and checked the expiry dates. It is likely that this will be better in performance terms anyway than looping through the rows once they come out of the database. If you aren't hot on SQL and stored procedures forget this step for the moment, but then you should use a dataset instead of a datareader.
-Know that output parameters cannot be inspected until the point that a datareader is closed (see the datareader documentation for details).
-Fetch the data
-Use the output parameter, or iterate through the dataset if not using the stored procedure, to decide on grid visibility
-Decide on grid visibility based on the logic

Then, once it works, and only once it works, you can, for instance, move the logic (rows and output parameter inspection) to a class that has a method called "GetMyLinksDataSet" or similar. That method will return null if either the rows did not exist, or the data had expired, otherwise it will return a dataset. Now use that to work with your grid:

Dataset dsLinks = GetMyLinksDataSet;
if (dsLinks == null)
   myGrid.Visible = false;
else
{
   myGrid.dataSource = ...
}

Then you can move the database code out of GetMyLinksDataSet into a data access layer, having looked into the enterprise application block on MSDN to see what they are doing (it is quite complex, and you can make it a lot simpler, just bear in mind that they are designing it to be used by anyone in any situation and you don't have to build that flexibility in).

This is the essence of "design, write, test, refactor" when writing code. Design the outline. Write the code. Test the code. Then make small changes to it so that you are never again any more than 2 minutes away from it working, but always closer to a more maintainable design. This is a generalisation, but it works well.

Andy
0
 
AGBrownCommented:
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

0
 
Raju SrivatsavayeSoftware EngineerCommented:
Could you tell what pageId is??
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
annab0242000Commented:
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();
0
 
Kurt4949Author Commented:
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
0
 
Kurt4949Author Commented:
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>
0
 
AGBrownCommented:
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
0
 
AGBrownCommented:
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
0
 
Kurt4949Author Commented:
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
0
 
AGBrownCommented:
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
0
 
Kurt4949Author Commented:
CS0117: 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteDataset'

Line 50:     DataSet objSet = objCmd.ExecuteDataset();
0
 
AGBrownCommented:
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.
0
 
AGBrownCommented:
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.
0
 
Kurt4949Author Commented:
Seems like working with data before displaying it would be common practice.  I guess I need to find a simple tutorial on DataSets.

Kurt
0
 
Kurt4949Author Commented:
What am I missing here.

CS0165: Use of unassigned local variable 'objSet'

Line 83:     adapter.Fill(objSet);
0
 
Kurt4949Author Commented:
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();
    }
0
 
Kurt4949Author Commented:
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)
0
 
AGBrownCommented:
Try:

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

Sorry
0
 
Kurt4949Author Commented:
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;
    }
0
 
AGBrownCommented:
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
      }
0
 
Kurt4949Author Commented:
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?
0
 
AGBrownCommented:
Yup. But given that you haven't, use DateTime.ParseExact to get back to a datetime.
0
 
Kurt4949Author Commented:
I decided to change the database field to type datetime.  I'm still getting the same error.  Any ideas why?

Thanks
0
 
Kurt4949Author Commented:

Never mind it works now.  The field in the database was null for the record I was using to test.  Thanks for your help.
0
 
AGBrownCommented:
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
0
 
Kurt4949Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.