Help with writing sql statement

Question

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

Answer 1

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.