Problem with retrieving values in a combo box from a query

Hi,
I am trying to retrieve values from a query in a list box. However, it is
giving me no recordset. Although I get recordset when I query this access
database. Any help for resolution on this is appreciated. Thanks

CODE:
<%
Dim oConn
Dim oRS
Dim vCS
Dim sqltxt

set oConn=server.CreateObject("ADODB.connection")
vCS = "Provider=Microsoft.Jet.OLEDB.3.51"
vCS = vCS & "; Data Source=C:\Sailors.mdb"
oConn.connectionstring = vCS
oConn.Open

set oRS = server.CreateObject("ADODB.recordset")
sqltxt = "SELECT BoatName, BoatClass FROM Boats;"
oRS.Open sqltxt,oConn
%>

<%
Do while not oRS.EOF
response.Write ""
response.Write oRS("BoatClass") & ""
oRS.MoveNext
Loop

%>

<%oRS.Close
Set oRS = nothing %>

Re: Problem with retrieving values in a combo box from a query

Jack wrote:
> Hi,
> I am trying to retrieve values from a query in a list box. However,
> it is giving me no recordset. Although I get recordset when I query
> this access database. Any help for resolution on this is appreciated.
> Thanks
>
> CODE:
> <%
> Dim oConn
> Dim oRS
> Dim vCS
> Dim sqltxt
>
> set oConn=server.CreateObject("ADODB.connection")
> vCS = "Provider=Microsoft.Jet.OLEDB.3.51"

Why 3.51? You're not using a database that's that old are you? Use the
4.0 provider.

> vCS = vCS & "; Data Source=C:\Sailors.mdb"
> oConn.connectionstring = vCS
> oConn.Open
>
> set oRS = server.CreateObject("ADODB.recordset")
> sqltxt = "SELECT BoatName, BoatClass FROM Boats;"
> oRS.Open sqltxt,oConn

I see no major problems here. Do you have an On Error Resume Next
statement anywhere that is masking your errors?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with retrieving values in a combo box from a query

Hi Bob,
I do not have any on error resume next statement. What should be my next
step to get the recordset? I have changed to 4.0. Thanks


"Bob Barrows [MVP]" wrote:

> Jack wrote:
> > Hi,
> > I am trying to retrieve values from a query in a list box. However,
> > it is giving me no recordset. Although I get recordset when I query
> > this access database. Any help for resolution on this is appreciated.
> > Thanks
> >
> > CODE:
> > <%
> > Dim oConn
> > Dim oRS
> > Dim vCS
> > Dim sqltxt
> >
> > set oConn=server.CreateObject("ADODB.connection")
> > vCS = "Provider=Microsoft.Jet.OLEDB.3.51"
>
> Why 3.51? You're not using a database that's that old are you? Use the
> 4.0 provider.
>
> > vCS = vCS & "; Data Source=C:\Sailors.mdb"
> > oConn.connectionstring = vCS
> > oConn.Open
> >
> > set oRS = server.CreateObject("ADODB.recordset")
> > sqltxt = "SELECT BoatName, BoatClass FROM Boats;"
> > oRS.Open sqltxt,oConn
>
> I see no major problems here. Do you have an On Error Resume Next
> statement anywhere that is masking your errors?
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Problem with retrieving values in a combo box from a query

Again, I see no obvious problem. If it was a problem with your query,
you would be getting an error message.

Are you sure you are connecting to the correct database?

As an aside, it is my preference to test for EOF immediately after
opening the recordset so I can return a "no records" message to the
client.

Jack wrote:
> Hi Bob,
> I do not have any on error resume next statement. What should be my
> next step to get the recordset? I have changed to 4.0. Thanks
>
>
> "Bob Barrows [MVP]" wrote:
>
>> Jack wrote:
>>> Hi,
>>> I am trying to retrieve values from a query in a list box. However,
>>> it is giving me no recordset. Although I get recordset when I query
>>> this access database. Any help for resolution on this is
>>> appreciated. Thanks
>>>


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with retrieving values in a combo box from a query

Thanks Bob for further advise. I changed the Provider to 4.0. I also checked
that the database had no IUSR_USERMACHINE connection. Doing so is giving me
result. However, I am getting only the second field instead of the two fields
described in the queries. Any idea why the first field will not be displayed?
Thanks again.

"Bob Barrows [MVP]" wrote:

> Again, I see no obvious problem. If it was a problem with your query,
> you would be getting an error message.
>
> Are you sure you are connecting to the correct database?
>
> As an aside, it is my preference to test for EOF immediately after
> opening the recordset so I can return a "no records" message to the
> client.
>
> Jack wrote:
> > Hi Bob,
> > I do not have any on error resume next statement. What should be my
> > next step to get the recordset? I have changed to 4.0. Thanks
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> Jack wrote:
> >>> Hi,
> >>> I am trying to retrieve values from a query in a list box. However,
> >>> it is giving me no recordset. Although I get recordset when I query
> >>> this access database. Any help for resolution on this is
> >>> appreciated. Thanks
> >>>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Problem with retrieving values in a combo box from a query

Jack wrote:
> Thanks Bob for further advise. I changed the Provider to 4.0. I also
> checked that the database had no IUSR_USERMACHINE connection.

I don't understand what you mean by that sentence. "... checked that the
database had no IUSR_USERMACHINE connection"??

> Doing so is giving me result.

Doing what?

> However, I am getting only the second field
> instead of the two fields described in the queries.

There is more than one query?

> Any idea why the
> first field will not be displayed? Thanks again.

Sorry, but no.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.