Why only first record added in another table.

Question

0.00/5 (No votes)

See more:

VS2015

Hi Friends,

I am trying to transfer each record from access table named Emp_master to LeaveMastr. After execution of code it is found that only first record added to LeaveMaster up to the length of Emp_master. My codes ara as follows:

What I have tried:

VB

Sub DataTrans()
   Dbconnection.Open()

   Dim StrArr(2) As String 

   MasterReader = MasterCmd.ExecuteReader()

   Try
      While MasterReader.Read()
         StrArr(0) = (MasterReader("CPF_No".ToString))
         StrArr(1) = (MasterReader("Name".ToString))
         StrArr(2) = (MasterReader("Designation".ToString))

         Try
            LvMasterCmd.CommandText = "INSERT INTO LeaveMaster" &
                                         "(CPF_No, Name, Designation,  MNT, YR, LeaveStatus)" &
                                         "VALUES ( @CPF_No, @Name, @Designation,  @MNT, @YR, @LeaveStatus) ;"

            LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0))
            LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1))
            LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2))

            LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
            LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
            LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")

            LvMasterCmd.ExecuteNonQuery()

         Catch ex As Exception
         End Try

      End While

   Catch ex As Exception
      MessageBox.Show(ex.Message)
   End Try
   Dbconnection.Close()
End Sub

Posted 12-Jul-18 20:38pm

J_Mohan

Updated 17-Jul-18 8:00am

Nelek

v2

Add a Solution

Comments

Richard MacCutchan 13-Jul-18 2:49am

You need to show your original SELECT command. You should also check the number of records returned by the first ExecuteNonQuery. And finally, do not code empty Catch blocks, it means you will never know if that code throws an exception.

J_Mohan 14-Jul-18 9:44am

Module Module1

Public MasterCmd As New OleDbCommand("Select * from Emp_Master", Dbconnection)
Public MasterAdapter As New OleDbDataAdapter(MasterCmd)
'Public AddEmpCommandbuilder As New OleDbCommandBuilder(AddEmpAdapter)
Public MasterDataSet As New DataSet
Public MasterReader As OleDbDataReader

Public LvMasterCmd As New OleDbCommand("Select * from LeaveMaster", Dbconnection)
Public LvMasterAdapter As New OleDbDataAdapter(LvMasterCmd)
' Public OnyEmpCommandbuilder As New OleDbCommandBuilder(OnYAdapter)
Public LvMasterDataSet As New DataSet
Public LvMasterReader As OleDbDataReader
End Module

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-07-12T21:26:00

Solution 1

You will never know, or at least not while you continue to swallow the exceptions that might give you in info you need:

VB

While MasterReader.Read()

    StrArr(0) = (MasterReader("CPF_No".ToString))
    StrArr(1) = (MasterReader("Name".ToString))
    StrArr(2) = (MasterReader("Designation".ToString))
    
    Try
        LvMasterCmd.CommandText = "INSERT INTO LeaveMaster" &
        "(CPF_No, Name, Designation, MNT, YR, LeaveStatus)" &
        "VALUES ( @CPF_No, @Name, @Designation, @MNT, @YR, @LeaveStatus) ;"
        
        LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0))
        LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1))
        LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2))
        
        LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
        LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
        LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")
        
        
        LvMasterCmd.ExecuteNonQuery()
    Catch ex As Exception
        ' EXCEPTION SWALLOWED HERE
    End Try

End While

Never do that: you discard the error, and any information it might include that could help you to actually solve the problem - you even hide the fact that an error occurred at all, so it just looks to you that not all the updates happened...

Posted 12-Jul-18 21:26pm

OriginalGriff

Comments

J_Mohan 14-Jul-18 9:46am

Yes I have added it and run code but NO ERROR or warning received and problem remain same... please help.

OriginalGriff 14-Jul-18 10:05am

Then the code isn't getting executed, or you are looking at the wrong DB.

What does the debugger show?

J_Mohan 14-Jul-18 10:22am

Dibuggre shows NOTHING & code run smoothly and I am using right DB. Each time after execution I see the first record of EMP_Master to LvMaster. I ry to run code in break mode and found after each loop reader reads proper records i.e.
StrArr(0) = (MasterReader("CPF_No".ToString))
StrArr(1) = (MasterReader("Name".ToString))
StrArr(2) = (MasterReader("Designation".ToString))

OriginalGriff 14-Jul-18 10:29am

If the debugger shows nothing, then you are using it wrong! :laugh:
Even negatives are important here.
What exactly did you do with the debugger, and what did it show happened? How and when did you check the DB?

J_Mohan 15-Jul-18 11:02am

I run the code in break mode, codes run smoothly and I check DB immediate after code execution, please help..

OriginalGriff 15-Jul-18 11:08am

So you put a breakpoint where, and you stepped through - what exactly happened? I can't see your screen!

And how did you check the db? When? After the ExecuteNonQuery? After the app closed? What did you use to check? Where did you look?

J_Mohan 15-Jul-18 12:40pm

Yes sir, I put breakpoint to Debug the sub routine rest of the code executed by pressing F11 key. The codes run step by step and screen does not shows any error. I am checking DB Immediate after the App close at the path given in Connection string. The DB is Access Database Table.

OriginalGriff 16-Jul-18 3:16am

"show no error" is irrelevant: what did happen is important, even if it didn't seem to be anything.
Don't check the db after the app exits, check it immediately before and again immediately after the ExecuteNonQuery call. How did you check? What exact file did you check? What file is in the connection string?

Richard Deeming · Answer 2 · 2018-07-17T08:01:00

You need to clear the parameters on each iteration of the loop.

You can also move the code that sets the CommandText outside of the loop, since that doesn't change.

VB.NET

LvMasterCmd.CommandText = "INSERT INTO LeaveMaster" &
                          "(CPF_No, Name, Designation,  MNT, YR, LeaveStatus)" &
                          "VALUES ( @CPF_No, @Name, @Designation,  @MNT, @YR, @LeaveStatus) ;"

While MasterReader.Read()
    StrArr(0) = (MasterReader("CPF_No".ToString))
    StrArr(1) = (MasterReader("Name".ToString))
    StrArr(2) = (MasterReader("Designation".ToString))

    Try
        LvMasterCmd.Parameters.Clear() ' <-- ADD THIS LINE            
        
        LvMasterCmd.Parameters.AddWithValue("@CPF_No", StrArr(0))
        LvMasterCmd.Parameters.AddWithValue("@Name", StrArr(1))
        LvMasterCmd.Parameters.AddWithValue("@Designation", StrArr(2))
        LvMasterCmd.Parameters.AddWithValue("@MNT", CmbMonth.Text)
        LvMasterCmd.Parameters.AddWithValue("@YR", CmbYear.Text)
        LvMasterCmd.Parameters.AddWithValue("@LeaveStatus", "N")
        LvMasterCmd.ExecuteNonQuery()
     
    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    End Try
End While