SQL statements in a function - The code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

SQL statements in a function - The code

Author

Topic

malambing57
Starting Member

5 Posts

Posted - 2005-04-14 : 12:32:41

I think my I did not explain my problem properly as I have 5 reads and no reply
Here is the code.

I have no problem creating the connection and opening the MARKET TABLE. However, when I want to open the AGENT TABLE based on the selected market, the system does not recognized my connection.
Can anybody help me??? Thanks a million.

ASP PAGE:

<%
'**to set field value
sqlvarMarket= "SELECT TOP 100 PERCENT MarketStaffID,MarketAreaCode, MarketDescription FROM dbo.MarketAreaTable ORDER BY MarketDescription"
Set revarMarket = conn.Execute(sqlvarMarket) %>

HERE IS PART OF THE BODY:
<select name="selectMarket" size="1" style="width:150;COLOR:#669966;">
<option value=0 selected><font color="#336633">Market....</font></option>
<% do until reMarket.EOF
response.write "<option value= " & reMarket("MarketAreaCode") & "> " & reMarket("MarketDescription") & "</option>"
reMarket.MoveNext
loop
reMarket.close
Set reMarket = Nothing
%>

HERE IS THE FUNCTION CALLED WHEN THE selectMarket FIELD IS CHANGED:
Function selectMarket_OnChange
y=Document.form1.selectMarket.value
sqlAgent="SELECT TOP 100 PERCENT EnteredBy, id AS AgentID, Company FROM dbo.AgentInformation WHERE MarketID=" & y
sqlAgent=sqlAgent & " ORDER BY EnteredBy, Company"
set reAgent=conn.execute(sqlAgent)
End Function

HERE IS THE CONTENT OF THE CONNECTDB.TXT
Set conn = Server.CreateObject(ADODB.Connection)
conn.ConnectionTimeout = 30
conn.CommandTimeout = 30

conn.Open (my connection string)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 16:17:35

What is the error?

Tara

malambing57
Starting Member

5 Posts

Posted - 2005-04-15 : 12:06:20

quote:
Originally posted by tduggan

What is the error?
the error is in the function selectMarket_OnChange
I get an error here because the function cannot recognize the database connection made by the calling procedure

Function selectMarket_OnChange
y=Document.form1.selectMarket.value
sqlAgent="SELECT TOP 100 PERCENT EnteredBy, id AS AgentID, Company FROM dbo.AgentInformation WHERE MarketID=" & y
sqlAgent=sqlAgent & " ORDER BY EnteredBy, Company"
set reAgent=conn.execute(sqlAgent)
End Function
Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-15 : 12:44:50

You didn't post the error message. Not sure how we can help without it.

Tara

malambing57
Starting Member

5 Posts

Posted - 2005-04-15 : 14:53:09

Actually, there is no error message in the function below
Function selectMarket_OnChange
y=Document.form1.selectMarket.value
sqlAgent="SELECT TOP 100 PERCENT EnteredBy, id AS AgentID, Company FROM dbo.AgentInformation WHERE MarketID=" & y
sqlAgent=sqlAgent & " ORDER BY EnteredBy, Company"
set reAgent=conn.execute(sqlAgent)
End Function

The conn variable was used to connect to my database. However, when the function above was called, it does not recognize my connection anymore. When I create a new connection in my function, the old connection (opened in the body of my asp page)is lost so all my previously opened tables were closed as well.

quote:
Originally posted by tduggan

You didn't post the error message. Not sure how we can help without it.

Tara

malambing57
Starting Member

5 Posts

Posted - 2005-04-15 : 15:04:22

quote:
Originally posted by tduggan

You didn't post the error message. Not sure how we can help without it.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources