?
Solved

if statements, compare date, datagrid

Posted on 2006-03-21
27
Medium Priority
?
621 Views
Last Modified: 2012-08-13
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();
0
Comment
Question by:Kurt4949
  • 13
  • 12
  • +1
27 Comments
 
LVL 12

Expert Comment

by:AGBrown
ID: 16251367
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
 
LVL 9

Expert Comment

by:Raju Srivatsavaye
ID: 16251368
Could you tell what pageId is??
0
 

Expert Comment

by:annab0242000
ID: 16251651
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Kurt4949
ID: 16251871
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16252029
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16252050
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16252086
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16252207
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16252262
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16252412
CS0117: 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteDataset'

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

Expert Comment

by:AGBrown
ID: 16252655
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16252966
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16258107
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
 
LVL 12

Accepted Solution

by:
AGBrown earned 2000 total points
ID: 16259394
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16259433
0
 
LVL 7

Author Comment

by:Kurt4949
ID: 16260174
What am I missing here.

CS0165: Use of unassigned local variable 'objSet'

Line 83:     adapter.Fill(objSet);
0
 
LVL 7

Author Comment

by:Kurt4949
ID: 16261619
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16261634
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16261655
Try:

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

Sorry
0
 
LVL 7

Author Comment

by:Kurt4949
ID: 16261800
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16261906
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16262353
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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16264112
Yup. But given that you haven't, use DateTime.ParseExact to get back to a datetime.
0
 
LVL 7

Author Comment

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

Thanks
0
 
LVL 7

Author Comment

by:Kurt4949
ID: 16269833

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
 
LVL 12

Expert Comment

by:AGBrown
ID: 16269920
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
 
LVL 7

Author Comment

by:Kurt4949
ID: 16270016
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question