SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    recordset with query- a real puzzler!

    Code:
    sqlStr="SELECT users.userID,....cut and pasted from access sql view;"
    connection
    rsUserData.open sqlStr,con1,3,3
    Code:
    connection
    rsUserData.open "queryName" ,con1,3,3
    These two ways of drawing a recordset from an access database should
    be identical, although the top way a bit faster.

    I want to use the first way because I need to pass a parameter
    to the WHERE clause from the asp page.

    The problem is that the first way draws an error on the line where
    the recordset is opened. And its an unspecified error- the worse kind!

    Error Type:
    (0x80004005)
    Unspecified error
    /matifone/mat_asp/personal.asp, line 39

    Any ideas as to why an exact cut and pasted query that
    works in access, and works when an asp page refers to
    it by name, would not work when it is given as a parameter
    to rs.open?

    Alternatively, is there a way of passing my parameter to
    the query even though I am calling it by name in
    rs.open?


    Hope this is an interesting question to somebody.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Did you do a display of the strSQL value right before the execution? You might not be executing what you think you are....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes I tried that and was able to cut and paste from the browser into the access sql view where the query worked fine. But off the asp page, I keep getting the same error. When I simplified the query It worked.

    So what are we saying here, that the server software interpreting the query for access can only handle certain types of very simple queries, without too many fancy joins...?


    Here's the connection string. Maybe this will help.

    Code:
    	
    			set con1=server.CreateObject("adodb.connection")
    			con1.open "provider=microsoft.jet.oledb.4.0;data source=c:\db\db002.mdb"
    Anyway, If you recall David, I had a similar problem in the past with iis not being able to perform a query on access that worked fine in access itself. On that occasion, it was the actuall internet server that was failing to do the work. This time the problem was on my local windows 2k running iis. After much frustration, I uploaded the script to the internet server and guess what, it worked fine. So I should be happy. But I don't like never knowing whether thinkgs are going to work on this server or that.

    Any ideas what might be my problem here?
    has anyone else had similar problems?

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I cannot think of why the cut'n'paste doesn't work, but you can indeed pass parameters to the stored query, which will allow you to sidestep the problem for the moment.

    Here's a little wrapper function that I've cobbled together for you:
    Code:
    Function ExecuteCommand(byref Conn, byval CommandText)
    	Dim DBCommand,DBRS
    
    	'create the command and recordset objects
    	set DBCommand = Server.CreateObject("ADODB.Command")
    	set DBRS = Server.CreateObject("ADODB.Recordset")
    	
    	'assign the connection object
    	Set DBCommand.ActiveConnection = Conn
    
    	'assign the command text
    	DBCommand.CommandText = CommandText
    	
    	'perform query
    	Set DBRS = DBCommand.Execute
    	
    	'kill the command object
    	Set DBCommand = Nothing
    	
    	'return RecordSet
    	Set ExecuteCommand = DBRS
    End Function
    Example:
    Code:
    ExecuteCommand(Conn,"[GetCustomersByName] 'M@rco'"
    i.e. the general format is "[StoredQueryName] 'ParamValue1','ParamValue2','ParamValue3'..."

    The names of the parameters do not need to be specified, but they do need to be supplied in the order used in the stored query.


    M@rco
    Last edited by M@rco; Jun 24, 2002 at 01:24.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot Marco.

    I'm going to give that a try. I might get back to you with some more questions in a few days.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •