Error when trying to retrieve data by user id

[reisende]

I am coming accross an error while trying to select a location from a DBF table based on the user id of the logged in user.

Here is the error I'm getting:
[tt]
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC dBase Driver] Index not found.
[/tt]

It will pull all locations from the table successfully if i just do [tt]SELECT * FROM clinics[/tt], but I get the error when I try to refine my search:

  dim objConnGs, rsFinance, strSQL, sbtNumber, clinicName
  call openDbGs()
  
  set rsFinance = Server.CreateObject("ADODB.Recordset")
  strSQL = "SELECT * FROM clinics WHERE areamgr= '" & strUser & "' ORDER BY clinics.""Desc"""
  
  rsFinance.Open strSQL, objConnGs
  
  do while not rsFinance.Eof
    sbtNumber = rsFinance("SBT_Number")
    clinicName = rsFinance("Desc")
	
    Response.Write("<option value=""S:\Financial Reports\" & sbtNumber & "\" & Year(reportDate) & "\" & MonthName(Month(reportDate)) & "\SIS_" & sbtNumber & ".pdf"">" & clinicName & "</option>")

    rsFinance.MoveNext
  loop

I am using a system DSN pointing to the folder containing the DBF files. I'm just not sure why this won't work.

Thanks in advance.

 

[davejam]

do you need the . and quotes round desc, don't think i ever have!

strSQL = "SELECT * FROM clinics WHERE areamgr= '" & strUser & "' ORDER BY clinics Desc"

on the order by, is clinics a field in the table, if not you need to specify a field from the table I think.

hope that gives you ideas

Cheers

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[reisende]

I needed the double quotes because the guy who built the database used Desc as a field name. The problem is Desc is SQL syntax so When I didn't use the quotes I got a syntax error.

clinics is the table. I have no problems using the ORDER BY with clinics."Desc", that works. The problem is coming from the WHERE clause.

It says the Index is not found, but I don't have an index for Areamgr and am only using it for select criteria.

 

[DreXor]

desc is a sql reserved word for descending, for sortation.

so if you're not ordering your table contents by clinics in a descending manner, but instead are sorting ascending by clinics AND "Desc"ription you'll need to change it to the following :

order by clinics,[Desc]

no quotes around Desc


DreX
aKa - Robert

 

[reisende]

I'll give that a try, but I'm pretty sure it's the WHERE clause causing the problem (I don't know why). I think this is the case because I still get the same error when I remove the ORDER BY.

 

[DreXor]

check the DB and make sure that areamgr isn't a numeric field if it is you'll need to take out the single quotes around struser.

strSQL = "SELECT * FROM clinics WHERE areamgr=" & strUser & " ORDER BY clinics,[Desc]"


DreX
aKa - Robert

 

[reisende]

Nope, it's a VarChar.

I'll post back any results on Monday.

Thanks.

 

[Dynapen]

Good check is to force the page to spit out the SQL before you run it.

Response.Write strSQL
Response.End

This will show you exactly what the text that is going to the DB is. Cut and paste that into the DB SQL Editor,and run it. If it breaks, then you can track it down on the DB side. If it runs, then you know you have a driver or connection issue. Sometimes ODBC (especially through DNS) can get confused as to what the root cause is, and give you back messages that aren't as helpful as they could be.

If you can run the string in the DB, then you can figure out which side of the app the problem is on. That always makes things easier.