jay-are
asked on
Using DateDiff with a dataset?
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(ConnectionSt ring)
Dim Adapter As SqlDataAdapter = New SqlDataAdapter
Dim Date1 As String
Adapter.SelectCommand = _
New SqlCommand(SelectCommand1, myConnection)
myConnection.Open()
Adapter.Fill(objDataSet,"a ccounting" )
Adapter.SelectCommand = _
New SqlCommand(SelectCommand2, myConnection)
Adapter.Fill(objDataSet,"c omments")
DataGrid1.DataSource = objDataSet.Tables("account ing")
Date1 = DateDiff("d", Now(), objDataSet![Jrnl Date])
DataGrid1.Databind()
End Sub
Help!
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(ConnectionSt
Dim Adapter As SqlDataAdapter = New SqlDataAdapter
Dim Date1 As String
Adapter.SelectCommand = _
New SqlCommand(SelectCommand1,
myConnection.Open()
Adapter.Fill(objDataSet,"a
Adapter.SelectCommand = _
New SqlCommand(SelectCommand2,
Adapter.Fill(objDataSet,"c
DataGrid1.DataSource = objDataSet.Tables("account
Date1 = DateDiff("d", Now(), objDataSet![Jrnl Date])
DataGrid1.Databind()
End Sub
Help!
ASKER
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("account ing")
Line 287: datTemp = objDataSet![Jrnl Date]
Line 288: Date1 = DateDiff("d", Now(), datTemp)
Line 289:
Source File: c:\inetpub\wwwroot\aspx\ne w\omg2.asp x Line: 287
Does datTemp need to be declared as a string?
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("account
Line 287: datTemp = objDataSet![Jrnl Date]
Line 288: Date1 = DateDiff("d", Now(), datTemp)
Line 289:
Source File: c:\inetpub\wwwroot\aspx\ne
Does datTemp need to be declared as a string?
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
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(
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
How about :
Date1 = DateDiff("d", Now, objDataSet("Jrnl Date"))
Date1 = DateDiff("d", Now, objDataSet("Jrnl Date"))
ASKER
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.
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.
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
Do you know the ASP.Net DOM well enough to hazard a guess?
FtB
ASKER
No, I'm a noob to asp.net.
Is this subroutine supposed to return the date difference for every line on the grid or just for one item?
FtB
FtB
ASKER
Every line.
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
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
ASKER
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.
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.
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
AND DateDiff('d', Now, objDataSet![Jrnl Date]) ORDER BY ControlNo ASC"
and replace objDataSet![Jrnl Date] with the name of the date field.
FtB
ASKER
Ok I made the changes to the select and got this error:
System.Data.SqlClient.SqlE xception: 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.
System.Data.SqlClient.SqlE
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.
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
"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
ASKER
Here's what I get with that select statement:
Exception Details: System.Data.SqlClient.SqlE xception: 'Now' is not a recognized function name.
Source Error:
Line 275: New SqlCommand(SelectCommand1, myConnection)
Line 276: myConnection.Open()
Line 277: Adapter.Fill(objDataSet,"a ccounting" )
Line 278:
Line 279: Adapter.SelectCommand = _
Source File: c:\inetpub\wwwroot\aspx\ne w\omg2.asp x 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.SqlE xception: Invalid parameter 1 specified for datediff.
Exception Details: System.Data.SqlClient.SqlE
Source Error:
Line 275: New SqlCommand(SelectCommand1,
Line 276: myConnection.Open()
Line 277: Adapter.Fill(objDataSet,"a
Line 278:
Line 279: Adapter.SelectCommand = _
Source File: c:\inetpub\wwwroot\aspx\ne
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.SqlE
Try using Date() instead of Now().
Also, test this in your DBMS before trying it in your page.
FtB
Also, test this in your DBMS before trying it in your page.
FtB
ASKER
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.
'Date' is not a recognized function name.
What kind of database are you using?
FtB
FtB
ASKER
MsSql 7.0
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(),tblSent Items.datD ate) as numDaysDifference FROM tblSentItems
FtB
For example, this query works fine on my database:
SELECT tblSentItems.datDate, datediff('d',Now(),tblSent
FtB
ASKER
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.
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.
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
FtB
ASKER
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"!
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"!
ASKER
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?
but this doesn't work
looking at the examples here
www.w3schools.com/vbscript/func_datediff.asp
Why doesn't this work?
I just took a look, and I think that:
today()
is the funciton that you need.
FtB
today()
is the funciton that you need.
FtB
The problem is that date functions are handled differently by the various DBMSs. I think that using today() should do it.
FtB
FtB
ASKER
woo
SELECT *, DateDiff("d",GetDate(),"Jr nl 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.SqlE xception: Invalid parameter 1 specified for datediff.
so close!
SELECT *, DateDiff("d",GetDate(),"Jr
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.SqlE
so close!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YES
That's it!
Double quotes....See I knew it was something easy :)
Thanks FtB!!
That's it!
Double quotes....See I knew it was something easy :)
Thanks FtB!!
Glad to have helped and am sorry that it took so long.
FtB
FtB
datTemp = objDataSet![Jrnl Date]
Date1 = DateDiff("d", Now(), datTemp)
FtB