simple (?) connection problem

[middletree]

Trying to build a dropdown from the values in an Access database, but can't
get past this error. Should be easy, but I can't make it work.

First, the code:
1. <select name="Gift1" id="Gift1">
<option value="" selected>-Select One-
<%Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT GiftID, GiftDesc "
strSQL = strSQL & "FROM Gift "
strSQL = strSQL & "ORDER BY GiftDesc"
RS.Open strSQL, conn
' set rs = objconnection.execute(strSQL)

While Not RS.EOF
Response.Write "<option
value='"&RS.Fields("GiftID")&"'>"&RS.Fields("GiftDesc")&"</option>"
RS.MoveNext
WEND
RS.Close
set RS = nothing
%>

</select>


Then, the code in my include file to establish connection:

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("shape.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.open cst

The page already has successfuly connected to insert some data, so I don't
think the connection code is bad. What am I doing wrong?

 

[middletree]

Forgot to add., this is the error I get:

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/grace/shape.asp, line 117


Where 117 is:
RS.Open strSQL, conn

 

[Ray at]

What does
RESPONSE.WRITE STRSQL
RESPONSE.END

show you?

Or, which line is line 117?

Ray at home

 

[Ray at]

Oops. You told us what line 117 is.

Why did you comment out the "set rs = objconnection.execute(strSQL)" line?
I think you should use that instead of RS.Open.

Ray at home

 

[middletree]

When I have
RS.Open strSQL, conn
set rs = objconnection.execute(strSQL)

it gives me the same error. So then I comment out
RS.Open strSQL, conn

and keep the other one un-commented, and I get:
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/grace/shape.asp, line 118

where 118 is the one I just un-commented.

Should also mention that I'm using Access 2000

 

[Ray at]

Would you like to share that select statement?

Ray at home

 

[middletree]

It shows exactly what you'd think: The Select statement

 

[Ray at]

I'd put it back to the SET RS = line. It appears that the reason you're
getting that error is that your ADODB object is called conn, not
objconnection, as per your code snippet:

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("shape.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.open cst

Ray at home

 

[Chris Hohmann]

Trying to build a dropdown from the values in an Access database, but can't
get past this error. Should be easy, but I can't make it work.

First, the code:
1. <select name="Gift1" id="Gift1">
<option value="" selected>-Select One-
<%Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT GiftID, GiftDesc "
strSQL = strSQL & "FROM Gift "
strSQL = strSQL & "ORDER BY GiftDesc"
RS.Open strSQL, conn
' set rs = objconnection.execute(strSQL)

While Not RS.EOF
Response.Write "<option
value='"&RS.Fields("GiftID")&"'>"&RS.Fields("GiftDesc")&"</option>"
RS.MoveNext
WEND
RS.Close
set RS = nothing
%>

</select>


Then, the code in my include file to establish connection:

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("shape.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.open cst

The page already has successfuly connected to insert some data, so I don't
think the connection code is bad. What am I doing wrong?

Populating a select control became a recurring issue for me so I created
a function. Hooray for code reuse. Here it is:

Function DBCFS(strConn,strSQL,varSelectedKey)
'I/O:
'--> strConn : Connection String
'--> strSQL : SQL Statement OR "Table" Name
'--> varSelectedKey : Variant that identifies which option should be
selected
'Notes:
'The function expects strSQL to return at least two(2) columns.
'Column 1 will be used to populate the value attribute of the option tag
'Column 2 will be used to populate the content of the option tag, ie.
what gets displayed

'Determine command type
Dim re, lngOptions
Set re = New RegExp
re.Pattern = "^\s*(SELECT|EXEC)"
re.IgnoreCase = True
If re.Test(strSQL) Then
lngOptions = &H1 'Command Text
Else
lngOptions = &H2 'Table
End If

'Get the data
Dim conn, rs, arr
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
Set rs = conn.Execute(strSQL,,lngOptions)
If Not rs.EOF Then arr = rs.GetRows()
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing

'Build the option tags
Dim j,o
o=""
If IsArray(arr) Then
For j = 0 to UBound(arr,2)
o=o & "<option value=""" & Server.HTMLEncode(arr(0,j)) & """"
If CStr(arr(0,j)) = CStr(varSelectedKey) Then
o=o & " selected"
End If
o=o & ">" & Server.HTMLEncode(arr(1,j)) & "</option>" & vbCRLF
Next
Else
o=o & "<option>[No Option Data]</option>"
End If
DBCFS = o
End Function

For your situation you would invoke it like so:
<select name="Gift1" id="Gift1"><%= DBCFS(cst,strSQL,"") %></select>

HTH
-Chris Hohmann

 

[Bob Barrows]

Trying to build a dropdown from the values in an Access database, but
can't get past this error. Should be easy, but I can't make it work.

First, the code:
1. <select name="Gift1" id="Gift1">
<option value="" selected>-Select One-

<%Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT GiftID, GiftDesc "
strSQL = strSQL & "FROM Gift "
strSQL = strSQL & "ORDER BY GiftDesc"
RS.Open strSQL, conn
' set rs = objconnection.execute(strSQL)

While Not RS.EOF
Response.Write "<option
value='"&RS.Fields("GiftID")&"'>"&RS.Fields("GiftDesc")&"</option>"
RS.MoveNext
WEND
RS.Close
set RS = nothing
%>

</select>


Then, the code in my include file to establish connection:

cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("shape.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.open cst

The page already has successfuly connected to insert some data, so I
don't think the connection code is bad. What am I doing wrong?

I don't see the problem. Perhaps the line numbers are screwed up due to the
inter-mixed server-side and html code. Try creating a test page with just
the server-side script so you can test that it works correctly.

Bob Barrows

 

[middletree]

The closing option tag is optional. Besides, the error is talking about an
object, which could not apply to any HTML tag.

 

[middletree]

Sorry.

SELECT GiftID, GiftDesc
FROM Gift
ORDER BY GiftDesc

 

[Bob Barrows]

The closing option tag is optional. Besides, the error is talking
about an object, which could not apply to any HTML tag.

I realize that. That's why I wrote this:

Bob Barrows

 

[Ray at]

Are you still getting an error with the "set rs=" line after correcting the
name of your connection object?

Ray at home

 

[middletree]

Yes.

Are you still getting an error with the "set rs=" line after correcting the
name of your connection object?

Ray at home

 

[Ray at]

<%
'''from the include
cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("shape.mdb")
set conn = Server.CreateObject("ADODB.Connection")
conn.open cst
%>

<select name="Gift1" id="Gift1">
<option value="" selected>-Select One-</option>

<%
strSQL = "SELECT GiftID, GiftDesc "
strSQL = strSQL & "FROM Gift "
strSQL = strSQL & "ORDER BY GiftDesc"
set rs = conn.execute(strSQL)

While Not RS.EOF
Response.Write "<option value=""" & rs.Fields.Item(0).Value & """>" &
rs.Fields.Item(1).Value & "</option>"
RS.MoveNext
WEND
RS.Close
set RS = nothing
%>
</select>


Assuming that you have columns named GiftID and GiftDesc in a table named
Gift, this code above should work. Give it a try.

Ray at home

 

[middletree]

I'll do that when I get home (it's for a chruch thing I'm working on at
home, so I don't have the files here)

thanks

 

[middletree]

Well, I got it to work, but only by putting all the code into the same page.
When I had the top part in an include file, it gave me the object required
error. Not sure why. I am 100% sure that the code pointing to the include
file was correct.

Oh well, this thing is only going to have 3-4 pages anyway, so it's not that
big a deal to have the include for the connection.

thanks

 

[Ray at]

Is that code in a Sub? If so, did you call it? Also, did you correct the
incosistency between your object naming? ("conn" in your include and
"objconnection" in your page)

Ray at work