Remove From My Forums

Check in DB and stop inserting a specific value

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

Answered by:

Check in DB and stop inserting a specific value

Question

Answers