?
Solved

Using DateDiff with a dataset?

Posted on 2003-11-13
30
Medium Priority
?
666 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
Industry Leaders: 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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

771 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