如何检查VB.NET sqlserver中是否存在记录

本文介绍了如何检查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 END

vb 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 studentInformationtable

Problem. 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 END

vb 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 TRANSACTION

Why 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