Filter error: Get_RS_GetCount

[Nancy Steinmann]

I am getting an Get_RS_GetCount error when I try to filter on a name field in my grid.

I am displaying a database table that contains two employee ids (a User id and a
Designator id). I want to display the person's names as well as their ids. I also want to be
able to filter by the name, rather than the id.

My sql statement is looking up the names for both ids from the same table. So in my SQL
stmt, I aliased the names (since the field names were identical). I think it is this aliasing
that is causing the Get_RS_GetCount error.

I tried adding a dbGetCountSQL parameter, but this did not remove the error.

Is there a better way to display the names and be able to filter them and not get the error?

The error is as follows:
Error # (Get_RS_GetCount - After open mySQL) = 80040E14(-2147217900)
Description = ORA-00904: invalid column name
Source = Microsoft OLE DB Provider for Oracle
SQL State =
Native Error = 0

Error # (Get_RS_GetCount - After open mySQL) = 80040E14(-2147217900)
Description = One or more errors occurred during processing of command.
Source = Microsoft OLE DB Provider for Oracle
SQL State =
Native Error = 0

My code is as follows:

Dim oAspdb 'grid object
Dim sSQL 'sql stmt
dim lRecs 'grid record count
dim iUnit 'grid unit parameter
Dim sNM 'grid name map string
iUnit = CInt(Session("GridUnit&#34 & "4&#34

Set oAspdb = Server.CreateObject("Asp.db&#34
oAspdb.dbDBType = "ORACLE"
oAspdb.dbUnit = iUnit 'unique number for this grid on this page.
oAspdb.dbDSN = Session("gConnStr&#34 'oledb connect
sSQL = "SELECT A.NTD_BEMSID, B.HC_EMP_LAST_NAME AS DESLNAME, B.HC_EMP_FIRST_NAME AS DESFNAME, B.HC_EMP_MID_INITL AS DESMNAME, " _
& "A.FK_NTU_BEMSID, C.HC_EMP_LAST_NAME AS USERLNAME, C.HC_EMP_FIRST_NAME AS USERFNAME, C.HC_EMP_MID_INITL AS USERMNAME, " _
& "A.NTD_LAST_UPDATE_UID, A.NTD_LAST_UPDATE_TIMESTAMP " _
& "FROM NWT_DESIGNATORS A, RPP_EMPLOYEE B, RPP_EMPLOYEE C " _
& "WHERE A.NTD_BEMSID = B.HC_BEMSID(+) AND " _
& "A.FK_NTU_BEMSID = C.HC_BEMSID(+) " _
& "ORDER BY B.HC_EMP_LAST_NAME, B.HC_EMP_FIRST_NAME "
oAspdb.dbsql = sSQL 'sql stmt for contents
oAspdb.dbButtonAnchor = false
oAspdb.dbColor = "white,navy,navy,black,lightsteelblue"
oAspdb.dbCountSQL = "SELECT COUNT(*) FROM NWT_DESIGNATORS"
oAspdb.dbCSVName="NWTDesignatorsTable.csv" 'name of download file.
oAspdb.dbFilterHideFlds = "NTD_LAST_UPDATE_TIMESTAMP"
oAspdb.dbFilterParams = "CaseSensitive=False"
oAspdb.dbFormDisplayFlds = -1 'turn off rec display when click on record.
oAspdb.dbGridColSort = true 'turn off sorting when click on col header.
oAspdb.dbGridInc = "10" 'number of rows per page in grid
oAspdb.dbGridIndex = false 'turn off numbered hyperlink row.
oAspdb.dbGridTableTag="width='100%' BORDER='1'" 'Set table border
oAspdb.dbImageDir="images/ASPDBGrid/" 'directory for button images
mc = "NTD_LAST_UPDATE_TIMESTAMP,,Format=[DD-MMM-YYYY hh:mm:ss AMPM]"
oAspdb.dbMagicCell = mc
oAspdb.dbMode = "dual-horiz" 'display grid, form, etc.
sNM = &#34;NTD_BEMSID,<font color=white>Des. BEMSID</font>;DESLNAME,<font color=white>Des. LName</font>;DESFNAME,<font color=white>Des. FName</font>;DESMNAME,<font color=white>Des. MI</font>;FK_NTU_BEMSID,<font color=white>User BEMSID</font>;USERLNAME,<font color=white>User LName</font>;USERFNAME,<font color=white>User FName</font>;USERMNAME,<font color=white>User MI</font>;NTD_LAST_UPDATE_UID,<font color=white>LU By</font>;NTD_LAST_UPDATE_TIMESTAMP,<font color=white>LU Timestamp</font>&#34;
oAspdb.dbNameMap = sNM &#39;override grid hdr names and font color. This
oAspdb.dbNavigation=&#34;bottom&#34; &#39;pos of nav bar
oAspdb.dbNavigationItem=&#34;top, bottom, next, prev, filter, download, add&#34; &#39;contents of nav bar
oAspdb.dbNavigationIcon=&#34;std&#34; &#39;define graphic button file names. STD means use standard names hardcoded in grid.
oAspdb.dbSuppressMsg = true &#39;suppresses some grid error messages.
oAspdb.dbOptions=&#34;(,HeaderFontTag=Face=Arial Size=1;CellFontTag=Face=Arial size=1;NavGoToFldIndex=1&#34;
s = zHead & &#34;,<center><font face=&#39;Arial&#39; color=navy size=2><b>To move directly to a record enter a few letters of the Designator Last Name in the box next to the Go button and press Go.</b></font></center>;&#34;
oAspdb.dbUserLocalText = s
&#39;Edit parameters
oAspdb.dbButtonsOnOff = &#34;FilterDropOff,EditDropOff&#34;
oAspdb.dbEditAddROFlds = &#34;NTD_LAST_UPDATE_UID, NTD_LAST_UPDATE_TIMESTAMP&#34;
oAspdb.dbEditUpdateROFlds = &#34;NTD_LAST_UPDATE_UID, NTD_LAST_UPDATE_TIMESTAMP&#34;
oAspdb.dbEditFlds=&#34;(;,)NTD_BEMSID, FK_NTU_BEMSID, NTD_LAST_UPDATE_UID[&#34; & Session(&#34;NWT_USER&#34 & &#34;|&#34; & Session(&#34;NWT_USER&#34 & &#34;], NTD_LAST_UPDATE_TIMESTAMP[timestamp(dd-mmm-yyyy hh:mm:ss ampm)|timestamp(dd-mmm-yyyy hh:mm:ss ampm)]&#34;
oAspdb.dbEditParams = &#34;(;,)TableName=NWT_DESIGNATORS,BookMarkFlds=NT D_BEMSID+FK_NTU_BEMSID,&#34; _
& &#34;DeleteIcon=True,EditIconsLayout=Left&#34;
oAspdb.dbEditDropFlds = &#34;(;~/)NTD_BEMSID~~~~SELECT HC_BEMSID, (HC_BEMSID || &#39;: &#39;||HC_EMP_LAST_NAME || &#39;, &#39;||HC_EMP_FIRST_NAME||&#39; &#39;||HC_EMP_MID_INITL) AS EMPNAME &#34;_
& &#34;FROM RPP_EMPLOYEE ORDER BY HC_EMP_LAST_NAME, HC_EMP_FIRST_NAME~~NoBlank;&#34; _
& &#34;FK_NTU_BEMSID~~~~SELECT HC_BEMSID, (HC_BEMSID || &#39;: &#39;||HC_EMP_LAST_NAME || &#39;, &#39;||HC_EMP_FIRST_NAME||&#39; &#39;||HC_EMP_MID_INITL) AS EMPNAME &#34;_
& &#34;FROM RPP_EMPLOYEE ORDER BY HC_EMP_LAST_NAME, HC_EMP_FIRST_NAME~~NoBlank;&#34;
oAspdb.dbDebug = 102
oAspdb.aspDB &#39;draw grid
lRECS = oAspdb.dbRecordCount &#39;get record count from grid

[Nancy Steinmann]

Tried adding original name of field to namemap, as follows:
sNM = &#34;NTD_BEMSID,<font color=white>Des. BEMSID</font>;DESLNAME,<font color=white>Des. LName</font>,,B.HC_EMP_LAST_NAME;DESFNAME,<font color=white>Des. FName</font>;DESMNAME,<font color=white>Des. MI</font>;FK_NTU_BEMSID,<font color=white>User BEMSID</font>;USERLNAME,<font color=white>User LName</font>,,C.HC_EMP_LAST_NAME;USERFNAME,<font color=white>User FName</font>;USERMNAME,<font color=white>User MI</font>;NTD_LAST_UPDATE_UID,<font color=white>LU By</font>;NTD_LAST_UPDATE_TIMESTAMP,<font color=white>LU Timestamp</font>&#34;
oAspdb.dbNameMap = sNM &#39;override grid hdr names and font color. This

Note that original field name only added to DESLNAME and USERLNAME because I changed
code to hide both sets of first names and middle names on the filter screen.

Still getting Get_RS_GetCount error. What else can I try??

Thanks
Nancy


------------
Frank Kwong at 3/15/01 6:03:14 PM

Goto ->

http://www.usintertech.com/aspdb/casestudy/casestudy.shtm

and look at case 19 & 23

FK



------------
Nancy Steinmann at 3/14/01 7:45:41 PM

I am getting an Get_RS_GetCount error when I try to filter on a name field in my grid.

I am displaying a database table that contains two employee ids (a User id and a
Designator id). I want to display the person&#39;s names as well as their ids. I also want to be
able to filter by the name, rather than the id.

My sql statement is looking up the names for both ids from the same table. So in my SQL
stmt, I aliased the names (since the field names were identical). I think it is this aliasing
that is causing the Get_RS_GetCount error.

I tried adding a dbGetCountSQL parameter, but this did not remove the error.

Is there a better way to display the names and be able to filter them and not get the error?

The error is as follows:
Error # (Get_RS_GetCount - After open mySQL) = 80040E14(-2147217900)
Description = ORA-00904: invalid column name
Source = Microsoft OLE DB Provider for Oracle
SQL State =
Native Error = 0

Error # (Get_RS_GetCount - After open mySQL) = 80040E14(-2147217900)
Description = One or more errors occurred during processing of command.
Source = Microsoft OLE DB Provider for Oracle
SQL State =
Native Error = 0

My code is as follows:

Dim oAspdb &#39;grid object
Dim sSQL &#39;sql stmt
dim lRecs &#39;grid record count
dim iUnit &#39;grid unit parameter
Dim sNM &#39;grid name map string
iUnit = CInt(Session(&#34;GridUnit&#34 & &#34;4&#34

Set oAspdb = Server.CreateObject(&#34;Asp.db&#34
oAspdb.dbDBType = &#34;ORACLE&#34;
oAspdb.dbUnit = iUnit &#39;unique number for this grid on this page.
oAspdb.dbDSN = Session(&#34;gConnStr&#34 &#39;oledb connect
sSQL = &#34;SELECT A.NTD_BEMSID, B.HC_EMP_LAST_NAME AS DESLNAME, B.HC_EMP_FIRST_NAME AS DESFNAME, B.HC_EMP_MID_INITL AS DESMNAME, &#34; _
& &#34;A.FK_NTU_BEMSID, C.HC_EMP_LAST_NAME AS USERLNAME, C.HC_EMP_FIRST_NAME AS USERFNAME, C.HC_EMP_MID_INITL AS USERMNAME, &#34; _
& &#34;A.NTD_LAST_UPDATE_UID, A.NTD_LAST_UPDATE_TIMESTAMP &#34; _
& &#34;FROM NWT_DESIGNATORS A, RPP_EMPLOYEE B, RPP_EMPLOYEE C &#34; _
& &#34;WHERE A.NTD_BEMSID = B.HC_BEMSID(+) AND &#34; _
& &#34;A.FK_NTU_BEMSID = C.HC_BEMSID(+) &#34; _
& &#34;ORDER BY B.HC_EMP_LAST_NAME, B.HC_EMP_FIRST_NAME &#34;
oAspdb.dbsql = sSQL &#39;sql stmt for contents
oAspdb.dbButtonAnchor = false
oAspdb.dbColor = &#34;white,navy,navy,black,lightsteelblue&#34;
oAspdb.dbCountSQL = &#34;SELECT COUNT(*) FROM NWT_DESIGNATORS&#34;
oAspdb.dbCSVName=&#34;NWTDesignatorsTable.csv&#34; &#39;name of download file.
oAspdb.dbFilterHideFlds = &#34;NTD_LAST_UPDATE_TIMESTAMP&#34;
oAspdb.dbFilterParams = &#34;CaseSensitive=False&#34;
oAspdb.dbFormDisplayFlds = -1 &#39;turn off rec display when click on record.
oAspdb.dbGridColSort = true &#39;turn off sorting when click on col header.
oAspdb.dbGridInc = &#34;10&#34; &#39;number of rows per page in grid
oAspdb.dbGridIndex = false &#39;turn off numbered hyperlink row.
oAspdb.dbGridTableTag=&#34;width=&#39;100%&#39; BORDER=&#39;1&#39;&#34; &#39;Set table border
oAspdb.dbImageDir=&#34;images/ASPDBGrid/&#34; &#39;directory for button images
mc = &#34;NTD_LAST_UPDATE_TIMESTAMP,,Format=[DD-MMM-YYYY hh:mm:ss AMPM]&#34;
oAspdb.dbMagicCell = mc
oAspdb.dbMode = &#34;dual-horiz&#34; &#39;display grid, form, etc.
sNM = &#34;NTD_BEMSID,<font color=white>Des. BEMSID</font>;DESLNAME,<font color=white>Des. LName</font>;DESFNAME,<font color=white>Des. FName</font>;DESMNAME,<font color=white>Des. MI</font>;FK_NTU_BEMSID,<font color=white>User BEMSID</font>;USERLNAME,<font color=white>User LName</font>;USERFNAME,<font color=white>User FName</font>;USERMNAME,<font color=white>User MI</font>;NTD_LAST_UPDATE_UID,<font color=white>LU By</font>;NTD_LAST_UPDATE_TIMESTAMP,<font color=white>LU Timestamp</font>&#34;
oAspdb.dbNameMap = sNM &#39;override grid hdr names and font color. This
oAspdb.dbNavigation=&#34;bottom&#34; &#39;pos of nav bar
oAspdb.dbNavigationItem=&#34;top, bottom, next, prev, filter, download, add&#34; &#39;contents of nav bar
oAspdb.dbNavigationIcon=&#34;std&#34; &#39;define graphic button file names. STD means use standard names hardcoded in grid.
oAspdb.dbSuppressMsg = true &#39;suppresses some grid error messages.
oAspdb.dbOptions=&#34;(,HeaderFontTag=Face=Arial Size=1;CellFontTag=Face=Arial size=1;NavGoToFldIndex=1&#34;
s = zHead & &#34;,<center><font face=&#39;Arial&#39; color=navy size=2><b>To move directly to a record enter a few letters of the Designator Last Name in the box next to the Go button and press Go.</b></font></center>;&#34;
oAspdb.dbUserLocalText = s
&#39;Edit parameters
oAspdb.dbButtonsOnOff = &#34;FilterDropOff,EditDropOff&#34;
oAspdb.dbEditAddROFlds = &#34;NTD_LAST_UPDATE_UID, NTD_LAST_UPDATE_TIMESTAMP&#34;
oAspdb.dbEditUpdateROFlds = &#34;NTD_LAST_UPDATE_UID, NTD_LAST_UPDATE_TIMESTAMP&#34;
oAspdb.dbEditFlds=&#34;(;,)NTD_BEMSID, FK_NTU_BEMSID, NTD_LAST_UPDATE_UID[&#34; & Session(&#34;NWT_USER&#34 & &#34;|&#34; & Session(&#34;NWT_USER&#34 & &#34;], NTD_LAST_UPDATE_TIMESTAMP[timestamp(dd-mmm-yyyy hh:mm:ss ampm)|timestamp(dd-mmm-yyyy hh:mm:ss ampm)]&#34;
oAspdb.dbEditParams = &#34;(;,)TableName=NWT_DESIGNATORS,BookMarkFlds=NT D_BEMSID+FK_NTU_BEMSID,&#34; _
& &#34;DeleteIcon=True,EditIconsLayout=Left&#34;
oAspdb.dbEditDropFlds = &#34;(;~/)NTD_BEMSID~~~~SELECT HC_BEMSID, (HC_BEMSID || &#39;: &#39;||HC_EMP_LAST_NAME || &#39;, &#39;||HC_EMP_FIRST_NAME||&#39; &#39;||HC_EMP_MID_INITL) AS EMPNAME &#34;_
& &#34;FROM RPP_EMPLOYEE ORDER BY HC_EMP_LAST_NAME, HC_EMP_FIRST_NAME~~NoBlank;&#34; _
& &#34;FK_NTU_BEMSID~~~~SELECT HC_BEMSID, (HC_BEMSID || &#39;: &#39;||HC_EMP_LAST_NAME || &#39;, &#39;||HC_EMP_FIRST_NAME||&#39; &#39;||HC_EMP_MID_INITL) AS EMPNAME &#34;_
& &#34;FROM RPP_EMPLOYEE ORDER BY HC_EMP_LAST_NAME, HC_EMP_FIRST_NAME~~NoBlank;&#34;
oAspdb.dbDebug = 102
oAspdb.aspDB &#39;draw grid
lRECS = oAspdb.dbRecordCount &#39;get record count from grid