Solved

Using DateDiff with a dataset?

Posted on 2003-11-13
30
653 Views
Last Modified: 2007-12-19
Hello Experts!

I am trying to get a column in my datagrid called "Days Old".  It is simply the date difference from a journal date in the database to today's date.  I've been told to use something like this:

Date1 = DateDiff("d", Now, objDataSet![Jrnl Date])

I can't get this to work properly.  

This is the error I get:

Type character '!' does not match declared data type 'System.Data.DataSet'.

Here is what my sub looks like on page load:

Sub BindGrid()
                   Dim objDataSet As DataSet = New DataSet
                   Dim myConnection As New SqlConnection(ConnectionString)
                   Dim Adapter As SqlDataAdapter = New SqlDataAdapter
                   Dim Date1 As String


                   Adapter.SelectCommand = _
                       New SqlCommand(SelectCommand1, myConnection)
                       myConnection.Open()
                      Adapter.Fill(objDataSet,"accounting")

                      Adapter.SelectCommand = _
                       New SqlCommand(SelectCommand2, myConnection)
                       Adapter.Fill(objDataSet,"comments")

                   DataGrid1.DataSource = objDataSet.Tables("accounting")
                   
                   Date1 = DateDiff("d", Now(), objDataSet![Jrnl Date])
                   DataGrid1.Databind()

          End Sub

Help!
0
Comment
Question by:jay-are
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 14
30 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9740972
Can you do something like this:

datTemp = objDataSet![Jrnl Date]
Date1 = DateDiff("d", Now(), datTemp)

FtB
0
 

Author Comment

by:jay-are
ID: 9740990
Still gives me this error:

Compiler Error Message: BC30277: Type character '!' does not match declared data type 'System.Data.DataSet'.

Source Error:

 

Line 285:
Line 286:                   DataGrid1.DataSource = objDataSet.Tables("accounting")
Line 287:                   datTemp = objDataSet![Jrnl Date]
Line 288:                   Date1 = DateDiff("d", Now(), datTemp)
Line 289:
 

Source File: c:\inetpub\wwwroot\aspx\new\omg2.aspx    Line: 287



Does datTemp need to be declared as a string?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9741100
Please excuse my ignorance, I don't know much about ASP.net--I am still firmly in the classic world for now.

As I see the issue, the DateDiff() method does not like the argument: objDataSet![Jrnl Date]

As an aside, you can do this to test:

<%Response.write(DateDiff("d", Now(), "11/15/2003"))%>

and you will see that in all other respects you are using the function properly.

So, either the function will not accept the ! mark in the parameter, or the parameter is not being evaluated.

If it is the latter, I wonder if you need something like objDataSet![Jrnl Date].value or something like that?

FtB

0
Technology Partners: 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 3

Expert Comment

by:keystrokes
ID: 9741162
How about :

Date1 = DateDiff("d", Now, objDataSet("Jrnl Date"))
0
 

Author Comment

by:jay-are
ID: 9741256
FtB:  Sorry I didn't specify between asp.net & classic.

Using the response.write it returns 1.  So that's right!  

keystrokes:

I tested your solution and it gave me this error:
Class 'System.Data.DataSet' cannot be indexed because it has no default property.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9741445
Okay, so then all we need to do is to figure out how to pass the value of objDataSet![Jrnl Date] to the function.

Do you know the ASP.Net DOM well enough to hazard a guess?

FtB
0
 

Author Comment

by:jay-are
ID: 9741512
No, I'm a noob to asp.net.  

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9741573
Is this subroutine supposed to return the date difference for every line on the grid or just for one item?

FtB
0
 

Author Comment

by:jay-are
ID: 9741652
Every line.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9741723
Okay, then there has to be someway to get at the value of that control as it is being built....

Here is what I would suggest--use the DateDiff() method in your sql select to create an expression field.

IF you don't know how to do that, please post your sql statement below.

FtB


0
 

Author Comment

by:jay-are
ID: 9741828
Heh, sorry you kinda have to hold my hand on these things for now.

I plugged it into my select statement like so:
Dim SelectCommand1 As String = "SELECT * FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) AND DateDiff("d", Now, objDataSet![Jrnl Date]) ORDER BY ControlNo ASC"

Error:  End of statement expected.


So I guess I didn't do that right.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742079
A few changes might do it:

AND DateDiff('d', Now, objDataSet![Jrnl Date]) ORDER BY ControlNo ASC"

and replace objDataSet![Jrnl Date] with the name of the date field.

FtB
0
 

Author Comment

by:jay-are
ID: 9742108
Ok I made the changes to the select and got this error:

System.Data.SqlClient.SqlException: Invalid parameter 1 specified for datediff.


Here's the select statement now:
"SELECT * FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) AND DateDiff('d', Now, [Jrnl Date]) ORDER BY ControlNo ASC"

The column in 'Accounting' is called Jrnl Date.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742216
Oh, we are getting close!

"SELECT * FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) AND DateDiff('d', Now(), Accounting.Jrnl Date) ORDER BY ControlNo ASC"

This might do it....

FtB
0
 

Author Comment

by:jay-are
ID: 9742262
Here's what I get with that select statement:
Exception Details: System.Data.SqlClient.SqlException: 'Now' is not a recognized function name.

Source Error:


Line 275:                       New SqlCommand(SelectCommand1, myConnection)
Line 276:                       myConnection.Open()
Line 277:                      Adapter.Fill(objDataSet,"accounting")
Line 278:
Line 279:                      Adapter.SelectCommand = _
 

Source File: c:\inetpub\wwwroot\aspx\new\omg2.aspx    Line: 277

So if I take the () off of Now it doesn't recognize 'date' from Jrnl Date.  So I put Jrnl Date in []'s.  Then of course I get this error again:
 System.Data.SqlClient.SqlException: Invalid parameter 1 specified for datediff.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742278
Try using Date() instead of Now().

Also, test this in your DBMS before trying it in your page.

FtB
0
 

Author Comment

by:jay-are
ID: 9742305
Well I can't logon to the server right now to test it there...Someone else is logged in under my account!  argh!

'Date' is not a recognized function name.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742331
What kind of database are you using?

FtB
0
 

Author Comment

by:jay-are
ID: 9742341
MsSql 7.0
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742378
Hmn! That may be an issue. What I am suggesting works with Access and SQL Server, but different DBMS support different functions, and that may be the case here.

For example, this query works fine on my database:

SELECT tblSentItems.datDate, datediff('d',Now(),tblSentItems.datDate) as numDaysDifference FROM tblSentItems

FtB
0
 

Author Comment

by:jay-are
ID: 9742496
Argh I thought for sure this would be an easy thing to do!

Ok I can finally log into the sql server and test it with the analyzer.  It gives me the same exact errors...

Once I change Now() to just Now it goes to Jrnl Date saying incorrect syntax near 'Date'.  So I put brackets around Jrnl Date and then it again says:   Invalid parameter 1 specified for datediff.

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742513
Try to figure out what the proper syntax is by playing around in the analyzer. You are really close to it now! I would help further but do not have access to a MySql database.


FtB
0
 

Author Comment

by:jay-are
ID: 9742549
MS Sql

I do like your select better:
SELECT *, DateDiff("d",Now,"Jrnl Date") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY ControlNo ASC

I am very close...If I replace Now, or Date with a '0' it counts the days up from 0 to the date

even puts them in a 'daysold' column for me..
this is very slick!

I just have to figure out what the word is for "today"!
0
 

Author Comment

by:jay-are
ID: 9742572
For sure it must be "Date"
but this doesn't work

looking at the examples here

www.w3schools.com/vbscript/func_datediff.asp

Why doesn't this work?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742577
I just took a look, and I think that:

today()

is the funciton that you need.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742701
The problem is that date functions are handled differently by the various DBMSs. I think that using today() should do it.

FtB
0
 

Author Comment

by:jay-are
ID: 9742707
woo

SELECT *, DateDiff("d",GetDate(),"Jrnl Date") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY ControlNo ASC

In sql's analyzer this works...
Returns the days properly

Now when I add this to my page it says end of statement expected.  It doesn't seem to like "d"...

So I change it to 'd' and get the error:
System.Data.SqlClient.SqlException: Invalid parameter 1 specified for datediff.

so close!
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 9742739
Okay!

Any luck with this:

"SELECT *, DateDiff(""d"",GetDate(),""Jrnl Date"") as daysold FROM Accounting INNER Join Comments on Control# = ControlNo WHERE (Description <> 'BANK FEE') AND [AcctNo]='1020' AND (accounting.AutoID IN (SELECT MAX(accounting.AutoID) FROM Accounting WHERE Description<>'BANK FEE' AND [ACCT NO]='1020'  GROUP BY Control#)) ORDER BY ControlNo ASC"

0
 

Author Comment

by:jay-are
ID: 9742768
YES

That's it!

Double quotes....See I knew it was something easy  :)

Thanks FtB!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9742826
Glad to have helped and am sorry that it took so long.

FtB
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
wordpress display sub menu only when click 12 91
Select case on click 3 29
SP converting date time to date and time separately 2 36
calculate days away 11 61
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…

739 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