Datetime field - MS Access vs SQL Server

Hi all,
We were using MS Access as database and VB as frontend. Now we need
to support the database SQL Server also. We have following code which
works fine for MS Access.
For i = 0 To rsRecordset.Fields.Count - 1
Set objField = rsRecordset.Fields(i)
If (objField.Attributes And adFldIsNullable) =
adFldIsNullable Then
Select Case objField.Type
case adDate, adDBDate, adDBTime, adDBTimeStamp
If IsNull(objField.Value) Then
objField.Value = "#1/1/1900#"
--(1)
End If
end select
end if
next i
The select statement for that recordset is like this,
"Select max(Result_Date) as max_date from TestTable"
It was working fine with MS Access.
While executing statement(1) in SQL Server, it is throwing the error
"Multiple-step operation generated errors. Check each status value." I
found that the problem is using "max" function in the query(for that
field).
Can any one help for a work around for the above code to work in this
scenario for SQL Server.
Thanks in Advance.
Swapna.At a quick glance i would say get rid of the pound (#) signs in the date
http://sqlservercode.blogspot.com/
"swapna_munukoti@.yahoo.co.in" wrote:

> Hi all,
> We were using MS Access as database and VB as frontend. Now we need
> to support the database SQL Server also. We have following code which
> works fine for MS Access.
> For i = 0 To rsRecordset.Fields.Count - 1
> Set objField = rsRecordset.Fields(i)
> If (objField.Attributes And adFldIsNullable) =
> adFldIsNullable Then
> Select Case objField.Type
> case adDate, adDBDate, adDBTime, adDBTimeStamp
> If IsNull(objField.Value) Then
> objField.Value = "#1/1/1900#"
> --(1)
> End If
> end select
> end if
> next i
> The select statement for that recordset is like this,
> "Select max(Result_Date) as max_date from TestTable"
> It was working fine with MS Access.
> While executing statement(1) in SQL Server, it is throwing the error
> "Multiple-step operation generated errors. Check each status value." I
> found that the problem is using "max" function in the query(for that
> field).
> Can any one help for a work around for the above code to work in this
> scenario for SQL Server.
> Thanks in Advance.
> Swapna.
>|||> objField.Value = "#1/1/1900#"
How about
objField.Value = CDate("1900-01-01")|||All these things are working if the fleld is a direct one, as in this
query, "Select Result_Date as max_date from TestTable".
None of the above suggestions are working if the query has any field
with function like max() in it.|||You're trying to UPDATE a recordset that uses aggregates?
Have you considered using an UPDATE statement or a stored procedure?
<swapna_munukoti@.yahoo.co.in> wrote in message
news:1127364048.999418.224150@.g43g2000cwa.googlegroups.com...
> All these things are working if the fleld is a direct one, as in this
> query, "Select Result_Date as max_date from TestTable".
> None of the above suggestions are working if the query has any field
> with function like max() in it.
>|||<swapna_munukoti@.yahoo.co.in> wrote in message
news:1127364048.999418.224150@.g43g2000cwa.googlegroups.com...
> All these things are working if the fleld is a direct one, as in this
> query, "Select Result_Date as max_date from TestTable".
> None of the above suggestions are working if the query has any field
> with function like max() in it.
You cannot update a derived field of any sort, values returned by aggregate
functions included.
I'd be very much surprised to find that this truly worked with Jet -- I'd
easily believe it neglected to throw an error, but what row would it update?
-Mark