SQL Server Imoprt vb.net Application

[EDN Admin]

Hi Team,
I have got a vb.net application which imports data into sql server and so forth.
I have used bulk import to import data into sql server however never realised that when deployed on remote server it will look for the data on the same machine rather than local machine.
For instance I have used file dialog to let user select a file to import however the path being supplied for local machine via sql statement looks for the same on server machine which it wont find for obvious reason. It gives me operating system error 5, file not found.
Can any one please help me find another way to deal with this problem, i.e. substitute to using bulk insert to import data or how can I deal with bulk insert issue. I have copied my code below.
I really appreciate all your help. Try
OpenFileDialog1.Filter = "Text Files | *.csv"
OpenFileDialog1.DefaultExt = "csv"
OpenFileDialog1.Title = "Import Organisation Data : "
OpenFileDialog1.ShowDialog()

Dim sqlConn As SqlConnection = modSQLConn.SQLConn
If OpenFileDialog1.FileName <> "" Then

If sqlConn IsNot Nothing Then

Dim sqlImport As String
Dim sqlClearTable As String
Dim sqlMergeTable As String

Dim result As SqlDataReader

sqlClearTable = "TRUNCATE Table " & sqlConn.Database & ".dbo.tbl_Organisation_Temp"
sqlImport = "BULK INSERT " & sqlConn.Database & ".dbo.tbl_Organisation_Temp FROM " & System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & "" & OpenFileDialog1.SafeFileName & " WITH (FIRSTROW = 2, FIELDTERMINATOR = ,, ROWTERMINATOR = n)"
sqlMergeTable = "SP_Import_Organisation"
MsgBox(sqlImport)

Clear Table
Dim SqlCommClearTable As New System.Data.SqlClient.SqlCommand(sqlClearTable, sqlConn)
SqlCommClearTable.ExecuteScalar()

Bulk Import
Dim SqlCommImportTable As New System.Data.SqlClient.SqlCommand(sqlImport, sqlConn)
result = SqlCommImportTable.ExecuteReader()

Merge table
Dim SqlCommMergeTable As New System.Data.SqlClient.SqlCommand(sqlMergeTable, sqlConn)
SqlCommMergeTable.ExecuteScalar()

result = SqlCommMergeTable.ExecuteReader
If result.RecordsAffected <> 0 Then
Clear Temp Table
SqlCommClearTable.ExecuteScalar()
sqlConn.Close()
result = Nothing
MessageBox.Show("Import Successful...", Me.Text & " - Import Success :", MessageBoxButtons.OK)
Me.Tbl_OrganisationTableAdapter.Fill(Me.DataSet_db_analytics.tbl_Organisation)
Me.Refresh()
Else
Clear Temp Table
SqlCommClearTable.ExecuteScalar()
sqlConn.Close()
result = Nothing
MessageBox.Show("Import Unsuccessful..., Try Again.", Me.Text & " - Import Failure :", MessageBoxButtons.OK)
End If
Else
MessageBox.Show("No Database Connection")
End If
Else
Exit Sub
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Import Exception Error :", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

View the full article