本文介绍了如何检查VB.NET sqlserver中是否存在记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有两个表Historytable和studentInformation表。 我想做的是:
如果 schoolyear 和级别存在 in 历史记录 update studentInformationtable else INSERT schoolyear,level to Historytable update studentInformationtable问题。当我插入新的schoolyear和level时,它不会在我的历史记录表中插入新记录。有人可以帮我修改我的代码。谢谢 我尝试过: 我的storeProc
ALTER PROCEDURE [dbo]。[InsertUpdate] - 在此处为存储过程添加参数 @studentID INT, @SchoolYear Nvarchar( 20 ), @levels Nvarchar( 20 ), @FirstName Nvarchar( 20 ), @SurName Nvarchar( 20 ) AS BEGIN SET NOCOUNT ON; BEGIN TRAN IF 存在( SELECT SchoolYear,Levels FROM StudentHistory with (updlock,serializable)WHERE StudentID = @studentID) BEGIN UPDATE StudentInformation SET FirstName = @ FirstName, Surname = @SurName WHERE StudentID = @studentID END ELSE BEGIN INSERT INTO Studenthistory(StudentID,SchoolYear,Levels) values( @ studentID,@ SchoolYear,@ levels) UPDATE StudentInformation SET FirstName = @FirstName, Surname = @SurName WHERE StudentID = @studentID END COMMIT TRAN ENDvb code
使用 cmd As 新 SqlClient.SqlCommand( dbo.InsertUpdate ,cn) cmd.Parameters.AddWithValue( @ studentID, frmView.dgv1.SelectedCells( 0 )。值) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add( @ SurName,SqlDbType.VarChar, 100 )。 Value = txtStudLN.Text cmd.Parameters.Add( @ FirstName,SqlDbType。 VarChar, 100 )。Value = txtStudFN.Text cmd.Parameters.Add( @ SchoolYear,SqlDbType.VarChar, 100 )。Value = cboSchoolYear.Text cmd.Parameters.Add( @ levels,SqlDbType.VarChar, 100 )。Value = cboGradeLevel.Text cmd.ExecuteNonQuery() MsgBox( 成功更新新记录) 结束 使用解决方案
忘记EXISTS: BEGIN TRANSACTION 更新 MyTable SET MyColumn1 = @ MC1 ,MyColumn2 = @ MC2 WHERE ID = @ ID IF @@ ROWCOUNT = 0 BEGIN INSERT INTO MyTable(ID,MyColumn1,MyColumn2) VALUES ( @ ID , @ MC1 , @ MC2 ) 结束 COMMIT TRANSACTION
为什么不在SQL中使用MERGE语句。这将是最简单的。
尝试这个商店程序。 更改程序[dbo]。[InsertUpdate] @studentID INT, @SchoolYear Nvarchar(20), @levels Nvarchar(20), @FirstName Nvarchar(20), @SurName Nvarchar(20) AS BEGIN 设置NOCOUNT ON; 如果存在(选择1 FROM StudentHistory with(NOLOCK)WHERE StudentID = @studentID) BEGIN UPDATE StudentInformation SET FirstName = @FirstName, 姓氏= @SurName WHERE StudentID = @studentID 结束 ELSE BEGIN INSERT INTO Studenthistory(StudentID,SchoolYear,Levels) value (@ studentID,@ SchoolYear,@ levels) 更新学生信息 SET FirstName = @FirstName, Surname = @SurName WHERE StudentID = @studentID END
Assuming that i have two tables Historytable and studentInformation table. What i would like to do is this:
If schoolyear and level exist in Historytable update studentInformationtable else INSERT schoolyear,level to Historytable update studentInformationtableProblem. It does not INSERT a new record in my history table when i insert a new schoolyear and level. Can someone help me to fix my code. thanks What I have tried: My storeProc
ALTER PROCEDURE [dbo].[InsertUpdate] -- Add the parameters for the stored procedure here @studentID INT, @SchoolYear Nvarchar(20), @levels Nvarchar(20), @FirstName Nvarchar(20), @SurName Nvarchar(20) AS BEGIN SET NOCOUNT ON; BEGIN TRAN IF exists (SELECT SchoolYear,Levels FROM StudentHistory with (updlock,serializable) WHERE StudentID = @studentID) BEGIN UPDATE StudentInformation SET FirstName = @FirstName , Surname = @SurName WHERE StudentID = @studentID END ELSE BEGIN INSERT INTO Studenthistory (StudentID,SchoolYear, Levels) values (@studentID,@SchoolYear,@levels) UPDATE StudentInformation SET FirstName = @FirstName, Surname = @SurName WHERE StudentID = @studentID END COMMIT TRAN ENDvb code
Using cmd As New SqlClient.SqlCommand("dbo.InsertUpdate", cn) cmd.Parameters.AddWithValue("@studentID", frmView.dgv1.SelectedCells(0).Value) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@SurName", SqlDbType.VarChar, 100).Value = txtStudLN.Text cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = txtStudFN.Text cmd.Parameters.Add("@SchoolYear", SqlDbType.VarChar, 100).Value = cboSchoolYear.Text cmd.Parameters.Add("@levels", SqlDbType.VarChar, 100).Value = cboGradeLevel.Text cmd.ExecuteNonQuery() MsgBox("Update New record successfully") End Using 解决方案 Forget the EXISTS: BEGIN TRANSACTION UPDATE MyTable SET MyColumn1 = @MC1, MyColumn2=@MC2 WHERE ID = @ID IF @@ROWCOUNT = 0 BEGIN INSERT INTO MyTable(ID, MyColumn1, MyColumn2) VALUES (@ID, @MC1, @MC2) END COMMIT TRANSACTIONWhy don't you use the MERGE statement in SQL. That would be the simplest one.try this store procedure. ALTER PROCEDURE [dbo].[InsertUpdate] @studentID INT, @SchoolYear Nvarchar(20), @levels Nvarchar(20), @FirstName Nvarchar(20), @SurName Nvarchar(20) AS BEGIN SET NOCOUNT ON; IF exists (SELECT 1 FROM StudentHistory with (NOLOCK) WHERE StudentID = @studentID) BEGIN UPDATE StudentInformation SET FirstName = @FirstName , Surname = @SurName WHERE StudentID = @studentID END ELSE BEGIN INSERT INTO Studenthistory (StudentID,SchoolYear, Levels) values (@studentID,@SchoolYear,@levels) UPDATE StudentInformation SET FirstName = @FirstName, Surname = @SurName WHERE StudentID = @studentID END更多推荐
如何检查VB.NET sqlserver中是否存在记录
发布评论