I have the following code:
and what I need to do is check for a few variables before the file is uploaded to the server for a bulk insert. What I'm needing to check is that the time/date fields are correct, that the correct number of fields are there (this is being bulk inserted into a database.)
We currently have the database laid out as
ID
PHONE NUMBER
PATIENT NAME
DR NAME
APPOINTMENTDATE
APPOINTMENTTIME
CURRENTDATE
RESULTS
The appointmentdate and time are laid out as
20100601 2:30, 20100601 2:30
as their format. What I need to test against is that all files that are being uploaded to my server meet that format.
Can anyone think of a relatively easy way to check for these things before the bulk insert is done?
Can this be done in the code behind?
Thanks
Doug
Code:
Private Function UploadFile(ByVal SavePath As String) As Boolean
Dim fileWasUploaded As Boolean = False 'indicates whether or not the file was uploaded
'Checking if the file upload control contains a file
If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
Try
'checking if it was .txt file BEFORE UPLOADING IT!
'You used to upload it first...but the file could be a virus
If File1.FileName = ("doug.csv") = False Then
'The file is not the expected type...do not upload it
'just post the validation message
message.Text = "Sorry, thats not the correct file."
message2.Text = "Please locate and upload 'doug.csv'"
Else
'The file is a .txt file
'checking to see if the file exists already
'If it does exist Deleting the existing one so that the new one can be created
If IO.File.Exists(SavePath) Then
IO.File.Delete(SavePath)
End If
'Now upload the file (save it to your server)
File1.PostedFile.SaveAs(SavePath)
'After saving it check to see if it exists
If File.Exists(SavePath) Then
'Upload was sucessful
message.Text = "Thank you for your submission."
fileWasUploaded = True
Else
'the file was not saved
message.Text = "Unable to save the file."
End If
End If
Catch Exc As Exception
'We encountered a problem
message.Text = "Your file was not in the correct format. Please contact MSB Customer Service at 512-467-5200."
End Try
Else
'No file was selected for uploading
message.Text = "Please select a file to upload."
End If
Return fileWasUploaded
End Function
Private Sub SaveToDatabase(ByVal SavePath As String)
Try
Dim sqlQueryText As String = _
"BULK INSERT dialerresults " + _
"FROM '" & SavePath & "' " + _
"WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"
' and bulk import the data:
'If ConfigurationManager.ConnectionStrings("Dialerresults") IsNot Nothing Then
'Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
Dim connection As String = "data source=10.2.1.40;initial catalog=IVRDialer;uid=xxxxx;password=xxxxx;"
Using con As New SqlConnection(connection)
con.Open()
' execute the bulk import
Using cmd As New SqlCommand(sqlQueryText, con)
cmd.ExecuteNonQuery()
End Using
End Using
'Else
'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!"
'End If
Catch ex As Exception
message.Text = "Your file was not in the correct format. Please contact MSB Customer Service at 512-467-5200."
End Try
End Sub
End Class
We currently have the database laid out as
ID
PHONE NUMBER
PATIENT NAME
DR NAME
APPOINTMENTDATE
APPOINTMENTTIME
CURRENTDATE
RESULTS
The appointmentdate and time are laid out as
20100601 2:30, 20100601 2:30
as their format. What I need to test against is that all files that are being uploaded to my server meet that format.
Can anyone think of a relatively easy way to check for these things before the bulk insert is done?
Can this be done in the code behind?
Thanks
Doug