trouble running query on Access DB in asp classic

Question

I'm trying to run a query on my database and I keep getting an error 80004005 on the execute but do not know why?

Set adconn = Server.CreateObject("ADODB.Connection") 
adconn.Open "justlistit_changes"

Set sqlgetad1 = Server.CreateObject("ADODB.Command")
sqlgetad1.ActiveConnection = adconn
sqlgetad1.commandtext = "Select * from ads where county = '" & selcounty & "' and where position='1'"
sqlgetad1.Prepared = true
sqlgetad1.execute
adconn.Close

The query works fine when I just use the county parameter but will not work once I try to use position anywhere in the query, even just by itself. I have tried hardcoding and using variables but nothing works, please help.

what sort of field is position? Try `position=1` rather than `position='1'` — John, Nov 06 '14 at 20:03
So you use the `ADODB.Command` object but don't bother to parametrise your query? What is the point in that? — user692942, Nov 06 '14 at 20:06
I was having trouble running other queries when using parameters so i scrapped them all together. I did not build this site, I just took it over and I have never done classic asp before — adgoodso23, Nov 06 '14 at 20:11
@adgoodso23 That's your problem right there then. `ADODB.Command` is the correct approach, your predecessor was on the right track. As for `"Select * from ads where county = '" & selcounty` this is documented bad practice and leaves you open to SQL Injection. — user692942, Nov 06 '14 at 20:13
What is this line doing? - `adconn.Open "justlistit_changes"` The `ADODB.Connection` `.Open()` method expects a valid connection string which this is not. My guess is your `justlistit_changes` is a variable that defines a connection string not a literal string `"justlistit_changes"`. — user692942, Nov 06 '14 at 20:40

score 0 · Answer 1 · answered Nov 06 '14 at 18:31

0

Loose the second "where":

"Select * from ads where county = '" & selcounty & "' and where position='1'"

==>

"Select * from ads where county = '" & selcounty & "' and position='1'"

answered Nov 06 '14 at 18:31

Ekkehard.Horner

38,498
2
45
96

score 0 · Accepted Answer · edited May 23 '17 at 12:27

Update

The cause is position is a ANSI 92 reserved word in Microsoft Access - see ACC2002: Error with ANSI-92 Reserved Words When SQL Server Compatibility Syntax (ANSI 92) Is Enabled

To workaround this simply encapsulate your field in square brackets [] (Updated my example below).
[position] = ?

Update

Looks like the error your reporting has nothing to do with your code.

80004005 Errors

Your using ADODB.Command but setting variables in the CommandText which bypasses the protection using ADODB.Command gives you when executing Parametrised Queries.

Try this;

Dim sqlgetad1, rs

Set sqlgetad1 = Server.CreateObject("ADODB.Command")
With sqlgetad1
  'Assuming justlistit_changes is your connection string variable.
  .ActiveConnection = justlistit_changes
  .CommandType = adCmdText
  '******** Avoid any clashes with ANSI 92 reserved words ********
  .CommandText = "Select * from ads where [county] = ? and [position] = ?"
  .Prepared = true
  .Parameters.Append(.CreateParameter("@county", adVarChar, adParamInput, 100))
  'Assuming your position field is actually a numeric value.
  .Parameters.Append(.CreateParameter("@position", adInteger, adParamInput, 4))
  .Parameters("@county").Value = selcounty
  .Parameters("@position").Value = 1
  Set rs = .Execute()
End With

If your position field is not an numeric replace with

.Parameters.Append(.CreateParameter("@position", adVarChar, adParamInput, 10))
.Parameters("@position").Value = "1"

If you have trouble with the ADO named constants like adCmdText for example have a look at how to use METADATA to import DLL constants.

Ive tried this and i still get the error '80004005' /wwwroot/display.asp, line 154. line 154 being the Execute command — adgoodso23, Nov 06 '14 at 20:37
Its something to do with the position field in the database. I made a new field called 'adpos' and its working fine. I guess I'll just use the new field. — adgoodso23, Nov 06 '14 at 20:40
@adgoodso23 How can it be working, `adconn.Open "justlistit_changes"` isn't a valid connection string, what code are you actually using? — user692942, Nov 06 '14 at 20:43
@adgoodso23 Scrap that `POSITION` is a ANSI 92 reserved word that will be the issue. See [Update](http://stackoverflow.com/revisions/26788713/4). — user692942, Nov 06 '14 at 20:50

trouble running query on Access DB in asp classic

2 Answers2