时间戳

首页 > 解决方案 > 检查数据库中是否已经存在数据

sql-server - 检查数据库中是否已经存在数据

问题描述

如果数据库中已经存在数据，任何人都可以通过以红色显示消息来帮助我检查数据库中是否已经存在数据。

我想检查StaffID数据库中是否已经存在，如果存在，则用户无法使用相同的staffID.

但我不知道该怎么做。谁能帮我做代码来检查staffID数据库中是否已经存在？

非常感谢您的帮助。

这是我的代码：

<table id="tblBasicInfo">
  <tr>
     <td style="width: 50%">
        <div class="form-group">
           <label class="col-sm-3 control-label no-padding-right" for="form-field-1"><span style="color: red">*</span>Staff ID</label>
            <div class="col-sm-8">
                <asp:DropDownList ID="ddlStaffID" class="chosen-select form-control col-sm-9" Width="100%" data-placeholder="Choose StaffID" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlStaffID_SelectedIndexChanged"></asp:DropDownList>
            </div>
         </div>
      </td><tr/>
  <tr>
     <td style="width:50%">
       <div class="form-group">
          <label class="col-sm-3 control-label no-padding-right" for="form-field-1"><span style="color: red">*</span> Full Name</label>
            <div class="col-sm-8">
             <asp:TextBox class="form-control" placeholder="Enter your First Name" id="txtFirstName" runat="server"></asp:TextBox>
            </div>
       </div>
     </td>
     <td style="width: 50%">                                            
      <div class="form-group">
      <label class="col-sm-3 control-label no-padding-right" for="form-field-1"><span style="color: red">*</span> Last Name</label>
        <div class="col-sm-8">
          <asp:TextBox class="form-control" placeholder="Enter your Last Name" id="txtLastName" runat="server"></asp:TextBox>
          <span class="help-inline col-xs-12 col-sm-7" />
        </div>
     </div>
    </td>
 </tr>
 <tr>
   <td style="width:50%">
     <div class="form-group">
       <label class="col-sm-3 control-label no-padding-right" for="form-field-1"><span style="color: red">*</span> Email</label>
        <div class="col-sm-8">
         <asp:TextBox class="form-control" placeholder="Enter your Email Address" id="txtEmail" runat="server"></asp:TextBox>
         <span class="help-inline col-xs-12 col-sm-7" />
        </div>
       </div>
   </td>
   <td style="width:50%">
     <div class="form-group">
     <label class="col-sm-3 control-label no-padding-right" for="form-field-1"><span style="color: red">*</span> Mobile Phone</label>
     <div class="col-sm-8">
      <asp:TextBox class="form-control" placeholder="Enter Mobile Phone Number" id="txtMobilePhone" runat="server"></asp:TextBox>
      <span class="help-inline col-xs-12 col-sm-7" />
      </div>
     </div>
   </td>
 </tr>

aspx 代码

Protected Sub ddlStaffID_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Try
        ErrMsg = "LoadDataGrid "
        attPage.SQLQuery = DC.Data_TechnicalProfile("1001", ddlStaffID.SelectedItem.Value)
        DS = DA.GetSQLDataset(attPage.SQLQuery)
        If DS IsNot Nothing AndAlso DS.Tables(0).Rows.Count > 0 Then
            txtFirstName.Text = DS.Tables(0).Rows(0)("ParticipantName").ToString
            txtEmail.Text = DS.Tables(0).Rows(0)("Email").ToString
            txtMobilePhone.Text = DS.Tables(0).Rows(0)("ContactNo").ToString
        End If
    Catch ex As Exception
        attPage.ErrorMessage = DA.GetErrorMessage(1, System.Reflection.MethodBase.GetCurrentMethod.Name.ToString, ErrMsg, ex.Message.ToString, attPage.ActionPage)
        ShowError(attPage.ErrorHeader, attPage.ErrorMessage)
    End Try
End Sub

Sub LoadDropDownList()
    Try

        attPage.SQLQuery = DC.Data_TechnicalResource("2")
        DS = DA.GetSQLDataset(attPage.SQLQuery)
        attPage.ErrorMessage = "SearchStaffID "
        ddlStaffID.DataTextField = "StaffID"
        ddlStaffID.DataValueField = "ID"
        ddlStaffID.DataSource = DS.Tables(5)
        ddlStaffID.DataBind()
        ddlStaffID.Items.Insert(0, "")



    Catch ex As Exception
        attPage.ErrorMessage = DA.GetErrorMessage(1, System.Reflection.MethodBase.GetCurrentMethod.Name.ToString, ErrMsg, ex.Message.ToString, attPage.ActionPage)
        ShowError(attPage.ErrorHeader, attPage.ErrorMessage)
    End Try
End Sub

SQL 查询

IF @Action=101
BEGIN
    SELECT DISTINCT StaffID AS [StaffID], ParticipantID AS [ID], ParticipantName AS [Name]
    FROM mstUser
    WHERE StatusID = '1' AND GroupID = '12'
    ORDER BY StaffID
END

标签： sql-servervb.net

解决方案

假设 StatusID 是 mstUser 的主键如果您只需要检查记录是否存在，那么...

Private Sub OPCode2()
    Dim count As Integer
    Using cn As New SqlConnection("Your connection string")
        Using cmd As New SqlCommand("Select Count(*) From mstUser Where StatusID = @StatusID", cn)
            cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = ddlStaffID.SelectedItem.Value
            cn.Open()
            count = CInt(cmd.ExecuteScalar())
        End Using
    End Using
    If count > 0 Then 'Record exists
        'You red alert code here.
        'If showing the alert is the problem then that is another question
    End If
End Sub

编辑

根据 @marc_s 使用 If Exists 编写代码。

Private Sub OPCode3()
    Dim Exists As Boolean
    Using cn As New SqlConnection("Your connection string")
        Using cmd As New SqlCommand("If Exists (Select * From mstUser Where StatusID = @StatusID) Select 1 Else Select 0;", cn)
            cmd.Parameters.Add("@StatusID", SqlDbType.Int).Value = ddlStaffID.SelectedItem.Value
            cn.Open()
            Exists = CBool(cmd.ExecuteScalar())
        End Using
    End Using
    If Exists Then 'Record exists
        'You red alert code here.
        'If showing the alert is the problem then that is another question
    End If
End Sub

推荐阅读