0

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.

user692942
  • 16,398
  • 7
  • 76
  • 175
adgoodso23
  • 27
  • 1
  • 8
  • 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
  • Do you have the full detail of the error? – user692942 Nov 06 '14 at 20:11
  • 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

2 Answers2

0

Loose the second "where":

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

==>

"Select * from ads where county = '" & selcounty & "' and position='1'"
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
0

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.

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.

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • 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