Form validation and naming

have a page that I'm working on that I trying to do the following with:

1. Name a file upload.txt (with codebehind) every time a file is uploaded with a user pressing a submit button.
2. Validate that the file is a .txt file (again with codebehind)
3. Lastly, once the user presses the submit button, that a sql query is run.

I realize that .xml is the proper way to do this but as I'm new to .net, I want to do this with simply inserting the sql query into the codebehind. Here is my codebehind thus far:

 Partial Class _Default
    Inherits System.Web.UI.Page
 

    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick

        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
            Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn
            Try
                File1.PostedFile.SaveAs(SaveLocation)
                Response.Write(Thank you for your submission.)
                Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            End Try
        Else
            Response.Write(Please select a file to upload.)
        End If

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
End Class

Here is the sql query that I want to insert into the codebehind above:

bulk insert dialerresults
from '\\MSBWEB3\data\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Select *
from dialerresults
go

Can any one assist me with this.
Thank you

Doug

I should also mention that the server is running asp.net 3.5 and this is being written in .vb rather than .cs.

Update:

I have since written the following code:

[code]
Imports System.IO
Imports System.Data
Imports System.Data.Sql
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As EventArgs)

If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn
Try
File1.PostedFile.SaveAs(SaveLocation)
Response.Write(Thank you for your submission.)

' get the file that was submitted and check if it was .txt file
Dim theFile As FileInfo = New FileInfo(SaveLocation)
If theFile.Extension <> ".txt" Then
Response.Write(Please submit a text file.)
End If

Dim importPath As String = Server.MapPath("Data") & "\upload.txt"
If File.Exists(importPath) Then
' do something with existing upload.txt file, maybe archive?
End If

' rename the uploaded file to upload.txt for importing
theFile.MoveTo(importPath)

' and bulk import the data:
Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
Dim results As New DataTable

Using con As New SqlConnection(connection)
con.Open()

' execute the bulk import
Using cmd As SqlCommand = con.CreateCommand
cmd.CommandText = "bulk insert dialerresults from '" & importPath & "' " & _
" with ( fieldterminator = ',', rowterminator = '\n' )"
cmd.ExecuteNonQuery()
End Using
End Using

Catch Exc As Exception
Response.Write("Error: " & Exc.Message)
End Try
Else
Response.Write(Please select a file to upload.)
End If

End Sub
End Class

[/code]
And get the error when I debug that SqlConnection is not defined. Can anyone assist with that?

Thank you

Doug

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Doug Ancil16y ago
Update:

I have since written the following code:

[code]
Imports System.IO
Imports System.Data
Imports System.Data.Sql
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As EventArgs)

If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn
Try
File1.PostedFile.SaveAs(SaveLocation)
Response.Write(Thank you for your submission.)

' get the file that was submitted and check if it was .txt file
Dim theFile As FileInfo = New FileInfo(SaveLocation)
If theFile.Extension <> ".txt" Then
Response.Write(Please submit a text file.)
End If

Dim importPath As String = Server.MapPath("Data") & "\upload.txt"
If File.Exists(importPath) Then
' do something with existing upload.txt file, maybe archive?
End If

' rename the uploaded file to upload.txt for importing
theFile.MoveTo(importPath)

' and bulk import the data:
Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
Dim results As New DataTable

Using con As New SqlConnection(connection)
con.Open()

' execute the bulk import
Using cmd As SqlCommand = con.CreateCommand
cmd.CommandText = "bulk insert dialerresults from '" & importPath & "' " & _
" with ( fieldterminator = ',', rowterminator = '\n' )"
cmd.ExecuteNonQuery()
End Using
End Using

Catch Exc As Exception
Response.Write("Error: " & Exc.Message)
End Try
Else
Response.Write(Please select a file to upload.)
End If

End Sub
End Class

[/code]
And get the error when I debug that SqlConnection is not defined. Can anyone assist with that?

Thank you

Doug