Automated file upload using asp

[ucfcarver]

Unfortunate I am aware of my unique situation. I have been working on it for a little while now. Every time it seems I find the answer it turns out not to work.

However having talked to the individuals in charge of the server I have found out that asp and asp.net are installed and working on the server and the server has a web address.

I assume this means it is also a web server but as I mentioned in my first post this is a whole new area of work for me, and the learning has been slow.

So I guess the question now is there a way to use asp or asp.net connection in linux?

[debtboy]

Here is your solution in ASP.NET
made w/ Visual Studio. It would have
to be transferred (along with associated files
over to a virtual directory
in IIS on the SQL machine.

Imports System.web
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

        'DATA
        '**********
        Dim var_sqlconnection As New SqlConnection
        Dim var_sqldataadapter As New SqlDataAdapter
        Dim var_sqldataset As New DataSet
        Dim var_sqlselectcommand As New SqlCommand
        Dim var_sqlinsertcommand As New SqlCommand
        Dim var_sqldeletecommand As New SqlCommand
        Dim var_sqlcommandbuilder As New SqlCommandBuilder
        '**********

        Dim var_readline As String
        Dim var_sql As String
        Dim var_string As String


        Dim var_sqlconnectionstring As String = "data source=SERVER;initial catalog=DATABASE;persist security info=False;user " & _ 
                                                 "id=USER;password=PASSWORD;packet size=4096"

            var_streamreader = File.OpenText(var_file)

            Do
                var_readline = var_streamreader.ReadLine
                var_string = var_readline


                'IGNORE FIRST LINE, IF NECESSARY
                If (Mid(var_readline, 1, 3) <> "SOMETHING UNIQUE TO FIRST LINE") Then

                    Dim var_one As Integer
                    Dim var_two As Integer
                    Dim var_three As Integer
                    Dim var_four As Integer
                    Dim var_five As String
                    Dim var_count As Integer
                    Dim var_temp As String


                    '***EXTRACT VALUES FROM CSV ***
                    Try
                        'PAYRID
                        var_count = InStr(var_string, ",", CompareMethod.Text)
                        var_one = Mid(var_string, 1, (var_count - 1))
                        var_temp_string = Mid(var_string, (var_count + 1))
                        var_string = var_temp_string

                        var_count = InStr(var_string, ",", CompareMethod.Text)
                        var_two = Mid(var_string, 1, (var_count - 1))
                        var_temp_string = Mid(var_string, (var_count + 1))
                        var_string = var_temp_string

                        var_count = InStr(var_string, ",", CompareMethod.Text)
                        var_three = Mid(var_string, 1, (var_count - 1))
                        var_temp_string = Mid(var_string, (var_count + 1))
                        var_string = var_temp_string

                        var_count = InStr(var_string, ",", CompareMethod.Text)
                        var_four = Mid(var_string, 1, (var_count - 1))
                        var_temp_string = Mid(var_string, (var_count + 1))
                        var_string = var_temp_string

                        var_five = Mid(var_string, 1)


                    Catch ex As Exception
                        'ERROR CODE HERE ***
                    End Try


                    'UPLOAD TO DB ***
                    var_sql = ("INSERT INTO ROUTE_1 " & _
                                "VALUES(var_one, " & _
                                "var_two, " & _
                                "var_three, " & _
                                "var_four, " & _
                                "var_five)")

                    Try
                        var_sqlconnection.ConnectionString = var_sqlconnectionstring
                        var_sqlinsertcommand.CommandType = CommandType.Text
                        var_sqlinsertcommand.CommandText = var_sql
                        var_sqldataadapter.InsertCommand = var_sqlinsertcommand
                        var_sqldataadapter.InsertCommand.Connection = var_sqlconnection
                        var_sqldataadapter.InsertCommand.Connection.Open()
                        var_sqldataadapter.InsertCommand.ExecuteNonQuery()
                        var_sqldataadapter.InsertCommand.Connection.Close()


                    Catch Iex As Exception
                        'ERROR CODE HERE ***
                    End Try
                End If

            Loop Until (var_readline Is Nothing)

            var_streamreader.Close()
            var_streamreader.Dispose()

I had hoped to stay away from a solution such as this
as it requires Visual Studio and a understanding of
that whole process.
When you get this working, you need to create
a simple task to detect if the csv file has been transferred
and then to kick this program off.

BTW...
The "code behind" ASP.NET language
used was VB.

Any questions (I know you have some), let me know
and we can work through it.

Good Luck

[ucfcarver]

You were right I have questions

I have been researching asp.net to try to understand how it works.

What i have got so far is that there are basically two parts to the file upload. I keep reading about a form to upload the file which triggers a file on the server that handles the rest of the upload.

I am looking at the code you gave me and I am not sure which part it is. The first portion looks like it would go on the linux box and make the connection to the server. However the second half looks like it would be on the server and handle inserting the received data. If you could explain what I am missing that would be great.

Also I keep reading about this form to upload a file and it always has a user selecting the file to upload. How can I it so that the file does not need to be selected.

[debtboy]

Hi,
The code I posted is ASP.NET code to be run via IIS webserver
on the SQL server machine.
A virtual directory (in IIS) would need to be created and this code as well as
all the support files Visual Studio created would be transferred there.

The code opens a csv file, extracts the data and uploads it into a database.

It expects the csv file location to be in the var_file variable

var_streamreader = File.OpenText(var_file)

For example, we could (through samba) share the csv file location
on Ubuntu with the sql server machine which would map.
E:\csv_file_path\csv_file.csv

You could also upload the csv file to a particular location on the sql server.

Dim var_file as string = "E:\csv_file_path\csv_file.csv"

Now this line makes more sense.

var_streamreader = File.OpenText(var_file)

It uses a native sql server driver so no database interface problems,
but now you need to create the application in Visual Studio as an ASP.NET
app.

I hope you have some experience there, because VS has a bit of
a learning curve.

[ucfcarver]

I do not have experience with visual studio, however I have downloaded the express edition and am attempting to learn it.

One question I still have is in your code it is expecting the file to be in the "var_file" how do I get the file from the linux computer to the servers "var_file"
I was attempting to use php beforwe i found out that the server does not have php on it. have also heard of ftp but have not gotten to deep into it. Is there any particular approach i should use?

[debtboy]

Give me a little time and I'll put together a
step by step for you.

EDIT:
I can't give a step by step because a lot depends
on you individual configurations on Ubuntu and on
IIS/SQL server. Permissions, configurations as well as
program installations and versions all affect the result
and may require minor tweaks to the code.

[debtboy]

I'll assume FTP is enabled on the IIS/SQL server machine.
A simple script will upload the file. (see below)

You probably want this to be a cron job, to run every
so often as to hit when the wireless is in range.
You could also add a check for the wireless address
through ifconfig if you like. (something like)
sudo ifconfig wlan0 | grep 'inet addr:'

#!/bin/env bash

HOST='ftp.your server name'
USER='your user id'
PASSWORD='your password'
FILE='your file.csv'

ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put $FILE
quit
END_SCRIPT
exit 0

[ucfcarver]

Thank you for all your help, you have gone beyond my expectations to help me.

When the idea we were working on fell through i went back and stated looking at the tsql approach again. After some more research i was able to come up with a working script using a similar function. I used the isql command found with the iodbc package.

I have added the working script tot he bottom and thank you again for all your help.

#!/bin/bash
#
#
# Here is the beginning of the script:
#
Isql_auth='isql DSN user password'
#
# Prepare the SQL statements using the input file:
#
awk '{print "INSERT INTO Route_1 VALUES("$0")";}' rider1.txt >outFile.sql

#
# Execute the Isql:
#
$Isql_auth < outFile.sql

#
# Done.
#
exit 0

[debtboy]

AWESOME!!!!

Great work ucfcarver,
Isn't it a great feeling when you solve a tough problem like that.

I bet you'll be spending more time on Linux now.

Keep posting!!