为什么只有第一条记录添加到另一个表中 [英] Why only first record added in another table.
问题描述
嗨朋友们,
我正在尝试将每条记录从名为Emp_master的访问表转移到LeaveMastr。执行代码后,发现只有第一条记录添加到LeaveMaster,最长为Emp_master。我的代码如下:
我的尝试:
Sub DataTrans()
Dbconnection.Open()
Dim StrArr( 2 ) As 字符串
MasterReader = MasterCmd.ExecuteReader()
尝试
while MasterReader.Read()
StrArr( 0 )=(MasterReader( CPF_No .ToString))
StrArr( 1 )=(MasterReader( Name .ToString))
StrArr( 2 )=(MasterReader( 名称.ToString))
尝试
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 异常
结束 尝试
结束
Catch ex As 异常
MessageBox.Show(ex.Message)
结束 尝试
Dbconnection.Close()
结束 Sub
你永远不会知道,或者至少在你继续时不知道e吞下可能为您提供所需信息的异常:
MasterReader.Read()
StrArr( 0 )=(MasterReader( CPF_No .ToString))
StrArr( 1 )=(MasterReader( 名称 .ToString))
StrArr( 2 )=( MasterReader( 名称 .ToString))
尝试
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 .AddWi thValue( @ LeaveStatus, N)
LvMasterCmd.ExecuteNonQuery()
Catch ex 作为例外
' EXCEPTION SWALLOWED HERE b 结束 尝试
结束 虽然从不这样做:您丢弃错误,以及可能包含的任何信息这可以帮助你真正解决问题 - 你甚至隐藏了一个错误发生的事实,所以它只是向你看,并非所有的更新都发生...
你需要清除循环每次迭代的参数。
你也可以移动c ode在循环之外设置CommandText
,因为这不会改变。
LvMasterCmd。 CommandText = INSERT INTO LeaveMaster&
(CPF_No,Name,Designation,MNT,YR,LeaveStatus)&
VALUES(@ CPF_No,@ Name,@ Designation,@ MNT,@ YR,@ LeaveStatus);
MasterReader.Read()
StrArr( 0 )=(MasterReader( CPF_No .ToString))
StrArr( 1 )=(MasterReader( 名称 .ToString))
StrArr( 2 )=(MasterReader( 名称 .ToString))
尝试
LvMasterCmd.Parameters.Clear() ' < - 添加此行
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()
捕捉跨度> ex As 异常
MessageBox.Show(ex.ToString())
结束 尝试
结束
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:
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
You will never know, or at least not while you continue to swallow the exceptions that might give you in info you need:
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 WhileNever 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...
You need to clear the parameters on each iteration of the loop.
You can also move the code that sets theCommandText
outside of the loop, since that doesn't change.
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
这篇关于为什么只有第一条记录添加到另一个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!