Combo box variable and asp page 3

accessguy52 · Dec 5, 2002

Hello - I'm sorry if my subject line was not explicit enough, but here's my problem. I'm fairly new to ASP and I need to accomplish the following:

I have a page with a combo box that mimics an Access form where the values in the combo box are criteria for a SQL statement that when a button is pushed, an Excel page comes up listing who fits that value. I've got all the code I need to get working in ASP. My problem is this:

how do I tell ASP to "Select * From the value in the combobox where id equals combobox value and send the recordset to Excel in the browser". I have no problem with that. The combo control is called "Title". I think the compiler is saying, "WHAT control from WHAT page?"

Here's the code:

sSQL = "SELECT DISTINCT TBL_MASTER.Record_ID,[TBL_MASTER].[Last_Name], [First_Name] AS Name,"
sSQL= sSQL & " TBL_HR_Data.Application_Received, TBL_POSITIONS_OPEN.Position_Title,"
sSQL= sSQL & " TBL_POSITIONS_OPEN.POSITION_ID, TBL_HR_SEC_SHARE.Phone#1, TBL_HR_SEC_SHARE.Phone#2,"
sSQL= sSQL & " TBL_HR_SEC_SHARE.Phone#3, TBL_HR_SEC_SHARE.Email, TBL_HR_Data.Notes "
sSQL= sSQL & " FROM TBL_MASTER LEFT JOIN TBL_HR_Data ON TBL_MASTER.Record_ID = TBL_HR_Data.Record_ID INNER JOIN TBL_POSITIONS_OPEN RIGHT JOIN TBL_POSITION_CANDIDATES ON TBL_POSITIONS_OPEN.POSITION_ID = TBL_POSITION_CANDIDATES.POSITION_ID ON TBL_MASTER.Record_ID = TBL_POSITION_CANDIDATES.Record_ID INNER JOIN TBL_HR_SEC_SHARE ON TBL_MASTER.Record_ID = TBL_HR_SEC_SHARE.Record_ID LEFT JOIN TBL_SECURITY_DETAIL ON TBL_MASTER.Record_ID = TBL_SECURITY_DETAIL.Record_ID"
sSQL= sSQL & " Where TBL_POSITIONS_OPEN.POSITION_ID= '" & Request("Title&quot

& "'"

Ignore everything but the last line. It's the request part.
Am I doing that right? It's probably an absurdly simple solution, I just don't know it.

Does this request or does it need more clarification to everyone? Thanks much in advance. Mike

Tarwn · Dec 5, 2002

That appears to be correct, try doing a
Response.Write "Value of Title: " & Request("Title&quot

Response.End

before the sql statement and see if you get anything, it may be that the value is being passed correctly.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 5, 2002

OK -- here's the whole program cut & pasted:

<%
Response.ContentType = "application/vnd.ms-excel"
'Response.Buffer=true
'Response.Expires = -1
dim sSQL
dim rs

'Open the connection and retrieve data from the database
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "DSN=hrsec;UID=sa;PWD=sqladmin"
Response.Write "Value of Title: "
Request("Title&quot

'Response.End

sSQL = "SELECT DISTINCT TBL_MASTER.Record_ID,[TBL_MASTER].[Last_Name], [First_Name] AS Name,"
sSQL= sSQL & " TBL_HR_Data.Application_Received, TBL_POSITIONS_OPEN.Position_Title,"
sSQL= sSQL & " TBL_POSITIONS_OPEN.POSITION_ID, TBL_HR_SEC_SHARE.Phone#1, TBL_HR_SEC_SHARE.Phone#2,"
sSQL= sSQL & " TBL_HR_SEC_SHARE.Phone#3, TBL_HR_SEC_SHARE.Email, TBL_HR_Data.Notes "
sSQL= sSQL & " FROM TBL_MASTER LEFT JOIN TBL_HR_Data ON TBL_MASTER.Record_ID = TBL_HR_Data.Record_ID INNER JOIN "
sSQL= sSQL & " TBL_POSITIONS_OPEN RIGHT JOIN TBL_POSITION_CANDIDATES ON TBL_POSITIONS_OPEN.POSITION_ID = TBL_POSITION_CANDIDATES.POSITION_ID "
sSQL= sSQL & " ON TBL_MASTER.Record_ID = TBL_POSITION_CANDIDATES.Record_ID INNER JOIN TBL_HR_SEC_SHARE ON TBL_MASTER.Record_ID = TBL_HR_SEC_SHARE.Record_ID "
sSQL= sSQL & " LEFT JOIN TBL_SECURITY_DETAIL ON TBL_MASTER.Record_ID = TBL_SECURITY_DETAIL.Record_ID"
sSQL= sSQL & " Where TBL_POSITIONS_OPEN.POSITION_ID= '" & Request("Title&quot

& "'"

rs.Execute sSQL

'set up table
%>

<TABLE BORDER=1>
<TR BGCOLOR = YELLOW>
<%
'Loop thru field names and print out field names
j=2 'row counter
For i = 0 to rs.Fields.Count - 1
%>
<TD><B><% = rs(i).Name %></B></TD>
<% Next %>
</TR>
<%
'Loop thru rows displaying each field.
Do While Not rs.EOF
%>
<TR>
<% For i = 0 to rs.Fields.Count - 1
%>
<TD VALIGN=TOP><% = rs(i) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
j = j + 1
Loop
'Clean up
rs.Close
%>

</TABLE>
</HTML>

Now, the compiler is saying: Object required for the "rs.execute sSQL" statement

Can anyone help me with that? Many thanks.

onpnt · Dec 5, 2002

appears like a syntax issue
try
rs.Execute(sSQL) I dare to learn more

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

accessguy52 · Dec 5, 2002

re my code before -- I don't think anything is getting passed to "Title", the combo box. I tried the suggestions posted by seeing if there was a value being passed to the combobox. I think this may be why the compiler is saying "Object required". Or am I wrong?

To restate the problem: I need to compare the value in the combobox to the sql statement, push a button on the form, and have Excel showing me the recordset in the browser. This worked when I didn't have a "WHERE" statement, showing me ALL the records. Now that I have to compare values to a dropdown box, it's a different story. Also, I didn't have the sSQL variable, just a really LONG SQL statment!

Any suggestions appreciated. Mike

Tarwn · Dec 5, 2002

You never instantiated your recordset, so right now it is of variant type and that doesn't have a .Execute method, try adding the following line:

Code:

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)

after the similar connection object instantiation. Also, since the recordset doesn't actually have an execute method you can use the Open method to open it:
Recordset Open Method:

Code:

.Open SqlOrTable[,connectionstring or object[,cursor[,locktype[,options]]]]

Where options is 1 or more values of type CommandTypeEnum or ExecuteOptionEnum

or instead of instantiating the recordset you could use the execute method of the connection object.
Connection Execute Method:

Code:

.Execute sqlString[,numRecordsAffected[,options]]

options here is same as above.

Hope that helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 5, 2002

Thank you - that helped a great deal! OK, now I get my excel form, but no still no data, mostly because the WHERE statement is comparing 'TITLE' to the value in the SQL. Here's the code again --

<%
Response.ContentType = "application/vnd.ms-excel"

dim sSQL
dim rs

'Open the connection and retrieve data from the database
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "DSN=hrsec;UID="";PWD="""
Response.Write "Value of Title:" & Request("Title&quot

sSQL = "SELECT DISTINCT TBL_MASTER.Record_ID,[TBL_MASTER].[Last_Name], [First_Name] AS Name,"
sSQL= sSQL & " TBL_HR_Data.Application_Received, TBL_POSITIONS_OPEN.Position_Title,"
sSQL= sSQL & " TBL_POSITIONS_OPEN.POSITION_ID, TBL_HR_SEC_SHARE.Phone#1, TBL_HR_SEC_SHARE.Phone#2,"
sSQL= sSQL & " TBL_HR_SEC_SHARE.Phone#3, TBL_HR_SEC_SHARE.Email, TBL_HR_Data.Notes "
sSQL= sSQL & " FROM TBL_MASTER LEFT JOIN TBL_HR_Data ON TBL_MASTER.Record_ID = TBL_HR_Data.Record_ID INNER JOIN "
sSQL= sSQL & " TBL_POSITIONS_OPEN RIGHT JOIN TBL_POSITION_CANDIDATES ON TBL_POSITIONS_OPEN.POSITION_ID = TBL_POSITION_CANDIDATES.POSITION_ID "
sSQL= sSQL & " ON TBL_MASTER.Record_ID = TBL_POSITION_CANDIDATES.Record_ID INNER JOIN TBL_HR_SEC_SHARE ON TBL_MASTER.Record_ID = TBL_HR_SEC_SHARE.Record_ID "
sSQL= sSQL & " LEFT JOIN TBL_SECURITY_DETAIL ON TBL_MASTER.Record_ID = TBL_SECURITY_DETAIL.Record_ID"
sSQL= sSQL & " Where TBL_POSITIONS_OPEN.POSITION_ID= '" & Request("Title&quot

& "'"

set rs = conn.Execute(sSQL)

'set up table
%>

<TABLE BORDER=1>
<TR BGCOLOR = YELLOW>
<%
'Loop thru field names and print out field names
j=2 'row counter
For i = 0 to rs.Fields.Count - 1
%>
<TD><B><% = rs(i).Name %></B></TD>
<% Next %>
</TR>
<%
'Loop thru rows displaying each field.
Do While Not rs.EOF
%>
<TR>
<% For i = 0 to rs.Fields.Count - 1
%>
<TD VALIGN=TOP><% = rs(i) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
j = j + 1
Loop
'Clean up
rs.Close
%>

</TABLE>
</HTML>

Is the request OK? Remember, I'm loading the dropdown from a table on another page? Do I have to have THAT code included to make the SQL and the d.d. control name "TITLE" see each other?

Again, thanks in advance.

accessguy52 · Dec 5, 2002

Oh, sorry, the above code is for the BUTTON to launch excel according to the dropdown value. The table that feeds the dropdown is in another asp page. Does that help anyone?

Mike

Tarwn · Dec 5, 2002

See earlier post to check if the value is being passed, also comment out the contenttype setting, that only slows you down in this stage of debugging.

If the value is null then it is not being passed correctly, meaning it is either:
a) named differantly on the previous page
b) not inside the form tags on the previous page

If you get this far through my list without finding the problem, post the previous code.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 5, 2002

Tarwn - Here's the code again:

<%
Response.ContentType = "application/vnd.ms-excel"

dim sSQL
dim rs

'Open the connection and retrieve data from the database
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "DSN=hrsec;UID=sa;PWD=sqladmin"

' <TR>

' <TD width=150px align=right class=text style="color: White"><B>Position Title:</B></TD>
' <TD valign=top nowrap>

' <SELECT class=text NAME="Title" id="Title" class=text style="width:230px" tabindex="19" >
Response.Write "Value of Title:" & (Request.QueryString("Title&quot

)

sSQL = "SELECT DISTINCT TBL_MASTER.Record_ID,[TBL_MASTER].[Last_Name], [First_Name] AS Name,"
sSQL= sSQL & " TBL_HR_Data.Application_Received, TBL_POSITIONS_OPEN.Position_Title,"
sSQL= sSQL & " TBL_POSITIONS_OPEN.POSITION_ID, TBL_HR_SEC_SHARE.Phone#1, TBL_HR_SEC_SHARE.Phone#2,"
sSQL= sSQL & " TBL_HR_SEC_SHARE.Phone#3, TBL_HR_SEC_SHARE.Email, TBL_HR_Data.Notes "
sSQL= sSQL & " FROM TBL_MASTER LEFT JOIN TBL_HR_Data ON TBL_MASTER.Record_ID = TBL_HR_Data.Record_ID INNER JOIN "
sSQL= sSQL & " TBL_POSITIONS_OPEN RIGHT JOIN TBL_POSITION_CANDIDATES ON TBL_POSITIONS_OPEN.POSITION_ID = TBL_POSITION_CANDIDATES.POSITION_ID "
sSQL= sSQL & " ON TBL_MASTER.Record_ID = TBL_POSITION_CANDIDATES.Record_ID INNER JOIN TBL_HR_SEC_SHARE ON TBL_MASTER.Record_ID = TBL_HR_SEC_SHARE.Record_ID "
sSQL= sSQL & " LEFT JOIN TBL_SECURITY_DETAIL ON TBL_MASTER.Record_ID = TBL_SECURITY_DETAIL.Record_ID "
sSQL= sSQL & " Where TBL_POSITIONS_OPEN.POSITION_ID= '" & (Request.QueryString("Title&quot

)

set rs = conn.Execute(sSQL)

'set up table
%>

<TABLE BORDER=1>
<TR BGCOLOR = YELLOW>
<%
'Loop thru field names and print out field names
j=2 'row counter
For i = 0 to rs.Fields.Count - 1
%>
<TD><B><% = rs(i).Name %></B></TD>
<% Next %>
</TR>
<%
'Loop thru rows displaying each field.
Do While Not rs.EOF
%>
<TR>
<% For i = 0 to rs.Fields.Count - 1
%>
<TD VALIGN=TOP><% = rs(i) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
j = j + 1
Loop
'Clean up
rs.Close
%>

</TABLE>
</HTML>

Problem 1: There is still no value being passed to the dropdown.

Problem 2: I get the following compiler message re the last line in the sql statement:

Value of Title: <font face="Arial" size=2>
<p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e14'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ''.</font>
<p>
<font face="Arial" size=2>/HRSEC_Reports/DivGroupToExcel.asp</font><font face="Arial" size=2>, line 35</font>

Any ideas ? I just don't know how to close that statement.
Thanks, Mike

Tarwn · Dec 5, 2002

Sorry, I missed a word

I meant post the previous page's code so i could take a look at your form.

Also, take a closer look at the line you changed, generally when an error shows up thats new it has something to do with something you have changed:

Code:

See anything wrong with that last line of your SQL statement?
sSQL= sSQL & &quot; Where TBL_POSITIONS_OPEN.POSITION_ID= '&quot; & (Request.QueryString(&quot;Title&quot;))

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 5, 2002

Tarwn - Are you refering to querystring? I thought the querystring might retrieve the value of "Title". But, even without that it doesn't work.

accessguy52 · Dec 5, 2002

Tarwn - sorry here's the code for actually the form that loads the table into the dropdown.
<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<H2><CENTER>Criteria and Report Selection</H2>

<BODY BGCOLOR=Silver>

<BR>Please select the position:</BR>

<TR>

<TD width=150px align=right class=text style="color: White"><B>Position Title:</B></TD>
<TD valign=top nowrap>

<% Set conn = Server.CreateObject("ADODB.Connection&quot

%>
<% conn.Open "DSN=hrsec;UID=sa;PWD=sqladmin"%>
<% Set rs = conn.Execute("SELECT TBL_POSITIONS_OPEN.POSITION_ID, TBL_POSITIONS_OPEN.Position_Title FROM TBL_POSITIONS_OPEN WHERE TBL_POSITIONS_OPEN.Position_Title Is Not Null&quot

%>

<SELECT class=text NAME="Title" id="Title" class=text style="width:230px" tabindex="19" >

<%Do While Not rs.EOF%>
<OPTION><%= rs("Position_Title&quot

%></option>
<%rs.MoveNext
Loop%>
</SELECT>
</TD>
</TR>
</CENTER>

<%rs.Close %>
<%conn.Close %>
</SCRIPT>
<BR><BR>
<CENTER>
<FORM ACTION="DivGroupToExcel.asp" METHOD=POST>
<p><INPUT TYPE= "Submit" VALUE="Report by Position(s)"></CENTER></P>
</BODY>
</HTML>

Gotta go home soon...thru the aftermath of the east caost snowstorm...

onpnt · Dec 5, 2002

accessguy
read this thread. thread333-132705
it will help out in a lot of aspects that you may not have a problem with but should be looked into I dare to learn more

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

onpnt · Dec 5, 2002

you're not enclosing the form in the form tags as Tarwn said you may not be in the list earlier.
<SELECT class=text NAME="Title" id="Title" class=text style="width:230px" tabindex="19" >

<%Do While Not rs.EOF%>
<OPTION><%= rs("Position_Title&quot%></option>
<%rs.MoveNext
Loop%>
</SELECT>

</TD>
</TR>
</CENTER>

<%rs.Close %>
<%conn.Close %>
</SCRIPT>
<BR><BR>
<CENTER>
<FORM ACTION="DivGroupToExcel.asp" METHOD=POST>
<p><INPUT TYPE= "Submit" VALUE="Report by Position(s)"></CENTER></P>
close the tag also</form>
</BODY>
I dare to learn more

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

Tarwn · Dec 5, 2002

Also the error I was pointing out earlier was that you had accidentally overwritten your second single quote when you put in the Querystring portion:
Original:

Code:

sSQL= sSQL & &quot; Where TBL_POSITIONS_OPEN.POSITION_ID= '&quot; & Request(&quot;Title&quot;) & &quot;'&quot;

Changed (with missing quote):

Code:

sSQL= sSQL & &quot; Where TBL_POSITIONS_OPEN.POSITION_ID= '&quot; & (Request.QueryString(&quot;Title&quot;))

Another thing:
Once you move your form tag up so that the select is in the form as well, you will need to change the Request.QueryString to a Request.Form
QueryString is for the GET method, Form is for the POST method, so your final version of this string should like this:

Code:

sSQL= sSQL & &quot; Where TBL_POSITIONS_OPEN.POSITION_ID= '&quot; & Request.Form(&quot;Title&quot;) & &quot;'&quot;

For future reference, the things that lead onpnt and I to our conclusions were:

1) Object Required Error: This is always an error indicating that you are attempting to access an object that has not been instatiated
2) Error with SQL String: When concatenating in values from a form, the first thing you should always do is check that the value is being passed (1st response above):

Code:

Response.Write &quot;Value of Title: &quot; & Request(&quot;Title&quot;)
Response.End

(from previous post as well)
If the value is null then it is not being passed correctly, meaning it is either:
a) named differantly on the previous page
b) not inside the form tags on the previous page

3) It is always a good idea to allow the page to output to standard HTML when tracking errors, the error above that lead to the single quote missing is:

Code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ''. 
/HRSEC_Reports/DivGroupToExcel.asp, line 35

Note this line error points to line 35 (your execute statement) and that it has an issue with something in the string there. Your sql string is never evaluated as an SQL string until you attempt to use it, so the conclusion to be drawn from this type of error is that you have a grammatical error in your SQL string.

Hope this helps in the future as well as with this application,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 6, 2002

Thanks, folks, but I had no idea what the grammatical error IS. I've been referencing "Beginning ASP 3.0" (the ASP bible, you know the one) looking for an example to match my situation. My problems seem to be:

1. No value is being passed to dropdown
2. syntax error in WHERE clause.

Not at my programming desk today, look at next week. Many thanks, we'll continue then.

Mike.

accessguy52 · Dec 10, 2002

Hello, folks- I'm back at my problem again today. Let me respond to a few comments made earlier:

I can't comment out the content-type because without that, I can't bring up excel.

Can anyone give me an example of a SQL statement that shows me the correct syntax on how to return a recordset based on a form's dropdown box value? Especially with a WHERE clause. Just something simple so I can compare MY statement with yours and see where I'm going wrong. Many thanks!

accessguy52

Tarwn · Dec 10, 2002

Let me explain the commenting out comment type, this is for testing, after it outputs correctly to the web browser then you uncomment the excel portion so you can test it in excel already knowing the code works fine in the browser.

Example of SQL stmt using value of a select on previous page called selColor:

Code:

Dim sqlStmt
sqlStmt = &quot;SELECT * FROM ColorTable WHERE color = '&quot;&Request.Form(&quot;selColor&quot;)&&quot;'&quot;

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

accessguy52 · Dec 10, 2002

Tarwn - well, good news: that was the code I needed! I actually tried that out BEFORE I saw your reply and it worked. Many thanks to you and onpnt. NOW, I have to see whether using rs.Open will work just as well as DSN=. I think you suggested that before, didn't you? Anyway, thanks again ( I did a victory dance when it worked..).

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Combo box variable and asp page 3

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor