select statement

kingesk
08-14-2002, 02:29 PM
I have a select statemnt in an ASP page I have been using without any problems when connecting to Access. I have now upgraded my database to SQL Server and I'm getting a syntax error with the "isnull" function.


Erorr Message:


Microsoft OLE DB Provider for SQL Server error '80040e14'

The isnull function requires 2 arguments.

/WorkRequest/RequestViews.asp, line 469


<code>

'fldAssignedTo
If strUser = "All" Then
strUser = " (fldAssignedTo Like '%' or isNull(fldAssignedTo)) "
ElseIF strUser = "Unassigned" Then
strUser = " (isNull(fldAssignedTo)) "
Else
strUser = " (fldAssignedTo = '" & strUser & "') "
End If

'Complet SQL statement
strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where " & strUser

set objRS=server.CreateObject("ADODB.RecordSet")
objRS.Open strSql, objConn, adOpenKeySet

</code>

brillo
08-14-2002, 04:13 PM
Here is the syntax I use for Nulls.

SELECT * FROM MyTable WHERE MyField IS NULL

or

SELECT * FROM MyTable WHERE MyField IS NOT NULL

I hope this helps.

kingesk
08-15-2002, 10:00 AM
Thanks. The "Is Null" worked. I have another similar question. I have another select statemnent that doesn't seem to be working after switching to SQL Server from Access.

Error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '#'.

/WorkRequest/RequestViews.asp, line 475


<code>

strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where ((fldReqDate >= #12/31/69#) and (fldReqDate <= #1/2/2099#))"

</code>

Road Runner
08-15-2002, 10:18 AM
Try


strSql = "Select fldReqNum, fldDescription, fldAssignedTo, fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where ((fldReqDate >= '12/31/69') and (fldReqDate <= '1/2/2099'))"

kingesk
08-15-2002, 10:44 AM
Thanks. That worked perfectly. I don't know why I haven't heard of that. I thought # signs were the standard for dates.

FocusedChaos
08-15-2002, 06:36 PM
"#" sign is in access because access isn't true SQL. It borrows a ton of VBA functions, such as Trim(), Format() and mixes them together with its weaker sql engine.

Also, if your doing a ton of statements like this for date ranges use between to save you from cutting, pasting, and editing everything. At least thats what do, could be that I'm just overly lazy.
strSql = "Select fldReqNum, fldDescription, fldAssignedTo,
fldStatusNotes, fldActivities, fldCompletedInMinutes " & _
"From tblWorkRequest Where fldReqDate BETWEEN '12/31/69' and '1/2/2099'"

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum