How to File Upload csv to SQL database on intranet VB.NET site

Question

I am having problems uploading CSV files to my SQL Database. I am trying to achieve this through the File Upload technique on an intranet site. The intranet site is for me and another user to have the ability to file upload these CSVs (in case one of us is out).

I've used the following;

    Dim errorList As String = String.Empty
    Dim returnValue As Integer = 0
    Dim SQLCon As New SqlClient.SqlConnection
    Dim SQLCmd As New SqlClient.SqlCommand
    Dim ErrString As String

    Dim countRecs As Integer = 0
    Dim batchid As Integer = GetNextBatchNumber("PartsImport")

    Using tf As New TextFieldParser(fileandpath)
        tf.TextFieldType = FileIO.FieldType.Delimited
        tf.SetDelimiters(",")


        SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings("DB00ConnectionString").ConnectionString
        SQLCon.Open()
        SQLCmd.CommandType = CommandType.Text
        SQLCmd.Connection = SQLCon

        Dim recAdded As String = Now.ToString
        Dim row As String()
        While Not tf.EndOfData

            Try
                row = tf.ReadFields()
                Dim x As Integer = 0
                If countRecs <> 0 Then
                    Try
                      SQLCmd.CommandText = "insert into [Base].[PartsImport] " _
                      + " (ID,PartName,PartID,Price,ShipAddress) " _
                      + " values ('" + row(0) + "','" + row(1) + "','" _
                      + row(2) + "','" + row(3) + "','" + row(4) + "')"
                        SQLCmd.ExecuteNonQuery()

                    Catch ex As Exception
                        ErrString = "Error while Creating Batch Record..." & ex.Message
                    End Try
                End If

            Catch ex As MalformedLineException
                errorList = errorList + "Line " + countRecs + ex.Message & "is not valid and has been skipped." + vbCrLf
            End Try
            countRecs = countRecs + 1
        End While

        SQLCon.Close()
        SQLCon.Dispose()
        SQLCmd.Dispose()

When I click the form button to upload, it gives me a success message but when I look in the actual table, it is still blank.

Any ideas? Appreciate it

Thanks Dave

There's more likely some error somewhere you aren't seeing. You might want to run SQL Profiler to see what SQL is actually hitting the database. Also, you might be having issues with your CSV import. I have used this CSV library lately and loved it: https://github.com/JoshClose/CsvHelper. It's available on NuGet, too. — Gromer, Sep 18 '12 at 15:44
What happen if you issue a select statement using the same connection: SQLCmd.CommandText = "SELECT * [Base].[PartsImport]"; — Mark Kram, Sep 18 '12 at 15:54
I did the SELECT statement and no difference or addition to the SQL database still. I added more of my code. Hope this gives a clearer idea of whats wrong. — Dave Constantine, Sep 18 '12 at 18:17

score 1 · Accepted Answer · answered Sep 18 '12 at 16:14

private void UploaddataFromCsv()
        {
            SqlConnection con = new SqlConnection(@"Data Source=local\SQLEXPRESS;Initial Catalog=databaseName;Persist Security Info=True;User ID=sa");
            string filepath = "C:\\params.csv";
            StreamReader sr = new StreamReader(filepath);
            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;
            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while ( !sr.EndOfStream )
            {
                value = sr.ReadLine().Split(',');
                if(value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }
            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = "[Base].[PartsImport]";
            bc.BatchSize = dt.Rows.Count;
            con.Open();
            bc.WriteToServer(dt);
            bc.Close();
            con.Close();
        }

Nice example but I suggest adding some comments to your answer to describe what you're doing. — Nick Bork, Sep 18 '12 at 20:40

score 0 · Answer 2 · answered Sep 18 '12 at 20:34

Try catching a SqlException and seeing if there is a formatting issue with your request. If you have an identity column on the ID, you shouldn't set it explicitly from your CSV as that may cause a potential duplicate to be submitted to your database. Also, I suspect that you have some type mismatches in your types since you are putting quotes around what appear to be number columns. I would recommend you consider replacing the string concatenation in your query to using parameters to avoid issues with improper quote escaping (ie. what would happen if you have a PartName of "O'Reily's auto parts"?) Something like this may work. Note, I've been in the LINQ world for so long, I may have some syntax errors here.

SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings("DB00ConnectionString").ConnectionString
        SQLCon.Open()
        SQLCmd.CommandType = CommandType.Text  'Setup Command Type
        SQLCmd.CommandText = "insert into [Base].[PartsImport] " _
                      + " (PartName,PartID,Price,ShipAddress) " _
                      + " values (@PartName, @PartID, @Price, @ShipAddress)'"
        Dim partNameParam = New SqlParameter("@PartName", SqlDbType.VarChar)
        Dim partIdParam = New SqlParameter("@PartID", SqlDbType.Int)
        Dim partPriceParam = New SqlParameter("@Price", SqlDbType.Money)
        Dim partAddressParam = New SqlParameter("@ShipAddress", SqlDbType.VarChar)
        SQLCmd.Parameters.AddRange(  {partNameParam, partIdPAram, partPriceParam, partAddressParam})
        SQLCmd.Connection = SQLCon

        Dim recAdded As String = Now.ToString()
        Dim row As String()
        While Not tf.EndOfData

            Try
                row = tf.ReadFields()
                Dim x As Integer = 0
                If countRecs <> 0 Then
                    Try
                       partNameParam.Value = row[1]
                       partIdParam.Value = row[2]
                       partPriceParam.Value = row[3]
                       partAddressParam.Value = row[4]

                       SQLCmd.ExecuteNonQuery()

                    Catch ex As Exception
                        ErrString = "Error while Creating Batch Record..." & ex.Message
                    End Try
                End If

            Catch ex As MalformedLineException
                errorList = errorList + "Line " + countRecs + ex.Message & "is not valid and has been skipped." + vbCrLf
            End Try
            countRecs = countRecs + 1
        End While

        SQLCon.Close() 'TODO: Change this to a Using clause
        SQLCon.Dispose() 
        SQLCmd.Dispose() 'TODO: Change this to a Using clause

With all that being said, if you have any significant number of items to insert, the bulk copy example is a better answer.

How to File Upload csv to SQL database on intranet VB.NET site

2 Answers2