I have the following script. I'm trying to make "MSDS PDF LINK" to display an actual link on the .asp page. It used to come up looking like (filename#url#). I've inserted a code to trim the text to include only a URL in results, but now I have Invalid Use of Null. Can't figure out why. Here's the whole script:
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Connecting to an Access Database without a DSN</title>
<link rel="stylesheet" type="text/css" href="safety.css">
</head>
<body>
<%
'Declare variables
Dim Connection, sConnString
Dim sSQL, Recordset
Dim strURL, intPoundLocation, strURLFileName, strURLFilePath
sSQL = "SELECT [Chemical Name], [Manufacturer], [Revision Date], [Health], [Fire], [Reactivity], [PPE], [Health 2], [Fire 2], [Instability 2], [Other], [Dept01], [Dept02], [Dept03], [Dept04], [Dept05], [Dept06], [Dept07], [Dept08], [Dept09], [MSDS PDF LINK] FROM MasterDB WHERE ([Dept01] LIKE 'h50%') OR ([Dept02] LIKE 'h50%') OR ([Dept03] LIKE 'h50%') OR ([Dept04] LIKE 'h50%') OR ([Dept05] LIKE 'h50%') OR ([Dept06] LIKE 'h50%') OR ([Dept07] LIKE 'h50%') OR ([Dept08] LIKE 'h50%') OR ([Dept09] LIKE 'h50%') ORDER BY [Chemical Name]"
'SQL Code from the Query in Access Database File
'SELECT [Master MSDS DB].[Chemical Name], [Master MSDS DB].Manufacturer, [Master MSDS DB].[Revision Date], [Master MSDS DB].Health, [Master MSDS DB].Fire, [Master MSDS DB].Reactivity, [Master MSDS DB].PPE, [Master MSDS DB].[Health 2], [Master MSDS DB].[Fire 2], [Master MSDS DB].[Instability 2], [Master MSDS DB].Other, [Master MSDS DB].Dept01, [Master MSDS DB].Dept02, [Master MSDS DB].Dept03, [Master MSDS DB].Dept04, [Master MSDS DB].Dept05, [Master MSDS DB].Dept06, [Master MSDS DB].Dept07, [Master MSDS DB].Dept08, [Master MSDS DB].Dept09, [Master MSDS DB].[MSDS PDF LINK]
'FROM [Master MSDS DB]
'WHERE ((([Master MSDS DB].Dept01) Like "h50*")) OR ((([Master MSDS DB].Dept02) Like "h50*")) OR ((([Master MSDS DB].Dept03) Like "h50*")) OR ((([Master MSDS DB].Dept04) Like "h50*")) OR ((([Master MSDS DB].Dept05) Like "h50*")) OR ((([Master MSDS DB].Dept06) Like "h50*")) OR ((([Master MSDS DB].Dept07) Like "h50*")) OR ((([Master MSDS DB].Dept08) Like "h50*")) OR ((([Master MSDS DB].Dept09) Like "h50*"))
'ORDER BY [Master MSDS DB].[Chemical Name];
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Master MSDS Database 2007.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
connection.Open sConnString
recordset.Open sSQL, connection
recordset.movefirst
'Set variables to values based on recordset field value
strURL = rtrim(recordset("MSDS PDF LINK"))
intPoundLocation = instr(strURL, "#")
strURLFileName = mid(strURL, 1, intPoundLocation-1) 'LINE THAT ERRORS OUT with INVALID USE OF NULL error
strURLFilePath = mid(strURL, intPoundLocation+1, len(strURL)-len(strURLFileName)-2)
response.write "<table width='100%' class='sample'>"
If Recordset.EOF Then
Response.Write "<tr><td>No records returned.</td></tr>"
Else
Response.Write "<TH>Chemical Name</TH><TH>Manufacturer</TH><TH>Revision Date</TH><TH>Health</TH><TH>Fire</TH><TH>Reactivity</TH><TH>PPE</TH><TH>Health 2</TH><TH>Fire 2</TH><TH>Instability 2</TH><TH>Other</TH><TH>Dept01</TH><TH>Dept02</TH><TH>Dept03</TH><TH>Dept04</TH><TH>Dept05</TH><TH>Dept06</TH><TH>Dept07</TH><TH>Dept08</TH><TH>Dept09</TH><TH>MSDS PDF LINK</TH>"
Do While Not recordset.EOF
Response.Write "<tr>"
Response.Write "<td>" & recordset("Chemical Name") & "</td>"
Response.Write "<td>" & recordset("Manufacturer") & "</td>"
Response.Write "<td>" & recordset("Revision Date") & "</td>"
Response.Write "<td>" & recordset("Health") & "</td>"
Response.Write "<td>" & recordset("Fire") & "</td>"
Response.Write "<td>" & recordset("Reactivity") & "</td>"
Response.Write "<td>" & recordset("PPE") & "</td>"
Response.Write "<td>" & recordset("Health 2") & "</td>"
Response.Write "<td>" & recordset("Fire 2") & "</td>"
Response.Write "<td>" & recordset("Instability 2") & "</td>"
Response.Write "<td>" & recordset("Other") & "</td>"
Response.Write "<td>" & recordset("Dept01") & "</td>"
Response.Write "<td>" & recordset("Dept02") & "</td>"
Response.Write "<td>" & recordset("Dept03") & "</td>"
Response.Write "<td>" & recordset("Dept04") & "</td>"
Response.Write "<td>" & recordset("Dept05") & "</td>"
Response.Write "<td>" & recordset("Dept06") & "</td>"
Response.Write "<td>" & recordset("Dept07") & "</td>"
Response.Write "<td>" & recordset("Dept08") & "</td>"
Response.Write "<td>" & recordset("Dept09") & "</td>"
'Response.Write "<td>" & recordset("MSDS PDF LINK") & "</td>"
Response.write "<td><a href='" & strURLFilePath & "'>" & strURLFileName & "</a></td>"
Recordset.MoveNext
Loop
response.write "</table>"
End If
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>
<table border="0" width="100%" id="table1" cellspacing="0" cellpadding="0">
<tr>
<td><font face="Arial" size="2"><a href="safety.htm">back</a></font></td>
</tr>
</table>
</body>
</html>
any ideas?
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Connecting to an Access Database without a DSN</title>
<link rel="stylesheet" type="text/css" href="safety.css">
</head>
<body>
<%
'Declare variables
Dim Connection, sConnString
Dim sSQL, Recordset
Dim strURL, intPoundLocation, strURLFileName, strURLFilePath
sSQL = "SELECT [Chemical Name], [Manufacturer], [Revision Date], [Health], [Fire], [Reactivity], [PPE], [Health 2], [Fire 2], [Instability 2], [Other], [Dept01], [Dept02], [Dept03], [Dept04], [Dept05], [Dept06], [Dept07], [Dept08], [Dept09], [MSDS PDF LINK] FROM MasterDB WHERE ([Dept01] LIKE 'h50%') OR ([Dept02] LIKE 'h50%') OR ([Dept03] LIKE 'h50%') OR ([Dept04] LIKE 'h50%') OR ([Dept05] LIKE 'h50%') OR ([Dept06] LIKE 'h50%') OR ([Dept07] LIKE 'h50%') OR ([Dept08] LIKE 'h50%') OR ([Dept09] LIKE 'h50%') ORDER BY [Chemical Name]"
'SQL Code from the Query in Access Database File
'SELECT [Master MSDS DB].[Chemical Name], [Master MSDS DB].Manufacturer, [Master MSDS DB].[Revision Date], [Master MSDS DB].Health, [Master MSDS DB].Fire, [Master MSDS DB].Reactivity, [Master MSDS DB].PPE, [Master MSDS DB].[Health 2], [Master MSDS DB].[Fire 2], [Master MSDS DB].[Instability 2], [Master MSDS DB].Other, [Master MSDS DB].Dept01, [Master MSDS DB].Dept02, [Master MSDS DB].Dept03, [Master MSDS DB].Dept04, [Master MSDS DB].Dept05, [Master MSDS DB].Dept06, [Master MSDS DB].Dept07, [Master MSDS DB].Dept08, [Master MSDS DB].Dept09, [Master MSDS DB].[MSDS PDF LINK]
'FROM [Master MSDS DB]
'WHERE ((([Master MSDS DB].Dept01) Like "h50*")) OR ((([Master MSDS DB].Dept02) Like "h50*")) OR ((([Master MSDS DB].Dept03) Like "h50*")) OR ((([Master MSDS DB].Dept04) Like "h50*")) OR ((([Master MSDS DB].Dept05) Like "h50*")) OR ((([Master MSDS DB].Dept06) Like "h50*")) OR ((([Master MSDS DB].Dept07) Like "h50*")) OR ((([Master MSDS DB].Dept08) Like "h50*")) OR ((([Master MSDS DB].Dept09) Like "h50*"))
'ORDER BY [Master MSDS DB].[Chemical Name];
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Master MSDS Database 2007.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
connection.Open sConnString
recordset.Open sSQL, connection
recordset.movefirst
'Set variables to values based on recordset field value
strURL = rtrim(recordset("MSDS PDF LINK"))
intPoundLocation = instr(strURL, "#")
strURLFileName = mid(strURL, 1, intPoundLocation-1) 'LINE THAT ERRORS OUT with INVALID USE OF NULL error
strURLFilePath = mid(strURL, intPoundLocation+1, len(strURL)-len(strURLFileName)-2)
response.write "<table width='100%' class='sample'>"
If Recordset.EOF Then
Response.Write "<tr><td>No records returned.</td></tr>"
Else
Response.Write "<TH>Chemical Name</TH><TH>Manufacturer</TH><TH>Revision Date</TH><TH>Health</TH><TH>Fire</TH><TH>Reactivity</TH><TH>PPE</TH><TH>Health 2</TH><TH>Fire 2</TH><TH>Instability 2</TH><TH>Other</TH><TH>Dept01</TH><TH>Dept02</TH><TH>Dept03</TH><TH>Dept04</TH><TH>Dept05</TH><TH>Dept06</TH><TH>Dept07</TH><TH>Dept08</TH><TH>Dept09</TH><TH>MSDS PDF LINK</TH>"
Do While Not recordset.EOF
Response.Write "<tr>"
Response.Write "<td>" & recordset("Chemical Name") & "</td>"
Response.Write "<td>" & recordset("Manufacturer") & "</td>"
Response.Write "<td>" & recordset("Revision Date") & "</td>"
Response.Write "<td>" & recordset("Health") & "</td>"
Response.Write "<td>" & recordset("Fire") & "</td>"
Response.Write "<td>" & recordset("Reactivity") & "</td>"
Response.Write "<td>" & recordset("PPE") & "</td>"
Response.Write "<td>" & recordset("Health 2") & "</td>"
Response.Write "<td>" & recordset("Fire 2") & "</td>"
Response.Write "<td>" & recordset("Instability 2") & "</td>"
Response.Write "<td>" & recordset("Other") & "</td>"
Response.Write "<td>" & recordset("Dept01") & "</td>"
Response.Write "<td>" & recordset("Dept02") & "</td>"
Response.Write "<td>" & recordset("Dept03") & "</td>"
Response.Write "<td>" & recordset("Dept04") & "</td>"
Response.Write "<td>" & recordset("Dept05") & "</td>"
Response.Write "<td>" & recordset("Dept06") & "</td>"
Response.Write "<td>" & recordset("Dept07") & "</td>"
Response.Write "<td>" & recordset("Dept08") & "</td>"
Response.Write "<td>" & recordset("Dept09") & "</td>"
'Response.Write "<td>" & recordset("MSDS PDF LINK") & "</td>"
Response.write "<td><a href='" & strURLFilePath & "'>" & strURLFileName & "</a></td>"
Recordset.MoveNext
Loop
response.write "</table>"
End If
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>
<table border="0" width="100%" id="table1" cellspacing="0" cellpadding="0">
<tr>
<td><font face="Arial" size="2"><a href="safety.htm">back</a></font></td>
</tr>
</table>
</body>
</html>
any ideas?