Export csv files from user select query in asp classic

A small database system created. User can type the queries and click query button to display out all the selected record. User can write any select queries to get  record of any table in database.
<%
Dim strConn, strScriptName

strScriptName = Request.ServerVariables("Script_Name")

strConn = Application("eDSN")

Dim strSQL, boolQuery
strSQL=""


strSQL = Request.QueryString("SQL")
boolQuery = Request.QueryString("boolQuery")


Dim objRS, objConn
set objConn = server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.RecordSet")
%>
<head>
<script language="Javascript">
    function Submit_Query(){
        var SQL = SQL_Form.SQL.value;
        if (SQL=="") return false;
        window.location="<%=strScriptName%>?boolQuery=True&SQL=" + URLEncoding(SQL);
    }
    function Submit_Execute(){
        var SQL = SQL_Form.SQL.value;
        if (SQL=="") return false;
        if (confirm("Are you sure export to csv file?")){


window.location="csv.asp?SQL="  + SQL;

        }
    }
</script>
</head>
<form name="SQL_Form" onSubmit="return false;">
        <table>
            <tr>
                <td style="font-weight:bold">SQL Statement: </td>
            </tr>
            <tr >
                <td><textarea rows=5 cols=70 name="SQL"><%=Server.HTMLEncode(strSQL)%></textarea><br><input class="button" type=button value="Query" onClick="Submit_Query();">&nbsp;
                <a><input class="button" type=button value="Export"  onClick="Submit_Execute();"></a>&nbsp;
                <input type=button style="cursor:hand;width:75px" value="Cancel" onClick="window.location='ConsultantMenu.asp'"></td>
            <tr>
        </table>
</form>

Open in new window


From above coding, when user type in the select query and click export button, it will redirect to csv.asp to create a csv files from the selected query.
csv.asp
<%Option Explicit%>
<%
Dim strConn, strScriptName

strScriptName = Request.ServerVariables("Script_Name")

strConn = Application("eDSN")

Dim strSQL, boolQuery
strSQL=""

strSQL = Request.QueryString("SQL")
boolQuery = Request.QueryString("boolQuery") 'apa ini

sub Write_CSV_From_Recordset(RS)
  if RS.EOF then
        exit sub
    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    Separator = ""
    for i = 0 to RS.Fields.Count - 1
        Field = RS.Fields(i).Name
        if RX.Test(Field) then
            Field = """" & Replace(Field, """", """""") & """"
        end if
        Response.Write Separator & Field
        Separator = ","
    next
    Response.Write vbNewLine

    do until RS.EOF
        Separator = ""
        for i = 0 to RS.Fields.Count - 1
            Field = RS.Fields(i).Value & ""
            if RX.Test(Field) then
                Field = """" & Replace(Field, """", """""") & """"
            end if
            Response.Write Separator & Field
            Separator = ","
        next
        Response.Write vbNewLine
        RS.MoveNext
    loop
end sub
 	
Dim objRS, objConn
set objConn = server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM TABLE_NAME_HERE", "CONNECTION_STRING_HERE", 0, 1

Write_CSV_From_Recordset objRS
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
%>

Open in new window

But i know there are wrong in csv.asp. Hope someone may help. Because user can select any record they like, and i do not know how to handle it to export csv files.
Jan tanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
it will create a big threat to allow users to select/export the data by using allowing them to write their own SQL statement.

I suggest you replace that with a predefined dropdown list, etc instead.

when you troubleshooting your scripts, tell us what's the errors you have encountered.
1
Jan tanAuthor Commented:
Hmm thanks for your suggestion! but this is what my teacher want..if let user to create their own select statement is it will possible to export them to csv files?
0
Ryan ChongCommented:
ok, so can you tell us which line of codes causing the error? we will try to provide suggestions
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jan tanAuthor Commented:
ADODB.Recordset error '800a0ea9'

Provider is not specified and there is no designated default provider.

/tr/csv.asp, line 57

thank you for your help :)

due to i don't know what the user will select so i'm not sure what to code at line 57..it's some sample code to export csv files that i found and modified from google.
0
Ryan ChongCommented:
>>Provider is not specified and there is no designated default provider.
you need to put the correct connectionstring into your scripts. it means to let your scripts to connect to a backend database.

try modify:

Dim objRS, objConn
set objConn = server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM TABLE_NAME_HERE", "CONNECTION_STRING_HERE", 0, 1

to:

Dim objRS, objConn
set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString = <your Connection String>
objConn.Open

set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM TABLE_NAME_HERE", objConn, 0, 1

for connectionstring setting, you can refer to:

http://www.connectionstrings.com/
0
Jan tanAuthor Commented:
sorry to ask, i'm still not really sure, <your Connection String> is it refer to ADODB.Connection?
0
Ryan ChongCommented:
To explain further on ADO.Connection Object as well as ConnectionString... you can read this:

ADO Connection Object
http://www.w3schools.com/asp/ado_ref_connection.asp

and then:

ADO ConnectionString Property
http://www.w3schools.com/asp/prop_conn_connectionstring.asp

so it depends on what's your backend database and its location, user access rights etc, and then we will decide how the connectionstring should look like
1
Jan tanAuthor Commented:
okay thanks!!
Dim objRS, objConn
set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strConn
objConn.Open
set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM TABLE_NAME_HERE", objConn, 0, 1

now the last line got error, which is

OraOLEDB error '80004005'

ORA-00942: table or view does not exist

/tr/csv.asp, line 59
0
Ryan ChongCommented:
>>"SELECT * FROM TABLE_NAME_HERE"

so you need to put in a valid table name there. Do you have similar experience before in doing this?
0
Jan tanAuthor Commented:
valid table name? i don't have similar experience before..but maybe u can give me link to refer.. :)
0
Ryan ChongCommented:
the whole idea is that we need to know the objective of your scripting >> as what you have explained in the question, it's to export data into CSV.

so, in general, we could do it via a server side programmng lauguage, in this case it's ASP.

ASP will connect to a backend database, in order to pull the data, and then export it into a CSV.

so we need to tell the scripts, which table to look for. It means where the data is coming from.
0
Jan tanAuthor Commented:
I never try to do csv files before.. and i'm quite new in asp..but i'll try my best!

Dim valid_table_name
objRS.Open "SELECT * FROM valid_table_name", objConn, 0, 1

like this?
 how can i compare the user input table is same as the database's table?
0
Ryan ChongCommented:
>> how can i compare the user input table is same as the database's table?
so, do you have a database you want to connect to? in the database, it should have a list of "tables" in which storing the data for you?

your task to "export data into CSV" would be retrieving one of the tables (or multiple tables) using the ADO objects and then output the result into a CSV file.

are these you have had learnt so far?
0
Jan tanAuthor Commented:
Is it got a specific column name for storing those tables name?
I'm quite confuse how to pull the name of table in database..
0
Ryan ChongCommented:
Did you design a database? Do you have a "table" in it? if yes, what's its fields available?

are you trying to export all the fields and all the records?
0
Jan tanAuthor Commented:
This is my solution in csv.asp. Ryan, thank you for your help!!
<%Option Explicit%>
<%
Dim strConn, strScriptName,strSQL

strConn = Application("eDSN")

strSQL = Request.querystring("SQL")

sub Write_CSV_From_Recordset(RS)
  if RS.EOF then
        exit sub
    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    Separator = ""
    for i = 0 to RS.Fields.Count - 1
        Field = RS.Fields(i).Name
        if RX.Test(Field) then
            Field = """" & Replace(Field, """", """""") & """"
        end if
        Response.Write Separator & Field
        Separator = ","
    next
    Response.Write vbNewLine

    do until RS.EOF
        Separator = ""
        for i = 0 to RS.Fields.Count - 1
            Field = RS.Fields(i).Value & ""
            if RX.Test(Field) then
                Field = """" & Replace(Field, """", """""") & """"
            end if
            Response.Write Separator & Field
            Separator = ","
        next
        Response.Write vbNewLine
        RS.MoveNext
    loop
end sub
 	
Dim objRS, objConn

set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strConn
objConn.Open
set objRS = server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL, strConn, 0, 1

Write_CSV_From_Recordset objRS
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
%>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
ok, so now any error you get from scripts above?

aside of that, can you also tell us what was returned for:

1. strConn (hide the confidential info when necessary)
2. Request.querystring("SQL")
0
Ryan ChongCommented:
ok glad that you solved it yourself cheers
0
Jan tanAuthor Commented:
It's no more error, thank you very much for help.

1. strConn is the connectionstring from application server (Refer to the sms.asp which is the coding at the top) for connection to the back end database.
2. Request.querystring("SQL")  is the user input select statement query.
0
Jan tanAuthor Commented:
I find the way to solve the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.