Ir para conteúdo

POWERED BY:

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

xanburzum

[Resolvido] exportar dados do DB em um arquivo de texto

Por , em ASP Clássico

Recommended Posts

xanburzum    169

xanburzum
Postado

Usando este código, você pode exportar dados do SQL Server (DB ou outra) em um arquivo de texto no computador de clientes que utilizam registros ADO , sem qualquer tipo de fluxo ou sistema de objectos - "conjunto de registros para arquivo de texto no cliente".

 

arquivo default.asp

<%@ Language=VBScript %>
<html>
<head>
</head>
<body>

<form method="POST" action="down.asp">
<table border="0" cellpadding="0" cellspacing="0" width="471">
  <tr>
    <td width="471" colspan="2" align="center" height="60" valign="top"><font color="#FF0000"><b>EXPORT
      to text file (*.txt)</b></font></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>SQL Server:</b></td>
    <td width="320"> <input type="text" name="srvSQL" size="50" id="srvSQL"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>Database:</b></td>
    <td width="320">
    <input type="text" name="dbSQL" size="50" id="dbSQL"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>User:</b></td>
    <td width="320">
    <input type="text" name="usrSQL" size="50" id="usrSQL"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>Password:</b></td>
    <td width="320">
    <input type="password" name="pasSQL" size="50" id="pasSQL"></td>
  </tr>
  <tr>
    <td width="471" align="center" colspan="2">
      <hr>
    </td>
  </tr>
  <tr>
    <td width="147" align="right"><b>SQL String:</b></td>
    <td width="320">
    <input type="text" name="strSQL" size="50" id="strSQL"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>Delimitador de coluna:</b></td>
    <td width="320"> <input type="text" name="colDim" size="50" id="colDim"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>Texto delimitador de campo:</b></td>
    <td width="320"> <input type="text" name="fldDim" size="50" id="fldDim"></td>
  </tr>
  <tr>
    <td width="147" align="right"><b>Separador de data: </b></td>
    <td width="320"> <input type="text" name="dateDim" size="50" id="DateDim"></td>
  </tr>
  <tr>
    <td width="471" colspan="2" align="center"><input type="submit" value="Submit" name="btSubmit" ID="btSubmit"></td>
  </tr>
</table>
</form>

</body>
</html>

down.asp

<%@ Language=VBScript %>

<!--#include file="adovbs.inc"-->
<!--#include file="functions.asp"-->

<%
Dim objCn, objRS, strSQL, strCn, BinData, strData
Dim objFld, colDim, fldDim, dateDim, errLog

strCn="Provider=SQLOLEDB.1;"
strCn=strCn & "Password=" & Request.Form("pasSQL") & ";"
strCn=strCn & "Persist Security Info=True;"
strCn=strCn & "User ID=" & Request.Form("usrSQL") & ";"
strCn=strCn & "Initial Catalog=" & Request.Form("dbSQL") & ";"
strCn=strCn & "Data Source=" & Request.Form("srvSQL")

strSQL = Request.Form("strSQL")
colDim = Request.Form("colDim")
fldDim = Request.Form("fldDim")
dateDim = Request.Form("dateDim")

errLog=OpenCn(strCn, strSQL)

If errLog<>0 Then
Response.Write "Access denied!"
Response.End
End If

Response.AddHeader "Content-Disposition", "attachment;filename=test.txt" 
Response.ContentType = "text/plain"

Do While Not objRS.EOF

strData=""

For i=0 To objRS.Fields.Count-1

If IsNull(objRS.Fields(i).Value) Then
Select case objRS.Fields(i).Type
	   case adVarchar, adDBTimeStamp objFld=fldDim & fldDim
	   case else objFld=""
End Select
Else
Select case objRS.Fields(i).Type
	   case adDBTimeStamp objFld=fldDim & Trim(ConvDate(objRS.Fields(i).Value, dateDim)) & fldDim
	   case adInteger, adSmallInt, adUnsignedTinyInt objFld=Trim(objRS.Fields(i).Value)
	   case else objFld=fldDim & Trim(objRS.Fields(i).Value) & fldDim
End Select
End If

strData=strData & colDim & CStr(objFld)

Next

strData=strData+Chr(13)+Chr(10)

strData=Right(strData, Len(strData)-1)

BinData=StringToBin(strData)

Response.BinaryWrite BinData

objRS.MoveNext
Loop

Call CloseCn()
%>

functions.asp

<%
Function OpenCn(strCn, strSQL)

On Error Resume Next

Set objCn  =  Server.CreateObject("ADODB.Connection")
Set objRS  =  Server.CreateObject("ADODB.Recordset")	
	
With objCn
	.CursorLocation = adUseClient
	.ConnectionTimeout = 15
	.CommandTimeout = 30
	.ConnectionString = strCn
	.Open
End With

With objRS
	.ActiveConnection = objCn
	.CursorLocation = adUseClient
	.CursorType = adOpenForwardOnly
	.LockType = adLockReadOnly
	.Source = strSQL
	.Open
	Set .ActiveConnection = Nothing
End With

OpenCn=Err.number

End Function

'~~~~~~~~~~~~~

Function CloseCn()

objRS.Close
Set objRS = Nothing
Set objCn = Nothing

End Function

'~~~~~~~~~~~~~

Function StringToBin(StringStr)
Dim strRtn

strRtn = ""
    
For i = 1 To Len(StringStr)
	Char   = Mid(StringStr, i, 1)
	strRtn = strRtn & ChrB(Asc(Char))
Next

StringToBin = strRtn

End Function

'~~~~~~~~~~~~~

Function ConvDate(inDate, dateDim)

Dim TempMonth, TempDay

If Month(inDate) <= 9 Then
	TempMonth = CStr("0" & Month(inDate))
Else
    TempMonth = CStr(Month(inDate))
End If

If Day(inDate) <= 9 Then
    TempDay = CStr("0" & Day(inDate))
Else
    TempDay = CStr(Day(inDate))
End If
   
ConvDate = TempDay + dateDim + TempMonth + dateDim + CStr(Year(inDate))

End Function

'~~~~~~~~~~~~~

%>

Compartilhar este post

Link para o post
Compartilhar em outros sites
Ir para a lista de tópicos ASP Clássico
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.

  Aceito