locked
Check in DB and stop inserting a specific value RRS feed

  • Question

  • User810354248 posted

    In my asp.net+VB+SQL web i am using this code to check if the data already exists in table and it works fine.

    Dim con As New SqlConnection(Str)
            con.Open()
            Str = "Select userid from users"
            Dim SQLadp As New SqlDataAdapter(Str, con)
            Dim dt As New DataSet
            dt.Clear()
            SQLadp.Fill(dt, "users")
            For i As Integer = 0 To dt.Tables("users").Rows.Count - 1
                If newusertxt.Text = dt.Tables("users").Rows(i).Item("Userid") Then
                    Page.ClientScript.RegisterStartupScript(Me.GetType, "Forms", "<script> alert('User ID already Exists ..........') </script>")
                    newusertxt.Text = ""
                    Exit Sub
                End If
            Next

    Users

    id   Userid   Ch

    1    Baiju      1

    2    Sanath   3

    3    Sony      2

    There is a sample data table above. How can i stop users by adding 1 and 2 in Ch field (In only CH field there should be only one 1&2 values)

    If 1 &2 are not there in Ch field then it should permit to proceed. If 1&2 already exists then exit sub 

    after this i want to check that if 

    Friday, January 5, 2018 10:09 AM

Answers

  • User-1838255255 posted

    Hi Baiju EP,

    According to your description, please check the following sample code:

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" EmptyDataText="No records has been added.">
                    <Columns>
                        <asp:TemplateField HeaderText="Userid" ItemStyle-Width="150">
                            <ItemTemplate>
                                <asp:Label ID="Userid" runat="server" Text='<%# Eval("Userid") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Ch" ItemStyle-Width="150">
                            <ItemTemplate>
                                <asp:Label ID="Ch" runat="server" Text='<%# Eval("Ch") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
                <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
                    <tr>
                        <td style="width: 150px">Userid:<br />
                            <asp:TextBox ID="Userid" runat="server" Width="140" />
                        </td>
                        <td style="width: 150px">Ch:<br />
                            <asp:TextBox ID="Ch" runat="server" Width="140" />
                        </td>
                        <td style="width: 100px">
                            <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" />
                        </td>
                    </tr>
                </table>
    
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                Me.BindGrid()
            End If
        End Sub
    
    
        Private Sub BindGrid()
            Dim constr As String = ConfigurationManager.ConnectionStrings("TestNewConnectionString").ConnectionString
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT * FROM UserTb")
                    Using sda As SqlDataAdapter = New SqlDataAdapter()
                        cmd.Connection = con
                        sda.SelectCommand = cmd
                        Using dt As DataTable = New DataTable()
                            sda.Fill(dt)
                            GridView1.DataSource = dt
                            GridView1.DataBind()
                        End Using
                    End Using
                End Using
            End Using
        End Sub
    
        Protected Sub btnAdd_Click(sender As Object, e As EventArgs)
            Dim exist As Integer = 0
            Dim txt As String = Userid.Text.Trim
            Dim nch As String = Ch.Text.Trim
            Dim constr As String = ConfigurationManager.ConnectionStrings("TestNewConnectionString").ConnectionString
            Dim con As SqlConnection = New SqlConnection(constr)
            Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM UserTb")
            Dim sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Dim dt As DataTable = New DataTable
            Dim i As Integer = 0
            sda.Fill(dt)
    
            Do While (i < dt.Rows.Count)
                If (Convert.ToInt32(dt.Rows(i)(1)) = Convert.ToInt32(nch)) Then
                    exist = 1
    
                End If
                i = (i + 1)
            Loop
    
    
            If (exist = 1) Then
                Page.ClientScript.RegisterStartupScript(Me.GetType, "Registered Script", "alert('Exist this user!')", True)
            Else
                ' execute insert command 
                Page.ClientScript.RegisterStartupScript(Me.GetType, "Registered Script", "alert('Insert Success!')", True)
            End If
        End Sub
    

    Result:

    Database:

    Best Regards,

    Eric Du 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 8, 2018 10:17 AM