ASP/VBScript connected drop down box error

[JSubadhra]

Hi,

I am very new to ASP. I am trying to change another person's code. I
googled my query and read a lot of ideas. But my problem is as I try to
change one thing, someother code gets affected. SO I have decided to
modify the code I have to do the task. I really need some help here.
The task is --

I have 3 drop down boxes, country, state, city. The state has to
populate based on country ....

The code I have is --
<%-------------------------------- populates the country
Sub MakeConnectedDropDownCountry(byVal sColumn, byVal sID, byVal
sTable, sName, sHeight, sWidth)
Dim oRS, strSQL, RSCount
strSQL="SELECT DISTINCT " & sColumn & " FROM " & sTable & " ORDER BY "
& sColumn
Set oRS=server.CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open strSQL, oConn
if oRS.RecordCount>0 then
%>
<OPTION Value=0></OPTION>
<%oRS.MoveFirst
Do While Not oRS.EOF
%>
<OPTION Value="<%=oRS(sColumn & "").Value%>"><%=oRS(sColumn &
"").Value%></OPTION>
<%
oRS.MoveNext
Loop
End if
%>
</SELECT>
<%
oRS.Close
Set oRS=nothing
End Sub
%>

The above code will populate the country drop down list. Now to
populate the State ---


<%
Sub MakeConnectedDropDownState(byVal sColumn, byVal sID, byVal sTable,
sName, sHeight, sWidth)

Dim oRS, strSQL, RSCount
Dim sCountry

sCountry =Cint(txtCountry.options[txtCountry.selectedIndex].text)

strSQL="SELECT DISTINCT " & sColumn & " FROM LOCATION WHERE Country="
&sCountry&" ORDER BY " & sColumn
Set oRS=server.CreateObject("ADODB.Recordset")
oRS.CursorLocation=3 'adUseClient
oRS.Open strSQL, oConn
if oRS.RecordCount>0 then
%>
<SELECT id=<%=sName%> name=<%=sName%><%if sHeight<>"" OR sWidth<>""
<OPTION Value=0></OPTION>
<%
oRS.MoveFirst
Do While Not oRS.EOF
%>
<OPTION Value="<%=oRS(sColumn & "").Value%>"><%=oRS(sColumn &
"").Value%></OPTION>
<%
oRS.MoveNext
Loop
End if
%>
</SELECT>
<%
oRS.Close
Set oRS=nothing
End Sub
%>


The country sub is called as
<%MakeConnectedDropDownCountry "COUNTRY", "", "LOCATION", "txtCountry",
"10", "75"%>

The name of this dropdown box will be txtCountry. So why is this line
in the sub makestate ---

*******sCountry
=Cint(txtCountry.options[txtCountry.selectedIndex].text) *****

Not working. May be this is very obvious for people who know these
stuff. For me (I started ASP two days ago after this project fell in my
head). Is there any way that I could refer the value that was selected
in the country dropbox in this SQL and make it work, instead of using
"sCountry".

strSQL="SELECT DISTINCT " & sColumn & " FROM LOCATION WHERE Country="
&sCountry&" ORDER BY " & sColumn


Any help will be greatly appreciated. Thanks a lot.

 

[Michael Evanchik]

can you form a question?

can your produce an error message?

i see you have pasted all your code. that is good.

Michael Evanchik
www.michaelevanchik.com

Hi,

I am very new to ASP. I am trying to change another person's code. I
googled my query and read a lot of ideas. But my problem is as I try to
change one thing, someother code gets affected. SO I have decided to
modify the code I have to do the task. I really need some help here.
The task is --

I have 3 drop down boxes, country, state, city. The state has to
populate based on country ....

The code I have is --
<%-------------------------------- populates the country
Sub MakeConnectedDropDownCountry(byVal sColumn, byVal sID, byVal
sTable, sName, sHeight, sWidth)
Dim oRS, strSQL, RSCount
strSQL="SELECT DISTINCT " & sColumn & " FROM " & sTable & " ORDER BY "
& sColumn
Set oRS=server.CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open strSQL, oConn
if oRS.RecordCount>0 then
%>
<OPTION Value=0></OPTION>
<%oRS.MoveFirst
Do While Not oRS.EOF
%>
<OPTION Value="<%=oRS(sColumn & "").Value%>"><%=oRS(sColumn &
"").Value%></OPTION>
<%
oRS.MoveNext
Loop
End if
%>
</SELECT>
<%
oRS.Close
Set oRS=nothing
End Sub
%>

The above code will populate the country drop down list. Now to
populate the State ---


<%
Sub MakeConnectedDropDownState(byVal sColumn, byVal sID, byVal sTable,
sName, sHeight, sWidth)

Dim oRS, strSQL, RSCount
Dim sCountry

sCountry =Cint(txtCountry.options[txtCountry.selectedIndex].text)

strSQL="SELECT DISTINCT " & sColumn & " FROM LOCATION WHERE Country="
&sCountry&" ORDER BY " & sColumn
Set oRS=server.CreateObject("ADODB.Recordset")
oRS.CursorLocation=3 'adUseClient
oRS.Open strSQL, oConn
if oRS.RecordCount>0 then
%>
<SELECT id=<%=sName%> name=<%=sName%><%if sHeight<>"" OR sWidth<>""
<OPTION Value=0></OPTION>
<%
oRS.MoveFirst
Do While Not oRS.EOF
%>
<OPTION Value="<%=oRS(sColumn & "").Value%>"><%=oRS(sColumn &
"").Value%></OPTION>
<%
oRS.MoveNext
Loop
End if
%>
</SELECT>
<%
oRS.Close
Set oRS=nothing
End Sub
%>


The country sub is called as
<%MakeConnectedDropDownCountry "COUNTRY", "", "LOCATION", "txtCountry",
"10", "75"%>

The name of this dropdown box will be txtCountry. So why is this line
in the sub makestate ---

*******sCountry
=Cint(txtCountry.options[txtCountry.selectedIndex].text) *****

Not working. May be this is very obvious for people who know these
stuff. For me (I started ASP two days ago after this project fell in my
head). Is there any way that I could refer the value that was selected
in the country dropbox in this SQL and make it work, instead of using
"sCountry".

strSQL="SELECT DISTINCT " & sColumn & " FROM LOCATION WHERE Country="
&sCountry&" ORDER BY " & sColumn


Any help will be greatly appreciated. Thanks a lot.

 

[JSubadhra]

Thanks for your reply.

The error message is "SQL statement not properly terminated" when I
remove the Cint, when I keep the Cint it gives a type mismatch error..
This is what I have understood --

sCountry=Cint(txtCountry.options[txtCountry.selectedIndex].text)

This line is not substituting the value of the
"txtCountry.options[txtCountry.selectedIndex].text", its just taking it
as another string. So it gives a type mismatch error. If I remove the
Cint, in the query it just gives the string and SQL cannot understand
it and giving me the SQL error.

My question is --

I have seen txtCountry.options[txtCountry.selectedIndex].text gives the
value of the selection (in many javascript code). How do I make this
code take the calue of the selection and not take it as a whole string?
Basically what I am trying to do is take the selection made in the
previous drop box and use it in the WHERE condition. Is there a way t
do that in my code?

Thanks a lot.

 

[Michael Evanchik]

can u debug your sql statement for me in both cases

also what field type is the country field in your sql database?

Thanks for your reply.

The error message is "SQL statement not properly terminated" when I
remove the Cint, when I keep the Cint it gives a type mismatch error..
This is what I have understood --

sCountry=Cint(txtCountry.options[txtCountry.selectedIndex].text)

This line is not substituting the value of the
"txtCountry.options[txtCountry.selectedIndex].text", its just taking it
as another string. So it gives a type mismatch error. If I remove the
Cint, in the query it just gives the string and SQL cannot understand
it and giving me the SQL error.

My question is --

I have seen txtCountry.options[txtCountry.selectedIndex].text gives the
value of the selection (in many javascript code). How do I make this
code take the calue of the selection and not take it as a whole string?
Basically what I am trying to do is take the selection made in the
previous drop box and use it in the WHERE condition. Is there a way t
do that in my code?

Thanks a lot.

can you form a question?

can your produce an error message?

i see you have pasted all your code. that is good.

Michael Evanchik
www.michaelevanchik.com