Access2000/ASP Query problem

[fluxdemon]

I am having a problem pulling data out of an Access2000 file with ASP. The statment I have been working with is:

select distinct([codeview].section)
from [codeview]
order by [codeview].section

which works fine in the SQL window inside Access but throws an unspecified error when launched from ASP. The only luck I have found some info that says I can't use distinct on a field, only a row. Is there a way I could rewrite the statement to work?

 

[winston1983]

Have you tried it with Distinctrow?

Regards,


Lewis Harvey
lewis@lharvey.com

 

[fluxdemon]

I just tried that and I get all data in Access and unspecified error in ASP.

 

[winston1983]

can you post your asp code for me? or at least the bit which opens the connection and sets the sql and whn you use the results please.

Regards,


Lewis Harvey
lewis@lharvey.com

 

[fluxdemon]

dim sql,rs,conn

set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

openDatabase(conn)
sqlSelect conn,"select distinctrow(codeview.section) from codeview",rs
writeCodeViewMenu(rs)
closeDatabase(conn)

sub sqlSelect(conn,sql,rs)
set rs = conn.execute(sql)
end sub

function openDatabase(conn)
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("wp.mdb") & ";"
conn.Open
end function

sub closeDatabase(conn)
conn.close
set conn=nothing
end sub

 

[winston1983]

When you use your sqlSelect() funtion, you have not put brakcets around the properties.

Try
sqlSelect(conn,"select distinctrow(codeview.section) from codeview",rs)

Does this help?


Regards,


Lewis Harvey
lewis@lharvey.com

 

[fluxdemon]

It complained that I can't use parentheses on a sub.

 

[winston1983]

It doesn't like the "". In the sub try initilising the sql first as a string, the placing it in the sub, e.g.

sub sqlSelect(conn,sql,rs)
set rs = conn.execute(sql)
end sub


openDatabase(conn)
dim sqlsate = "select distinctrow(codeview.section) from codeview"
sqlSelect(conn,sqlstate,rs)
writeCodeViewMenu(rs)
closeDatabase(conn)


Regards,


Lewis Harvey
lewis@lharvey.com

 

[fluxdemon]

It doesn't like the () on a sub. Even if the Sub is declared as a Function. Ran into that one a while back.

 

[fluxdemon]

http://www.aspemporium.com/aspEmporium/tutorials/dontUseMSAccess.asp

- gives a little insight.

 

[Quehay]

Distinct row is not a function, it's a SQL directive so just get rid of the parentheses around the column and this should go. *Remember that Distinct Row is Access-specific if you ever migrate to another Db.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[fluxdemon]

Like this? select distinct codeview.section from codeview

This gives me an unspecified error message.

 

[fluxdemon]

I've deconstructed my functions to give a clearer view

set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("wp.mdb") & ";"
conn.Open
sql="select distinct codeview.section from codeview"
set rs = conn.execute(sql)
writeCodeViewMenu(rs)
conn.close
set conn=nothing

 

[Quehay]

If you've got direct access to the mdb file trying running your SQL string in the query GUI just to make sure it works.

Just to make sure your connection is open...

conn.Open
response.write (conn.state) '1=open

If conn.Errors.Count <> 0 or Err <> 0 Then
response.write ("connection errors")
End If
set rs = conn.execute(sql)
response.write (rs.state) '1=open

writeCodeViewMenu(rs)
conn.close



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[fluxdemon]

Some progress! I was looking at a previous version and found I was using the nonSelect statement, but it changed nothing. I changed what you sent me to show what is going wrong. The odd thing is this very sequence works for a query that pulls all records from a table. Here what I have now.

dim sql,rs,conn

set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("wp.mdb") & ";"
conn.Open
response.write (conn.state & "<br>") '1=open
response.flush

If conn.Errors.Count <> 0 or Err <> 0 Then
response.write (conn.Errors & "<br>")
response.flush
End If

rs.Open "select section from codeview", conn

response.write (rs.state) '1=open

gives me:

1

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment

/scott/codelisting.asp, line 15

 

[Quehay]

I never remember connection string syntax--here are two good places to check that:

http://www.able-consulting.com

http://www.connectionstrings.com

* for clarity I'd put path into a variable (I'm assuming you populate sql variable and just haven't pasted it):

dim strPath

strPath = server.mappath

you could make this a much more modular procedure by passing in parameters for Rst, StrSQL, strPath--then you can use it for other rst (close and destroy rs objects in calling functions)

Sub GetRecordset(Rst,strSQl,strPath)

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[fluxdemon]

Still gives me an unspecified error

dim sql,rs,conn
dim path

sql="select section from codeview"
path=server.mappath("wp.mdb")

set conn = Server.CreateObject("ADODB.Connection")
set newrs = Server.CreateObject("ADODB.Recordset")

conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & path & ";"
conn.Open
response.write (conn.state) '1=open
response.flush


If conn.Errors.Count <> 0 or Err <> 0 Then
response.write (conn.Errors & "<br>")
response.flush
End If

newrs.Open sql, conn
response.write (newrs.state) '1=open
response.flush

writeCodeViewMenu(newrs)
conn.close
set conn=nothing

1
Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment

/scott/codelisting.asp, line 20