Last post Jun 14, 2016 12:48 AM by aoshi_kh
Jun 08, 2016 03:26 AM|aoshi_kh|LINK
I create a program to read from text file then insert into database. the text file will have P_ID, P_PART, P_NO, P_REASON, P_DATE.
One P_ID can have difference P_PART and P_NO.
Currently my script below is working for insert line by line, if any line hit error it will skip to next line and continue insert.
So, my question is: did anyone how can I make my script to load all first, then if any 1 of the (P_ID, P_PART, P_NO) combination is failed. The whole batch of P_ID will not be insert into database?
For Each Line In arrAll
arrLine = Split(sLine, ";")
If arrLine.Length <> 0 Then
P_ID = arrLine(0).Trim.Replace("""", "")
P_PART = arrLine(1).Trim.Replace("""", "")
P_NO = arrLine(2).Trim.Replace("""", "")
P_REASON = arrLine(3).Trim.Replace("""", "")
P_DATE = arrLine(4).Trim.Replace("""", "")
'my select statement here to check if data duplication in database
If rChk.HasRows Then
errorFound = True
'my insert statement here..
Jun 08, 2016 06:05 PM|deepalgorithm|LINK
Are you using SQL Server? If so, you can use SqlBulkCopy.
With Oracle you can use the Oracle Data Provider for .Net (ODP.Net)
You have to use the Array binding feature for bulk inserting.
Jun 09, 2016 12:29 AM|aoshi_kh|LINK
hi, I was using oracle server
Jun 09, 2016 03:47 PM|deepalgorithm|LINK
I've updated my answer.
Jun 14, 2016 12:48 AM|aoshi_kh|LINK
Hi Deepalgorithm, I was still not understand how it work? Actually I confuse on how can I read every line on the data and once if found any line is failed, the whole brand of the P_ID should not be inserted?