how to import excel file into sql server using vb.net-CodePudding

I need help with importing Excel file into SQL Server by using VB.NET. My coding runs fine but sometimes it occur an ex message that said convert date failed

Here the error. When I import it for the first time it work fine but after changing the primary key in excel and other things it error at date

This is the date in the Excel file. First time it works but second time it doesn't work. I write the date in Excel just like SQL Server date format like 2021-12-14 which is YYYY-MM-DD. I kinda confused about this for a month now... if i have 10 rows in Excel, sometime it occur the error about convert the date but still import the data into SQL Server but some of them not imported in sql

Try
    OLEcon.Open()
    With OLEcmd
        .Connection = OLEcon
        .CommandText = "select * from [Sheet1$]"
    End With
    OLEda.SelectCommand = OLEcmd
    OLEda.Fill(OLEdt)

    For Each r As DataRow In OLEdt.Rows
        Dim intAge As Integer
        intAge = Convert.ToInt32(r(2).ToString)
        Dim dateLED As Date
        dateLED = Convert.ToDateTime(r(11).ToString)
        Dim dateDJ As Date
        dateDJ = Convert.ToDateTime(r(12).ToString)

        sql = "INSERT INTO MasterStaffListTry (EENo,Name,Age,AgeCategory,Gender,Ethnicity,Grade,Category,Department,Position,ReportingTo,LastEmploymentDate,DateJoin,LOCUM,Status) VALUES 
                ('" & r(0).ToString & "','" & r(1).ToString & "','" & intAge & "','" & r(3).ToString & "','" & r(4).ToString & "',
                 '" & r(5).ToString & "' ,'" & r(6).ToString & "','" & r(7).ToString & "','" & r(8).ToString & "','" & r(9).ToString & "',
                 '" & r(10).ToString & "','" & dateLED.ToShortDateString & "','" & dateDJ.ToShortDateString & "','" & r(13).ToString & "' ,'" & r(14).ToString & "')"
        resul = saveData(sql)
        If resul Then
            Timer1.Start()
        End If
    Next

This is my coding for import the Excel file. I think here the wrong part.

CodePudding user response：

You could make the code somewhat more efficient and add error-checking.

You can use just one instance of an SQL command and change the values of its parameters to submit new data.

When getting the values for those parameters, you can use functions like DateTime.TryParse, which gives you an opportunity to handle a parsing error however you want - you might skip the row, or add to an error log, instead of trying to insert invalid data.

You could start from this example:

'TODO: add all the parameters and set their .SqlDbType and .Size values to match the database columns.
Dim eeNo = New SqlParameter With {.ParameterName = "@EENo", .SqlDbType = SqlDbType.NVarChar, .Size = 16}
Dim name = New SqlParameter With {.ParameterName = "@Name", .SqlDbType = SqlDbType.NVarChar, .Size = 60}
Dim age = New SqlParameter With {.ParameterName = "@Age", .SqlDbType = SqlDbType.Int}
Dim lastEmploymentDate = New SqlParameter With {.ParameterName = "@LastEmploymentDate", .SqlDbType = SqlDbType.DateTime2}

'TODO: Write the query in full.
Dim sql = "INSERT INTO MasterStaffListTry (EENo,Name,Age,LastEmploymentDate)
                  VALUES 
                    (@EENo, @Name, @Age, @LastEmploymentDate)"

Dim ci = New CultureInfo("en-US")

Using conn As New SqlConnection("yourConnectionStringGoesHere")
    Using sqlcmd As New SqlCommand(sql, conn)
        'TODO: Add all the parameters.
        sqlcmd.Parameters.Add(eeNo)
        sqlcmd.Parameters.Add(name)
        sqlcmd.Parameters.Add(age)
        sqlcmd.Parameters.Add(lastEmploymentDate)

        Dim led As DateTime ' This will store the lastEmploymentDate when it has been parsed.

        For Each r As DataRow In oleDt.Rows

            'TODO: Use TryParse for all applicable data.
            Dim ledOK = DateTime.TryParse(r(11).ToString(), ci, Nothing, led)

            'TODO: Check all the parsing worked, e.g. If ledOK AndAlso variable2OK AndAlso variable3OK Then
            If ledOK Then
                eeNo.Value = r(0).ToString()
                name.Value = r(1).ToString()
                age.Value = Convert.ToInt32(r(2))
                lastEmploymentDate.Value = led

                sqlcmd.ExecuteNonQuery()

            End If

        Next

    End Using
End Using

It is a good idea to specify the culture which is to be used when trying to parse dates or numbers, e.g. 1/12/2020 could be the first of January or January 12th.