如何检查VB.NET sqlserver中是否存在记录 [英] How to check if record exist in 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）
 < span class =code-keyword> 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.net 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  < span class =code-keyword> 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.net 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

这篇关于如何检查VB.NET sqlserver中是否存在记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！