bcp from vb.net app to sql

Well are you wanting to shell to BCP?
Or are you wanting to just read the CSV, and map to SQL Insert statements, called over and over again?

What I would like to do is take a csv file and import the contents into a sql table called upload from a vb application.

I would need to delete any exiting data in the upload table and then populate with the new data from the file.

Thanks

Two ways

(Easier)
Create a DTS Package to do the job and either store it on the database server or embed it in your application.  Microsoft supplies a CSV ODBC driver
so you can open the CSV as simply another table.  Very easy.  Then in VB.Net just tell SQL-Server/MSDE to run the job

(Harder)
Again, pointing out CSV files can be opened as Database objects, use ADO.NET to open the CSV file, then loop through the CSV table
taking each row and using it to fill a prepared statement.  Any fixes can be done as each row is read.

Cheers
-Bill

thanks, the user needs to upload the file so I have a variable file name.   Here is what I have so far

****************vb.net *******************
Dim ExcelObj = New Excel.Application
        Dim currentfilename
        Me.OpenFileDialog1.FileName = "*.csv"
        If (Me.OpenFileDialog1.ShowDialog() = DialogResult.OK) Then
            currentfilename = OpenFileDialog1.FileName
            'pass currentfilename to stored procedure and upload csv file to upload table in sql
            Dim objCmd As New SqlCommand
            Dim objDR As SqlDataReader
            Connection1.Open()
            'Upload current file name to tblUpload on biz-talk healthdistrict db.
            objCmd.Connection = Connection1
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "usp_Update_Emp11"
            objCmd.Parameters.Add(New SqlParameter("@FILE_NAME", SqlDbType.Text, 20))
            objCmd.Parameters("@FILE_NAME").Value = currentfilename
            objCmd.ExecuteNonQuery()
            Console.WriteLine("Bulk inserted")

            Connection1.Open()


***************sql*********************
CREATE PROCEDURE usp_Update_Emp11

   @FILE_NAME AS VARCHAR(70)
AS
Delete from upload
EXEC('BULK INSERT upload FROM '''+@FILE_NAME+''' WITH  (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')')
GO

usp_update_emp11

It will not let me upload because the data is stored as below and the lastname, firstname are together.  Therefore I get an error on the dob.  thank you


Cxxxxx, Lxxx     11-02-48     58     S1111-02     1-Feb-06     d22     mfc     203.3     12455     124-42-1245     f     401 Taaddd Dr     xxxxx Cxxxxx     xx     8xxxx     XX

I can upload it if ok now if i create a fname lastname but I get " around the name" .  How can I avoid this.

Thanks

Can you paste a sample row?

Also, that looks tab separated, not comma, I would look at either cleaning up the "tsv" file first or transform it during the load.  One way would be to make the first column
fullname, then use a stored proc or query update to split the first and last names.

Cheers,
-Bill