Read Text File in Server using ASP

We can accomplish by using class Scripting.FileSystemObject.

Here, is an example.

  

<%

 Dim objFileReader,objTxt

 set objFileReader=Server.CreateObject("Scripting.FileSystemObject")

 

 if  objFileReader.FileExists(Server.MapPath("../SampleInv")& "\Terms.txt") then

                        set objTxt=objFileReader.OpenTextFile(Server.MapPath("../SampleInv")& "\Terms.txt",1)

                strTerms=objTxt.ReadAll()

 end if

%>

 

The above sample code reads Terms.txt file in location ../SampleInv of the web server and places the content in variable strTerms.

Format Date in ASP

1. If date is required in format 23-Apr-2008 then, use

day(mydate) & "-" & left(monthname(month(mydate)),3) & "-" & year(mydate)

 

 

2. If date is required in format 23/4/2008 then, use

day(now) & "/" & month(now) & "/" & year(now)

 

Using Microsoft Access with ASP and ADO

To access a database we first need to open a connection to it, which involves creating an ADO Connection object. We then specify the connection string and call the Connection object's Open method.
 
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                   "DBQ=C:\MyDatabases\database.mdb;DefaultDir=;UID=;PWD=;"

To create the ADO Connection object simply Dim a variable.
 
Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")

Then open the connection
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Open ConnectionString
 
Now, to access records in the database, ADO Recordset object has to be defined first
' Create a RecordSet Object
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
' Retrieve the records
rs.Open "SELECT * FROM MyTable", Connection, adOpenForwardOnly, adLockOptimistic
 
adOpenForwardOnly is defined as 0 and specifies that we only wish to traverse the records from first to last.
adLockOptimistic is defined as 3 and allows records to be modified.
 
' This will list all Column headings in the table
Dim item
For each item in rs.Fields
 Response.Write item.Name & "<br>"
next
   
' This will list each field in each record
while not rs.EOF
  
 For each item in rs.Fields
  Response.Write item.Value & "<br>"
 next
  
 rs.MoveNext
wend
 
End Sub

But, we should always remember to close our recordsets and connections.
rs.Close
set rs = nothing
Connection.Close
Set Connection = nothing
 

Item cannot be found in the collection corresponding to the requested name or ordinal

 
The error mainly comes due to wrong spelling of ordinal
like rscode("result") instead of rscode("result_text")
 
But while using procedures it ususally comes
 
I used following statement in ASP
 
set conn=server.CreateObject ("adodb.connection")
strConnString=Session("strConnectString")
conn.Open strConnString
 
strsql = "exec sp_check_Upload_PL '" & strInvNum & "'" 
set rscode = conn.execute(strsql)
 
When, I tried to print result like
response.Write(rsCode(0)),
It resulted in error "Item cannot be found in the collection corresponding to the requested name or ordinal".
Also, the recordset is not opened.
 
The problem can be solved by using
 
set nocount on
 
in the procedure.
 
The Statement stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned.
 

Server.Execute method

The Server.Execute method is a new ASP method, introduced with IIS 5.0 for a first time. You can execute a child ASP page with the Server.Execute and treat the child ASP page as part of the main page.

What are the advantages of using Server.Execute, why did Microsoft introduce a new method?

The main advantage of using Server.Execute is that you can do a dynamic conditional execution of an ASP pages. For example with the SSI includes you include file like this:

<-- #include File = "c:\Inetpub\wwwroot\Your_App\include1.asp" -->

<-- #include Virtual = " /Your_App/include1.asp" -->

One problem with the #include command that it is processed before the page is executed, while the Server.Execute method can be used after the ASP page processing has started. The developers can conditionally execute ASP pages depending the main page business logic or on the user input with Server.Execute method.

You can find an example of Server.Execute in action bellow.

Open any text editor, copy and paste the code below in it and save the file as Main.asp to a web folder:

<%@LANGUAGE="VBSCRIPT"%>
<html>
<head>
<title>Server.Execute Method in Action</title>
</head>
<body>
<% If Request.QueryString("file")="" Then %>
This is the main page!<br><br>
<a href="main.asp?file=file1.asp">Execute File1.asp</a> |
<a href="main.asp?file=file2.asp">Execute File2.asp</a>
<% Else %>
<a href="main.asp">Back</a>
<% Server.Execute Request.QueryString("file") %>
<% End If %>
</body>
</html>


The File1.asp looks like this:

<%@LANGUAGE="VBSCRIPT"% >
<% Response.Write "This is File1" % >


The File2.asp looks like this:

<%@LANGUAGE="VBSCRIPT"%>
<% Response.Write "This is File2, which has longer text :)" %>


After you have saved all 3 ASP pages in your web folder, open a new browser and load the main.asp:

http://localhost/Your_Web_Folder/Main.asp

Now you can click on the File1 and File2 links to trigger the Server.Execute method.

Pass values from ASP to JavaScript code

We often, need to pass variables declared in ASP to JavaScript code. This can be accomplished by following ways

1. By declaring a hidden input type.

We can declare hidden input type as follows to store values retrieved from ASP.

<input type=hidden name=invno value="<%=Request("invnum")%>">

 

Then, while submitting the form through JavaScript, we can use hidden input name as follows.

 

document.frm1.action = 'PLProcess.asp?InvNum=' + document.frm1.invno.value;

document.frm1.submit();

 

   2.  By changing the location of document , which is possible for pop up windows.

opener.document.location='PLProcess.asp? InvNum =<%=invno%>&status="A"&ConfirmFlg="N"';

 

Here, invno is a variable defined in ASP in same page.

 

 

Getting the Physical path from virtual path by using Server Mappath object in ASP

In ASP environment, MapPath method returns the physical path of the file or directory from the virtual path.
 

Here is an example

response.write(Server.MapPath("test.asp")
The output of the above line is here
C:\Inetpub\wwwroot\my_files\test.asp

Note that the object Server.MapPath does not check the existence of actual path, it only maps the given path along with the physical path.
In above case, even if "test.asp" does not exist in present script running  location, it does not show error.
 
 
To get the server root we have to use

response.write(Server.MapPath("\")
output =  C:\Inetpub\wwwroot
 
 
To get the root of the present script running we have to use like this.

response.write(Server.MapPath(".")
output = C:\Inetpub\wwwroot\my_files

Using Excel Database in ASP

 

    ' Open Excel Connection
    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strExcelFile & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

    Response.Write "Excel connection opened<BR>"

    ' Load ADO Recordset with Excel Data
    Set rstExcel = Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "Select * from [Sheet1$A1:B5]", cnnExcel, adOpenStatic 

    Response.Write "Excel Recordset loaded<BR>"
   

 

1. upload your excel file
2. make the connection to the file

Set fso = Server.CreateObject("Scripting.FileSystemObject")
psFilePath = "file.xls"
sPath = server.MapPath("\")
psFilePath = fso.BuildPath(sPath,psFilePath)

'Response.Write psFilePath
' Response.End

Set objConn = Server.CreateObject("ADODB.Connection")
strCnxn = "DRIVER=Driver do Microsoft Excel(*.xls);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 8.0;DriverId=790;DBQ=" & psFilePath & ""
objConn.Open strCnxn

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3 'Static cursor.
objRS.LockType = 2 'Pessimistic Lock.



3. you geting out the cells you want, here its Cell1_name. (todo this you have to define the area in excel file (Excel: Insert->Name->Define)

sql = "Select " & periode & " from Cell1_name"
objRS.Source = sql
objRS.Open
objRS.MoveFirst
If objRS.Fields.Item(0).Value <> "" Then
lev_prosent = round((objRS.Fields.Item(0).Value * 100),1)
Else
lev_prosent = 0
End if

objRS.Close

4. next is to put it into sql database:

if update = "true" Then

' ====
' UPDATE
set g_conn = open_db()

set objRegExp = New RegExp
objRegExp.Pattern = ","
objRegExp.Global = true
Cell1_name= round(cell1_name,1)
set rs = Server.CreateObject("ADODB.Command")
set rs.ActiveConnection = a_conn

rs.CommandText = stSQL
rs.CommandType = 1
rs.Execute intNoOfRecords
'rs.close

5. Close the connection to the sql...

Set objRS = Nothing
objConn.Close
Set objConn = Nothing

> How to import data from Excel to SQL Server using Distributed Query?

It can be done by using OPENDATASOURCE or the OPENROWSET function

 

Here are the examples

 

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')