I need some help writing a query. I have a text file that will be imported on a weekly basis, with 1000+ rows and 5 columns. This i need to import into table tblECR which i have added 2 of my own columns.
The problem is the text file will arrive with current data and new data. The current data may or may not have changed (dates, status etc). How do i go about importing the new data and updating the existing data with the new fields, without deleting the data in the 2 columns i've added. I'm using vs 2005 with a sql 2005 express database.
This is the code i'm using to import the data currently. Clicking the button more than once will obviously just import all the data into the database again.
Private Sub CustomerDataToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerDataToolStripMenuItem.Click
'Clear the dataset
dsimport.Clear()
'Set the file variables
Dim strFileName As String
Dim strFilePath As String
Dim sSlash As Single
'Open the file dialog and select the text file to open
Try
With OpenFileDialog1
'Set the initial dialog options
.Title = "Import Customer data file"
.InitialDirectory = "P:\Ian\"
.FileName = ""
.Filter = "File (*.csv)|*.csv|All files (*.*)|*.*"
If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
Else
MessageBox.Show("No file was selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
'Strip the filename into its seperate portions
sSlash = InStrRev(.FileName, "\")
strFilePath = Mid(.FileName, 1, CInt(sSlash))
strFileName = Mid(.FileName, CInt(sSlash + 1), Len(.FileName))
End With
'Set the connection properties to read the text file
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim conn As New OleDb.OleDbConnection(strConnectionString)
'Open connection with the database.
conn.Open()
'Create new OleDbCommand to return data from the text file
Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM [" & strFileName & "]", conn)
' Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement
Dim objAdapter1 As New OleDb.OleDbDataAdapter
'Pass the Select command to the adapter
objAdapter1.SelectCommand = objCmdSelect
'Fill the DataSet with the information from the file
objAdapter1.Fill(dsimport, "Import")
objAdapter1.AcceptChangesDuringFill = False
'Clean up objects
conn.Close()
Catch ex As Exception
MsgBox(ex.Message).ToString()
Exit Sub
End Try
'Now import the data into the table
Dim sqlcn As New SqlConnection(ConnString)
Dim sqlcmd_ECR As New SqlCommand
Dim dr As DataRow
Try
sqlcn.Open()
'Setup the sql command to enter data into the ECR table
sqlcmd_ECR.Connection = sqlcn
sqlcmd_ECR.CommandText = "Insert into tblECR_Test(ECR_No,Aims_No,ECR_Type) Values(@.a,@.b,@.c)"
'Setup the sql parameters to enter data into the ECR table
sqlcmd_ECR.Parameters.Add("@.a", SqlDbType.Int)
sqlcmd_ECR.Parameters.Add("@.b", SqlDbType.Int)
sqlcmd_ECR.Parameters.Add("@.c", SqlDbType.VarChar, 255)
Try
For Each dr In dsimport.Tables(0).Rows
sqlcmd_ECR.Parameters("@.a").Value = dr(0)
sqlcmd_ECR.Parameters("@.b").Value = dr(1)
sqlcmd_ECR.Parameters("@.c").Value = dr(2).ToString()
sqlcmd_ECR.ExecuteNonQuery()
Next
Catch ex1 As SqlException
MsgBox(ex1.Message).ToString()
Exit Sub
End Try
MessageBox.Show("The text file was successfully imported.", "Customer data import", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex3 As Exception
MsgBox(ex3.Message).ToString()
End Try
sqlcn.Close()
End Sub
ExecuteNonQuery returns an integer that tells you how many rows were affected by the query, so you could run an update first (trying to update the record assuming it is already there) and then if the rows affected is 0 instead of 1, run the insert.
