Insert Skips When use in a Loop

[yogesh_kumar_agarwal]

When i used to upload the excel file, i inserts the records but it used to skip the insertion, like my file has 9000 records it insert 8890 or 8800 sometimes 8950, plz suggest what would be the possible reason.

            Dim conexcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("upload/" & FileUpload1.FileName) & "; Extended Properties=Excel 8.0")
            Dim cmd As New OleDbCommand("select \* from \[Sheet1$\]", conexcel)


            Try
                If ConnectionState.Open Then
                    conexcel.Close()
                End If
                conexcel.Open()
                Dim dr As OleDbDataReader
                dr = cmd.ExecuteReader()

                Dim act, cnt, ds, cf, co, distt, dtype As String

                act = ddl\_Activity.SelectedItem.Text.ToString()
                cnt = ddl\_Center.SelectedItem.Text.ToString()
                ds = ddl\_DataSource.SelectedItem.Text.ToString()
                distt = ddl\_Distt.SelectedItem.Text.ToString()
                bcode = UCase(bcode) & "/" & distt & "/" & act & "/" & bno.ToString()
                dtype = ddl\_DataType.SelectedItem.Text.ToString()

                Try
                    Dim name, cnt1, cnt2 As String
                    name = ""
                    cnt1 = ""
                    cnt2 = ""
                    cf = ""
                    co = ""

                    Dim m, p As Int64
                    m = 1
                    While (dr.Read())
                        name = Trim(dr(0).ToString())
                        cnt1 = Trim(dr(1).ToString())
                        cnt2 = Trim(dr(2).ToString())
                        cf = Trim(dr(3).ToString())
                        co = Trim(dr(4).ToString())

                        Dim str As String
                        If cnt1 <> "" Or name <> "" Then
                            str = "insert into tbl\_edu\_dt(full\_name,contact1,contact2,Activity,Center,DistName,DataSource,Collected\_From,Collected\_On,batch\_code,batch\_id,imp\_date,imp\_time,Data\_Type) values ('" & name & "','" & cnt1 & "','" & cnt2 & "','" & act & "','" & cnt & "','" & distt & "','" & ds & "','" & cf & "' , '" & co & "','" & bcode & "','" & m & "','

[Michel Godfroid]

If you don't check for any errors, you'll never know...

[Abhijit Jana]

yogesh_kumar_agarwal wrote:

like my file has 9000 records it insert 8890 or 8800 sometimes 8950,

With the same file or multiple file ? I guess it may be causing of exception during the insert records. What is my suggestion is, Just do a quick comparison with the insrted data and the orginal data. You will easily identify the records which are not inserted. Now check what goes those records. Hope you got my point. Thanks !

Cheers ! Abhijit Jana | MVP Web Site : abhijitjana.net | Follow Me @ twitter Read my Latest Article :Mastering Debugging in VS 2010

[yogesh_kumar_agarwal]

Its fine but i m not so free that i will post the problem without checking it. If u dont know the solution why give this kind of posts.

[yogesh_kumar_agarwal]

This happens in the same file, u overcome the problem i try it to do upload the records using code also with the bearkpoint at catch but it will never come over that. Also when it inserts 8890 records then abviousally it inserts those records also that it skips when it inserts only 8800. So i just can't undersatnd the problem why it skips the inserts. Plz help!! Thanks Yogesh Agarwal

[Abhijit Jana]

No, Here is my one doubt. You are saying you have 900 records in file and when you inserted the in DB the records comes to 890 ( Let Say ). So there is missing of 10 Recrods. Did you try to identify which records are not inserted ? If yes what is the different between the data which are inserted or which not ? Is there any problem with special char. with in the data ?

Cheers ! Abhijit Jana | MVP Web Site : abhijitjana.net | Follow Me @ twitter Read my Latest Article :Mastering Debugging in VS 2010

[Michel Godfroid]

Looking at your code, you never check for an error after the insert statement. Chances are that you have some illegal values in your input records, and checking for errors would tell you the rows on which it happens. Just stepping over the insert statement will bring you nothing.

[Michel Godfroid]

If you program doesn't raise an exception now, adding a try/catch block won't help. You should lookup the functionality of execute_query(str) statement, and see if it returns any error codes or messages.

[Martin Jarvis]

There's a lot of string concatenation with the insert statement, are you sure that none of your records in the file contain additional '? Is it possible that ObjClass is swallowing exceptions as well? Also, are you certain the missing datalines aren't missing values for >cnt1 and name?

---

Freestyle Interactive Ltd | Martin on .Net