Link to home
Create AccountLog in
Avatar of fizzlefry
fizzlefry

asked on

Data Conversion Error on DataTable INSERT

I have a FUNCTION I found online and tailored that reads a flat file, and creates a datatable, based on the number of columns.  The FUNCTION is as follows:

Private Function BuildDataTable(ByVal fileFullPath As String, ByVal seperator As Char) As DataTable

        seperator = "|"

        Dim myTable As DataTable = New DataTable("tmpDT")
        Dim i As Integer
        Dim myRow As DataRow
        Dim fieldValues As String()
        Dim myReader As IO.StreamReader

        Try
            'Open file and read first line to determine how many fields there are.
            myReader = File.OpenText("C:\DataReceiver\Incoming\" & fileFullPath)
            fieldValues = myReader.ReadLine().Split(seperator)

            'Create data columns accordingly
            For i = 0 To fieldValues.Length() - 1
                myTable.Columns.Add(New DataColumn("Field" & i))
            Next

            'Adding the first line of data to data table
            myRow = myTable.NewRow
            For i = 0 To fieldValues.Length() - 1
                myRow.Item(i) = fieldValues(i).ToString
            Next
            myTable.Rows.Add(myRow)

            'Now reading the rest of the data to data table
            While myReader.Peek() <> -1
                fieldValues = myReader.ReadLine().Split(seperator)
                myRow = myTable.NewRow
                For i = 0 To fieldValues.Length() - 1
                    myRow.Item(i) = fieldValues(i).ToString
                Next
                myTable.Rows.Add(myRow)
            End While

            myReader.Close()

        Catch ex As Exception
            MsgBox("Error building datatable: " & ex.Message)
            Dim errortime As DateTime = DateTime.Now
            Dim currenterroractivity As String = txtFolderActivity.Text
            Dim errortext As String = "" & errortime & " - ERROR Translating File: " & fileFullPath & ": " & ex.Message
            txtFolderActivity.Text = currenterroractivity & errortext & vbCrLf & ""
            log(errortext)
            Return New DataTable("Empty")
            Exit Function
        End Try

        Return myTable

    End Function

I call to the table creation like so:

Dim reader As DataTable = BuildDataTable(strFileName, "|")

Now what I need to do is add this to a database, however I need to check before it is added (so I can't use BulkCopy) to verify if it needs to INSERT or UPDATE.  I do that like so:

Dim GetID As String = reader.Columns(0).ToString
                    ' Hard coded handling of translation file changes
                    Dim sqlCheckExTrans As New SqlCommand("SELECT COUNT(*) FROM tblTypeOfTrans WHERE (TransID = '" & GetID & "')", connection.sqlTranslator)
                    Dim checkint As Integer = sqlCheckExTrans.ExecuteScalar
                    sqlCheckExTrans = Nothing
                    If checkint = 0 Then

' INSERT

Else

' UPDATE

End If

My original logic for INSERT / UPDATE was like this:

Dim sqlAdd As New SqlCommand("INSERT INTO tblTypeOfTrans (TransID, TransCode, UpdateStmnt, InsertStmnt, TargetDB, MTStmnt, TargetTable) VALUES ('" & Convert.ToInt32(reader.Rows(0).Item(0)) & "', '" & reader.Rows(0).Item(1) & "', '" & reader.Rows(0).Item(2) & "', '" & reader.Rows(0).Item(3) & "', '" & reader.Rows(0).Item(4) & "', '" & reader.Rows(0).Item(5) & "', '" & reader.Rows(0).Item(6) & "')", connection.sqlTranslator)
                        sqlAdd.ExecuteNonQuery()
                        sqlAdd = Nothing

However I get a conversion error from VarChar to INT on the first field (which is a number).  Now I realize I can specify the data type in the BuildDataTable function, however, it was my goal to use this for several different types of imports (with other styles of flat file; I take my cues from the first 2 characters in the filename to dictate what I need to run, however they all involve creating a temp datatable from the flat file first), so I don't want to specify column types here.  As I said, I have to check for existing records before processing, so as I understand it, BulkCopy is out the window.  I've tried several other variations on converting the first column:

Convert.ToInt32(reader.Rows(0).Item(0)) - Within the code - Same result
Convert(INT, (reader.Rows(0).Item(0))) - Within the actual SQL statement - Same result

Then I tried this:

Dim DA As SqlDataAdapter = New SqlDataAdapter
                        Dim Parm As New SqlParameter
                        DA.InsertCommand = New SqlCommand("INSERT INTO tblTypeOfTrans (TransID, TransCode, UpdateStmnt, InsertStmnt, TargetDB, MTStmnt, TargetTable) VALUES (@fld0, @fld1, @fld2, @fld3, @fld4, @fld5, @fld6)", connection.sqlTranslator)
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld0", SqlDbType.Int, 32, "fld0"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld1", SqlDbType.VarChar, 50, "fld1"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld2", SqlDbType.VarChar, 50, "fld2"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld3", SqlDbType.VarChar, 50, "fld3"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld4", SqlDbType.VarChar, 50, "fld4"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld5", SqlDbType.VarChar, 50, "fld5"))
                        Parm = DA.InsertCommand.Parameters.Add(New SqlParameter("@fld6", SqlDbType.VarChar, 50, "fld6"))
                        DA.Update(reader)

All end with the same result.  The target db has the column TransID set as INT (which is my first field), hence the failure.  However, if I change the data type in the target to VarChar (which I assume is the default type the function uses when creating the datatable), it works like a charm.  My concern is that, moving forward, if I'm using this for other flat files, how can I write the function so that it can be used for all types of data?  I just don't know where to go with it, and sadly all my googling seems to point to SQLBulkCopy, which I can't see how I can use it, due to needing to verify if records exist or not, before processing.
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Your field may be a number, but it is probably being concatenated other characters. Maybe it has a space in the end, or some other char. Try to debug it and catch the actual value that is causing the error.

As for the update/insert, you can do it in one single query:
UPDATE tblTypeOfTrans
  SET TransCode=@fld1, UpdateStmnt=@fld2, InsertStmnt=@fld3, TargetDB=@fld4, MTStmnt=@fld5, TargetTable=@fld6
WHERE TransID=@fld0
IF @@ROWCOUNT = 0
  INSERT INTO tblTypeOfTrans (TransID, TransCode, UpdateStmnt, InsertStmnt, TargetDB, MTStmnt, TargetTable) VALUES (@fld0, @fld1, @fld2, @fld3, @fld4, @fld5, @fld6)

Alternately, you can simply create a syntax of IF EXISTS THEN UPDATE ELSE INSERT. Both account to the same thing. Then you can blindly run the same code to all rows.

This won't get rid of conversion errors, though. Those would have to be fixed on the file or function (maybe with trim, depending on the values).
Avatar of fizzlefry
fizzlefry

ASKER

CodeCruiser, as usual...  you're right on the mark.  I hate it when my issues aren't me being too technical, but me not paying attention to detail.  I appreciate it, as always.

As a footnote, I did notice that my check statement (SELECT COUNT(*)...) to verify records existing was not calling the same converted field, rather the unconverted one.  I made the changes there as well and it works like a champ!

Cluskitt, I appreciate your insight.  Thank you again for your help.
>> however, it was my goal to use this for several different types of imports [...], so I don't want to specify column types here

You said this, which is why I haven't said anything about the ''
If you remove the '', when you try to use this on another file where that field actually is a string, you'll get a syntax error.