Trouble selecting records from Access database!

Results 1 to 4 of 4

Thread: Trouble selecting records from Access database!

  1. #1
    HerrDeacon Guest

    Default Trouble selecting records from Access database!

    I am having a bit of trouble displaying records from an Access database. Below is the code used to connect to the DB and the SQL statement used, can anyone see any problems with this setup? I am trying to populate a drop-down box with records returned from the select statement. I am using PWS (and yes it is running) and have set up the ODBC system DSN for the database (it works OK for an update).<BR><BR>&#060;%<BR>set conn = server.createobject("adodb.connection") <BR> "DSN=myDatabase" <BR><BR>SQL = "SELECT * FROM news ORDER BY adate DESC"<BR>Set rs = Conn.Execute(SQL)<BR>%&#062;<BR><BR>&#060;form name="itemform" action="deletenews.asp" method="post"&#062;<BR> &#060;SELECT NAME="articles"&#062;<BR> &#060;OPTION selected value="0"&#062;Choose an article to delete...&#060;/OPTION&#062;<BR><BR> &#060;% <BR> do while NOT rs.eof<BR> Title = rs("title")<BR> id = rs("id")<BR> Response.Write("&#060;OPTION value = " & id & "&#062;" & Title & "&#060;/OPTION&#062;")<BR> rs.MoveNext<BR> loop <BR> %&#062; <BR><BR> &#060;/SELECT&#062;<BR> &#060;input type="hidden" name="Action" value="go"&#062;<BR> &#060;input type = "submit" name="submit" value="Delete"&#062;<BR> &#060;/form&#062;<BR><BR>I have verified that the SQL statement is correct using the Query Design tool in Access. I have another page that updates the database and this works with no problems (with the same code to connect to the database) so it seems that I have the ODBC system DSN setup correct for the database.<BR><BR>I did some debugging using Response.write statements and it showed that rs.eof was true (i.e. no records were selected) right after the conn.execute statement eventhough there are plenty of records in the table.<BR><BR>Also, if I move the Title = rs("title") statement to just after the conn.execute(sql) statement (for debugging purposes) I get a "Driver does not support this function" error (ODBC Drivers error &#039 80004005&#039 ). Is this only because there were no records selected?<BR><BR>Any clues would be greatly appreciated because I am getting very frustrated. I have tried every debugging trick I know.<BR><BR>Thanks,<BR>HerrDeacon<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Trouble selecting records from Access database

    The ONLY things I can think of is that either the connection or the SQL query are causing the problems. If that is so, why no errors?<BR><BR>I think I might try replacing these two lines:<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;SQL = "SELECT * FROM news ORDER BY adate DESC"<BR>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = Conn.Execute(SQL)<BR><BR>With these two, instead:<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;Set RS = Server.CreateObject("ADODB.RecordSet")<BR>&nbsp;&n bsp;&nbsp;&nbsp;RS.Open "news",Conn<BR><BR>That is, just open the entire table. This will lose your "ORDER BY", but it might at least help isolate the problem a little more (e.g., if this works, the problem is in the SQL).<BR><BR>The results you get from moving the rs("title") line clearly indicate that somehow, for some reason, the RecordSet is not properly opened. But, again, why no error messages?<BR><BR>You don&#039t possibly have one of those ugly On Error Resume Next statements someplace in the page do you?<BR><BR>

  3. #3 Guest

    Default RE: Trouble selecting records from Access database

    (A) How do you know that your update script works. Is it just that it does not come up with error, or have you actually checked that it successfully updates/inserts records.<BR><BR>(B) Yes, it looks like the script fails when you move the Title = rs("title") statement to just after the conn.execute(sql) statement, because RS.EOF is true. It does look like your SQL is correct, or the Conn.Execute(SQL) would cause an error, if you had a syntax problem or misspelled field names. <BR><BR>I do not know what to suggest, except to remove the order by part of the query an see if that works. Also add a "Response.Write SQL" statement to verify that the string is what you expected, and take copy this result from your web page and paste it in the Query Design tool in Access and see what you get.<BR><BR>Otherwise post your update script so I can check it.<BR>

  4. #4
    HerrDeacon Guest

    Default Problem Fixed, yeehaw!!!

    I have the problem fixed now. The problem was some of the ODBC DLL files were of a different version than the ODBC setup DLL (how this happened I don&#039t know). I decided to delete the system DSN and try and set it up again to make sure that it was not the problem and I got this error message explaining the above mentioned problem with the DLLs. I downloaded the latest Microsoft Data Access Components and this fixed the problem.<BR><BR>Thanks for responding Bill!!!<BR><BR>HerrDeacon

Posting Permissions

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