PDA

Click to See Complete Forum and Search --> : global.asa

jmaresca2005
12-09-2004, 09:10 AM
i have an asp project in which i need to connect to 2 seperete servers. 1 is the webserver that is outside the network the other is a db server that is in the network. the asp pages need to be on the webserver while i retrieve data from the db server that resides on the netwrok. how to i get the connection string to connect to both databases. im sure i have to code for it in the global.asa and the asp page itself. any help would be appreciated.


Webmaster
http://www.ptsdcs.com
lmf232s
12-09-2004, 09:51 AM
you will most likely have two database connection strings.
One to connect to each DB.
As they will be hitting different servers, and different tables.
jmaresca2005
12-09-2004, 09:55 AM
i tried this code but it still doesnt work: global.asa. i emoved info for security reasons, obviously.

Sub Application_OnStart
dim rsTemp, dicTemp
Application("Server") = ""
Application("Server") = ""
Application("UserID") = ""
Application("PWD") = ""
Application("DBName") = ""


Application("Conn_ConnectionString") = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source="





End Sub
Sub Application_OnStart
dim rsTemp, dicTemp
Application("Server") = ""
Application("Server") = ""
Application("UserID") = ""
Application("PWD") = ""
Application("DBName") = ""

Application("Conn_ConnectionString2") = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source="


Webmaster
http://www.ptsdcs.com
russell
12-09-2004, 01:05 PM
'' Remote Server -- pass IP address
Application("conn2") = "" &_
"Provider=SQLOLEDB.1;Data Source=255.255.255.1; " &_
"Database=MyDatabaseName; User ID=dbUserId; " &_
"Password=dbUserPwd;"
'' wrapped to fit here - can be all 1 line.

Change the 255.255.255.1 to the real IP address of your remote sql server. Also change the database to the name of the remote database and the user id and password fields.

Your conn string above should be fine for a local network server.

Also, you can only have one Sub Application_OnStart.
jmaresca2005
12-09-2004, 01:39 PM
i cant seem to get it to work. heres what i did.

Sub Application_OnStart

dim rsTemp, dicTemp
Application("Server") = "server"
Application("Server") = "servername"
Application("UserID") = "id"
Application("PWD") = "password"
Application("DBName") = "db"



Application("Conn_ConnectionString") = "Provider=SQLOLEDB.1;Password=pwd;Persist Security Info=True;User ID=id;Initial Catalog=db;Data Source=server ip(local)"

Application("Conn_ConnectionString2") = "" &_ "Provider=SQLOLEDB.1;Data Source=server ip(remote); " &_ "Database=db; User ID=id; " &_ "Password=pwd;"




End Sub


Webmaster
http://www.ptsdcs.com
russell
12-09-2004, 01:58 PM
what error are you getting? can you ping the ip address?
jmaresca2005
12-09-2004, 02:01 PM
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified


Webmaster
http://www.ptsdcs.com
jmaresca2005
12-09-2004, 02:02 PM
i pinged the ip address and everything is correct as far as the ip address that i am using
russell
12-09-2004, 02:17 PM
need to see the code where you are using the connsection string
jmaresca2005
12-09-2004, 02:19 PM
the code:

<%@ Language=VBScript %>

<%

Const adChar = 129


Dim objConn
Dim objCmd
Dim objRS


Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString2")

If Request.Form("Search") <> "" Then
IF Cstr(Request.Form("compDD")) > 0 then

Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn

objCmd.CommandText="_SP_Customers"
objCmd.CommandType=4

objCmdParameters.Append objCmd.CreateParameter ("@ShipToName",adChar,adParamInput,,Cstr(Request.Form("compDD")))

set objRS = objCmd.Execute

%>

<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function blockError(){return true;}
window.onerror = blockError;
// End -->
</script>
</head>

<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()" target="_new">
<table>
<tr bgcolor=Thistle>
<td>Great Plains Item Number <input type="text" name="GPITEM"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>


<%

Do until objRS.EOF %>
<select name="compDD">
<option value="0">- Please Select a Customer -</option>
<option value="<%trim(objRS.fields("ShipToName"))%>">
<%=objRS.fields("ShipToName")%>
</option>

<%objRS.MoveNext%>
<%Loop%>
<%end if%>
<%end if%>
</td>
<% Set objRS = nothing
objConn.Close
Set objConn = nothing%>
</tr>
</table>
<BR><CENTER>
<input type="hidden" name="Search" Value="1">
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</CENTER>
</FORM>
</body>
</html>


Webmaster
http://www.ptsdcs.com
jmaresca2005
12-09-2004, 02:20 PM
the ShipToName comes from the remote db server


Webmaster
http://www.ptsdcs.com
russell
12-09-2004, 02:25 PM
Right after this line:
Response.Write Application("Conn_ConnectionString2")

Does it look right?

this
Application("Conn_ConnectionString2") = "" &_ "Provider=SQLOLEDB.1;Data Source=server ip(remote); " &_ "Database=db; User ID=id; " &_ "Password=pwd;"

should be
Application("Conn_ConnectionString2") = "Provider=SQLOLEDB.1;Data Source=server ip(remote); Database=db; User ID=id; Password=pwd;"

working?
jmaresca2005
12-09-2004, 02:30 PM
i dont get the driver error anymore but i am only able to view the SEARCh and CLEAR buttons. no input boxes


Webmaster
http://www.ptsdcs.com
jmaresca2005
12-09-2004, 02:41 PM
here is my code for the page,



<%@ Language=VBScript %>

<%

Const adChar = 129


Dim objConn
Dim objCmd
Dim objRS


Set objConn = CreateObject("ADODB.Connection")
Application("Conn_ConnectionString")= "Provider=SQLOLEDB.1;Data Source=server name; Database=db; User ID=id; Password=pwd;"

If Request.Form("Search") <> "" Then
IF Cstr(Request.Form("compDD")) > 0 then

Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn

objCmd.CommandText="_SP_Customers"
objCmd.CommandType=4

objCmdParameters.Append objCmd.CreateParameter ("@ShipToName",adChar,adParamInput,,Cstr(Request.Form("compDD")))

set objRS = objCmd.Execute

%>

<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function blockError(){return true;}
window.onerror = blockError;
// End -->
</script>
</head>

<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()" target="_new">
<table>
<tr bgcolor=Thistle>
<td>Great Plains Item Number <input type="text" name="GPITEM"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>


<%

Do until objRS.EOF %>
<select name="compDD">
<option value="0">- Please Select a Customer -</option>
<option value="<%trim(objRS.fields("ShipToName"))%>">
<%=objRS.fields("ShipToName")%>
</option>

<%objRS.MoveNext%>
<%Loop%>
<%end if%>
<%end if%>
</td>
<% 'Set objRS = nothing
'objConn.Close
'Set objConn = nothing%>
</tr>
</table>
<BR><CENTER>
<input type="hidden" name="Search" Value="1">
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</CENTER>
</FORM>
</body>
</html>


Webmaster
http://www.ptsdcs.com
russell
12-09-2004, 03:25 PM
coupla minor changes below. also, try executing the sp in query analyzer with the param passed (value of Request.Form("compDD")). also, check that Request.Form("compDD") is not empty.

<%

Const adChar = 129


Dim objConn
Dim objCmd
Dim objRS


Set objConn = CreateObject("ADODB.Connection")
Application("Conn_ConnectionString")= "Provider=SQLOLEDB.1;Data Source=server name; Database=db; User ID=id; Password=pwd;"

If Request.Form("Search") <> "" Then
IF Cstr(Request.Form("compDD")) > 0 then

Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn

objCmd.CommandText="_SP_Customers"
objCmd.CommandType=4

objCmdParameters.Append objCmd.CreateParameter ("@ShipToName",adChar,adParamInput,,Cstr(Request.Form("compDD")))

set objRS = objCmd.Execute

%>

<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function blockError(){return true;}
window.onerror = blockError;
// End -->
</script>
</head>

<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()" target="_new">
<table>
<tr bgcolor=Thistle>
<td>Great Plains Item Number <input type="text" name="GPITEM"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>

<select name="compDD">
<%

Do until objRS.EOF %>

<option value="0">- Please Select a Customer -</option>
<option value="<%trim(objRS.fields("ShipToName"))%>">
<%=objRS.fields("ShipToName")%>
</option>

<%objRS.MoveNext
Loop
end if
end if%>
</select>
</td>
<%Set objRS = nothing
objConn.Close
Set objConn = nothing%>
</tr>
</table>
<BR><CENTER>
<input type="hidden" name="Search" Value="1">
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</CENTER>
</FORM>
</body>
</html>
jmaresca2005
12-09-2004, 03:42 PM
i ran the query in query analyzer and it returns all the rows.

I inserted your code and i get this error:

ADODB.Connection error '800a0e78'

Operation is not allowed when the object is closed.

/Users/Search.asp, line 66


LINE 66>>>objConn.Close
russell
12-09-2004, 03:56 PM
where cn is your connection string, try this

<%
Const adChar = 129

Dim cmd
Dim rs

If Request.Form("Search") <> "" Then
If Cstr(Request.Form("compDD")) > 0 then

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = cn
.CommandText="_SP_Customers"
.CommandType=4

.Parameters.Append .CreateParameter ("@ShipToName",adChar,adParamInput,,Cstr(Request.Form("compDD")))

rs.Open .execute
End With

%>
<SCRIPT LANGUAGE="JavaScript"> <!--
function blockError(){return true;}
window.onerror = blockError;
// -->
</script>
</head>

<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()" target="_new">
<table>
<tr bgcolor=Thistle>
<td>Great Plains Item Number <input type="text" name="GPITEM"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>

<select name="compDD">
<option value="0">- Please Select a Customer -</option>
<%

Do until rs.EOF
Response.Write "<option value=""" & rs("ShipToName") & """>" & rs("ShipToName") & "</option>" & vbCrLf
rs.MoveNext
Loop

rs.Close

Response.Write "</select>" & vbCrLf
End If
End If

Set rs = nothing
Set cmd = nothing
%>
</td>
</tr>
</table>
<BR><CENTER>
<input type="hidden" name="Search" Value="1">
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</CENTER>
</FORM>
</body>
</html>
jmaresca2005
12-09-2004, 03:59 PM
i do not get an error but i am still on able to view the 2 buttons (Search,Clear) without any input boxes or the dropdown
russell
12-09-2004, 04:08 PM
check the value of

Request.Form("Search")
Cstr(Request.Form("compDD"))
jmaresca2005
12-09-2004, 04:10 PM
compDD was judt a name i used to add in there. i changed it to ShipToName and Nothing happens
jmaresca2005
12-09-2004, 04:11 PM
compDD is not a field in my table
russell
12-09-2004, 04:55 PM
your code:
If Request.Form("Search") <> "" Then
IF Cstr(Request.Form("compDD")) > 0 then

Well if either of these proves false, your query never gets executed. nor does the <select> element get written.

You are also passing Request.Form("compDD") to the SP as a param. If it's empty... what does your query return...
jmaresca2005
12-09-2004, 05:07 PM
the compDD is a drip down that populates with a list of customers so the user can do a search off of that. the next page is the page that displays the data.


<%


Const adVarChar = 200
Const adChar = 129
Const adDate = 7
Const adParamInput = &H0001

Dim objConn
Dim objCmd
Dim objRS
Dim GPITEM
Dim EXPSHIP
Dim ShipToName

If len(trim(Request("GPITEM"))) = 0 then
GPITEM = "%"
Else
GPITEM = "%" & Request("GPITEM") & "%"
End if

'If len(trim(Request("EXPSHIP"))) = 0 then
' EXPSHIP = "%"
'Else
' EXPSHIP = "%" & Request("EXPSHIP") & "%"
'End if
If len(trim(Request("ShipToName"))) = 0 then
ShipToName = "%"
Else
ShipToName = "%" & Request("ShipToName") & "%"
End if

'*************** MAKE CONNECTION ********************
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString2")

Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn

'Set objConn = CreateObject("ADODB.Connection")
'objConn.Open Application("Conn_ConnectionString2")

'Set objCmd=Server.CreateObject("ADODB.Command")
'objCmd.ActiveConnection=objConn
'******* STORED PROCEDURE CALL********
objCmd.CommandText="_SP_SearchInfo"
objCmd.CommandType=4
objCmd.Parameters.Append objCmd.CreateParameter ("@GPITEM",adChar,adParamInput,21,GPITEM)
'objCmd.Parameters.Append objCmd.CreateParameter ("@EXPSHIP",adDate,adParamInput,,EXPSHIP)
objCmd.Parameters.Append objCmd.CreateParameter ("@CUSTOMER",adChar,adParamInput,65,ShipToName)

set objRS = objCmd.Execute
%>

<html>
<title></title>
<head>
</head>

<body>
<TABLE border="1" bordercolor="black" width="100%" >
<tr>

<td width="7%" align="center" bgcolor="white">GP Item #</td>
<td width="7%" align="center" bgcolor="white">Expected Ship Date</td>
<td width="6%" align="center" bgcolor="white">Customer</td>

<td width="6%" align="center" bgcolor="white">Update</td>

</tr>

<%Dim counter
counter = 1

Do until objRS.EOF

if counter mod 2 = 0 then
response.write("<TR bgcolor='white'>")
else
response.Write("<TR bgcolor='white'>")
end if%>

<td align=center><font size=1><%=objRS.fields("GPITEM")%></font></td>
<td align=center><font size=1> <%'=objRs.fields("EXPSHIP")%> </font></td>
<td align=center><font size=1><select name="ShipToName"><%=objRS.fields("ShipToName")%></select></font></td>
<td align=center bgcolor="black">
<a href="update.asp?GPITEM=<%=objRS.fields("GPITEM")%>&EXPSHIP=<%=objRS.fields("EXPSHIP")%>&ShipToName=<%=objRS.fields("ShipToName")%>"></a></td>


</tr>
<%counter = counter + 1
objRS.MoveNext
Loop

' set objAssignedRS = nothing
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>

</table>


<center><a href="javascript:window.close()"><font size="2" ><b>Close Window</b></font></a></center>
</BODY>
</HTML>